Việc lọc và tìm kiếm dữ liệu trong Excel đôi khi có thể giống như một trò chơi “đoán mò”, đặc biệt khi bạn cần xử lý các mẫu dữ liệu phức tạp. Nhưng với các hàm Regex trong Excel, mọi thứ sẽ thay đổi hoàn toàn. Giờ đây, bạn có thể xác định chính xác những gì mình cần – từ các mẫu phức tạp, so khớp một phần, cho đến việc trích xuất dữ liệu có cấu trúc – một cách dễ dàng và hiệu quả. Bài viết này sẽ hướng dẫn bạn cách sử dụng Regex để tối ưu hóa khả năng xử lý dữ liệu trong Excel.
Regex Là Gì?
Regex, viết tắt của Regular Expression (Biểu thức chính quy), là một loại mẫu được sử dụng để tìm kiếm một chuỗi văn bản hoặc một dãy ký tự theo một quy tắc nhất định. Bạn có bao giờ tự hỏi làm thế nào các trang web có thể thông báo rằng định dạng email bạn nhập trên trang đăng nhập không hợp lệ không? Đó chính là một ví dụ về mẫu Regex sử dụng để kiểm tra định dạng email.
Biểu thức chính quy không chỉ có mặt trong Excel mà còn được sử dụng rộng rãi trong nhiều trình soạn thảo văn bản, ngôn ngữ lập trình, công cụ dòng lệnh, IDE và thậm chí cả đối thủ cạnh tranh của Excel là Google Sheets.
Regex nghe có vẻ phức tạp, và đúng là như vậy nếu bạn muốn sử dụng nó triệt để. Tuy nhiên, bạn không cần phải là một lập trình viên để sử dụng nó một cách hiệu quả. Trong nhiều trường hợp, chỉ cần hiểu một vài ký hiệu và mẫu cơ bản là bạn đã có thể bắt đầu. Chúng tôi sẽ giữ hướng dẫn này càng đơn giản càng tốt để bạn có thể nhanh chóng làm quen.
Các Ký Hiệu Regex Cơ Bản
Dưới đây là các ký hiệu Regex mà chúng ta sẽ sử dụng trong hướng dẫn này:
| Ký hiệu | Mô tả |
|---|---|
- |
Chỉ định một phạm vi ký tự trong dấu ngoặc vuông (ví dụ: [a-z]). |
^ |
Khớp với vị trí bắt đầu của một chuỗi. |
$ |
Khớp với vị trí kết thúc của một chuỗi. |
. |
Khớp với bất kỳ ký tự nào ngoại trừ ký tự xuống dòng mới. |
* |
Khớp 0 hoặc nhiều lần ký tự đứng trước nó. |
+ |
Khớp 1 hoặc nhiều lần ký tự đứng trước nó. |
() |
Nhóm các ký tự khớp được vào một nhóm. |
[] |
Khớp với bất kỳ ký tự nào bên trong dấu ngoặc vuông. |
[^] |
Khớp với bất kỳ ký tự nào KHÔNG nằm trong dấu ngoặc vuông. |
{n} |
Khớp chính xác n lần xuất hiện của ký tự đứng trước. |
{n,} |
Khớp n hoặc nhiều lần xuất hiện của ký tự đứng trước. |
Các Mẫu Regex Đơn Giản
Bạn có thể xây dựng các mẫu Regex đơn giản với những ký hiệu trên như sau:
| Mẫu Regex | Mô tả |
|---|---|
[0-9] |
Khớp một chữ số từ 0 đến 9. |
[a-zA-Z0-9] |
Phạm vi kết hợp này khớp một ký tự đơn từ chữ thường a đến z, chữ hoa A đến Z, và từ 0 đến 9. |
^pro |
Khớp bất kỳ chuỗi nào bắt đầu bằng pro. |
[^$] |
Khớp bất kỳ ký tự nào không phải là $. |
(con) |
Nhóm mẫu con. |
a{3,} |
Khớp 3 hoặc nhiều hơn các lần xuất hiện của ký tự a (ví dụ: a, aa, hoặc aaa). |
Các hàm Regex là các công thức được định nghĩa sẵn trong Excel, cho phép bạn xác định một mẫu để tìm kiếm và thao tác các chuỗi văn bản. Hiện tại có ba hàm Regex chính, và chúng ta sẽ tìm hiểu cách sử dụng chúng một cách độc lập và kết hợp với các hàm khác.
Tìm Kiếm Mẫu Dữ Liệu Với Hàm REGEXTEST
Hàm đầu tiên chúng ta sẽ tìm hiểu là REGEXTEST. Hàm này nhận một chuỗi văn bản bạn muốn tìm kiếm và một mẫu Regex, sau đó sử dụng mẫu đó để tìm sự khớp trong chuỗi. Hàm sẽ trả về TRUE hoặc FALSE.
Cú pháp của hàm REGEXTEST như sau:
REGEXTEST(string_to_search, regex_pattern_to_use, [case_sensitivity])Hai tham số đầu tiên, string_to_search (chuỗi cần tìm kiếm) và regex_pattern_to_use (mẫu Regex cần sử dụng), rất dễ hiểu. Tham số [case_sensitivity] là tùy chọn (những gì đặt trong dấu ngoặc vuông trong cú pháp Excel là tùy chọn) và cho biết bạn muốn tìm kiếm phân biệt chữ hoa/thường (0) hay không phân biệt (1). Mặc định là phân biệt chữ hoa/thường.
Trong ví dụ của chúng ta, chúng ta sẽ sử dụng REGEXTEST để xem liệu người dùng đã nhập địa chỉ email hợp lệ hay chưa, sử dụng công thức sau:
REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")Ở đây, chúng ta đang tìm kiếm trong ô B3 để xem nó có chứa địa chỉ email hay không, sử dụng mẫu Regex dưới đây:
^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$Nếu tôi đặt công thức vào ô C3 và nhập john.doe@example.com vào ô B3, nó sẽ trả về TRUE vì chuỗi đó khớp với định dạng email.
Ví dụ sử dụng hàm REGEXTEST để kiểm tra định dạng email hợp lệ trong Excel
Trích Xuất Dữ Liệu Với Hàm REGEXEXTRACT
Tiếp theo, hãy cùng tìm hiểu hàm REGEXEXTRACT. Hàm này trả về một chuỗi con (một phần của chuỗi gốc) khớp với mẫu Regex được cung cấp.
Cú pháp của hàm REGEXEXTRACT như sau:
REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_sensitivity])Tiếp tục với ví dụ về email, hãy thêm một công thức vào ô B4 để trích xuất tên người dùng (username) từ địa chỉ email.
Đây là công thức sẽ trông như thế này:
=REGEXEXTRACT(B3, "([^@]+)")Trong công thức này, chúng ta trích xuất mọi thứ trước ký hiệu @ trong địa chỉ email được nhập vào ô B3.
Cách dùng hàm REGEXEXTRACT để trích xuất tên người dùng từ địa chỉ email trong Excel
Tìm Và Thay Thế Dữ Liệu Với Hàm REGEXREPLACE
Hàm Regex cuối cùng chúng ta sẽ xem xét là REGEXREPLACE. Hàm này tương tự như hàm REPLACE của Excel, nhưng có hỗ trợ Regex. Nó nhận một chuỗi văn bản bạn muốn sửa đổi và kiểm tra xem có chuỗi con nào khớp với mẫu Regex đã định nghĩa hay không. Nếu tìm thấy, nó sẽ thay thế chuỗi con đó bằng chuỗi thay thế được cung cấp.
Cú pháp của hàm REGEXREPLACE như sau:
REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_sensitivity])Dưới đây là các tham số quan trọng cần chú ý trong hàm này:
string_to_modify: Chuỗi văn bản bạn muốn sửa đổi.replacement_string: Chuỗi sẽ dùng để thay thế chuỗi con khớp.number_of_occurrences: Lần xuất hiện chính xác mà bạn muốn thay thế.
Dưới đây là ví dụ về cách sử dụng hàm để thay thế phần tên người dùng (username) của email bằng một chuỗi văn bản khác:
=REGEXREPLACE(B3, "^[^@]+", "jane.doe")Giá trị của ô B3 là john.doe@example.com, và sau khi chúng ta nhập công thức trên vào ô C3, nó sẽ trả về jane.doe@example.com.
Minh họa hàm REGEXREPLACE thay thế phần username của email trong Excel
Kết Hợp Hàm Regex Với Các Hàm Excel Khác
Bạn cũng có thể kết hợp các hàm Regex với các hàm khác trong Excel để tạo ra các công cụ xử lý dữ liệu mạnh mẽ hơn. Chẳng hạn, bạn có thể kết hợp hàm REGEXTEST với một câu lệnh IF của Excel và hiển thị thông báo phù hợp dựa trên kết quả kiểm tra.
Đây là một ví dụ công thức:
=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"), "Đây là một địa chỉ email hợp lệ!", "Địa chỉ email không hợp lệ!")Công thức này sử dụng câu lệnh IF để kiểm tra xem địa chỉ email được nhập vào ô B6 có hợp lệ không và sau đó hiển thị Đây là một địa chỉ email hợp lệ! nếu kết quả là TRUE hoặc Địa chỉ email không hợp lệ! nếu là FALSE. Ngoài ra, bạn có thể kết hợp điều này với hàm FIND để nhanh chóng tìm dữ liệu trong Excel.
Bài viết này không nhằm mục đích là một hướng dẫn toàn diện về biểu thức chính quy – điều đó sẽ đòi hỏi nhiều bài viết riêng biệt. Tuy nhiên, đây là một cách tốt để bạn bắt đầu sử dụng Regex trong Excel. Các trường hợp sử dụng và khả năng ứng dụng chỉ bị giới hạn bởi trí tưởng tượng của bạn. Hãy bắt đầu thử nghiệm và khám phá sức mạnh của Regex ngay hôm nay để nâng cao kỹ năng xử lý dữ liệu của mình!
