Cách sử dụng hàm Regex của Excel để tăng sức mạnh tìm kiếm

Lọc và tìm kiếm trong Excel không phải nhiệm vụ đơn giản. Các hàm Regex sẽ thay đổi điều đó. Bây giờ, bạn có thể xác định chính xác những gì mình cần - các mẫu phức tạp, những kết quả khớp một phần hoặc trích xuất dữ liệu có cấu trúc - mà không cần tốn công sức.

 

Regex là gì?

Regex 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 chuỗi ký tự để tìm kết quả khớp. Bạn đã bao giờ tự hỏi làm thế nào các trang web có thể cho bạn biết mẫu email bạn đã nhập trên trang đăng nhập là không hợp lệ chưa? Đó là một ví dụ về mẫu regex sử dụng chữ ký email đang hoạt động.

Biểu thức chính quy không chỉ có trong Excel - chúng có sẵn 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í là đối thủ cạnh tranh của Excel, Google Sheets.

Regex có vẻ phức tạp và đúng là như vậy nếu bạn muốn sử dụng hết tiềm năng của nó, nhưng 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 một số trường hợp, bạn có thể thoát khỏi việc chỉ cần biết cách sử dụng một vài ký hiệu và mẫu cơ bản. Hướng dẫn này sẽ được giữ đơn giản nhất có thể để bạn có thể bắt đầu sử dụng chúng.

Sau đây là các ký hiệu sẽ sử dụng trong hướng dẫn này:

Ký hiệu	Mô tả
-	Chỉ định phạm vi ký tự trong dấu ngoặc.
^	Khớp với phần bắt đầu của một chuỗi.
$	Khớp với phần cuối 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.
*	Khớp với 0 hoặc nhiều ký tự đứng trước.
+	Khớp với 1 hoặc nhiều ký tự trước đó.
()	Nhóm các ký tự khớp nhau thành một.
[]	Khớp với bất kỳ ký tự nào bên trong dấu ngoặc.
[^]	Khớp với bất kỳ ký tự nào không nằm trong dấu ngoặc.
{n}	Khớp chính xác n trường hợp của ký tự trước đó.
{n,}	Phù hợp với n hoặc nhiều lần xuất hiện của ký tự trước đó

Các mẫu biểu thức chính quy đơn giản mà bạn có thể xây dựng bằng các ký hiệu này bao gồm:

Mẫu Regex	Mô tả
[0-9]	Khớp với một chữ số từ 0 đến 9
[a-zA-z0-9]	Đây là phạm vi kết hợp khớp với 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 với bất kỳ chuỗi nào bắt đầu bằng pro.
[^$]	Khớp với bất kỳ ký tự nào không phải là $.
(con)	Nhóm mẫu con.
a{3,}	Khớp với 3 hoặc nhiều lần xuất hiện của phần sau a (ví dụ, a, aa hoặc aaa).

Các hàm Regex là các công thức Excel xác định trước có thể được sử dụng để xác định một mẫu để tìm kiếm và thao tác những chuỗi văn bản. Hiện tại có 3 hàm regex. Chúng ta sẽ xem cách sử dụng chúng riêng biệt và với các hàm khác.

Tìm kiếm các mẫu

Hàm đầu tiên chúng ta sẽ xem là REGEXTEST. Hàm này lấy một chuỗi văn bản mà bạn muốn sử dụng để tìm kiếm và một mẫu regex, sau đó sử dụng mẫu sau để tìm kết quả khớp trong mẫu trước. 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_senstivity])

Hai tham số đầu tiên, string_to_search và regex_pattern_to_use rất dễ hiểu. Tham số [case_sensitivity] là tùy chọn - bất kỳ thứ gì được đặt trong dấu ngoặc vuông khi nói về cú pháp Excel đều là tùy chọn - và biểu thị bạn muốn tìm kiếm phân biệt chữ hoa chữ thường (0) hay không phân biệt chữ hoa chữ thường (1). Mặc định là phân biệt chữ hoa chữ thường.

Ví dụ sẽ sử dụng REGEXTEST để xem người dùng đã nhập địa chỉ email hợp lệ hay chưa bằ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 bằng cách sử dụng mẫu biểu thức chính quy bên dưới:

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

Nếu đặt công thức vào ô C3 và nhập john.doe@example.com vào ô B3, công thức sẽ trả về True vì nó khớp với chữ ký của email.

Dữ liệu bổ sung sử dụng Regex

Tiếp theo, hãy xem xét hàm REXEXEXTRACT. Hàm này trả về một chuỗi con (một phần của chuỗi) khớp với mẫu regex được cung cấp.

Cú pháp của hàm REXEXEXTRACT như sau:

REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

Tiếp tục với ví dụ về email, chúng ta hãy thêm một công thức vào ô B4 để trích xuất tên người dùng của phần email.

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 đã nhập vào B3.

Tìm và thay thế bằng Regex

Hàm regex cuối cùng mà 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ũng hỗ trợ RegEx. Nó lấy 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 đã xác định hay không. Nếu tìm thấy, nó sẽ thay thế chuỗi đó 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_senstivity])

Sau đâ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 để thay thế chuỗi con.
• number_of_occurrences: Trường hợp chính xác bạn muốn thay thế.

Sau đây là ví dụ về việc sử dụng hàm để thay thế phần tên người dùng 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.

Kết hợp Regex với các hàm khác

Bạn cũng có thể kết hợp các hàm regex với những hàm khác trong Excel. Ví dụ, bạn có thể kết hợp hàm REGEXTEST với 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ả.

Sau đây là một công thức ví dụ:

=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

Công thức này sử dụng câu lệnh IF để kiểm tra xem địa chỉ email đã nhập vào ô B6 có hợp lệ không và sau đó hiển thị This is a valid email address! nếu là TRUE hoặc The email address is invalid! nếu là FALSE. Ngoài ra, bạn có thể ghép công thức này với hàm FIND để tìm dữ liệu nhanh trong Excel.

Đây là một cách tốt để bắt đầu sử dụng RegEx trong Excel. Các trường hợp sử dụng và khả năng chỉ bị giới hạn bởi trí tưởng tượng của bạn.