Regex trong Excel: Nâng Cao Kỹ Năng Xử Lý Dữ Liệu Với Sức Mạnh Của Biểu Thức Chính Quy

Việc lọc và tìm kiếm dữ liệu trong Excel đôi khi giống như một trò chơi đoán mò, đặc biệt với các chuỗi văn bản phức tạp. Các hàm Regex đã thay đổi hoàn toàn điều đó. Giờ đây, 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, đối sánh một phần hoặc trích xuất dữ liệu có cấu trúc – mà không tốn nhiều công sức. Đây là công cụ mạnh mẽ giúp tối ưu hóa việc quản lý và phân tích dữ liệu trong bảng tính.

Regex Là Gì?

Regex, hay Biểu thức chính quy (Regular Expression), là một loại mẫu được sử dụng để tìm kiếm sự khớp trong một chuỗi văn bản hoặc một chuỗi ký tự. 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ó 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, môi trường phát triển tích hợp (IDE), và cả đối thủ của Excel là Google Sheets.

Regex nghe có vẻ phức tạp, và thực tế nó phức tạp nếu bạn muốn sử dụng hết tiềm năng của nó. Tuy nhiên, bạn không cần phải là một lập trình viên để sử dụng Regex một cách hiệu quả. Trong nhiều trường hợp, bạn chỉ cần nắm vững một vài ký hiệu và các mẫu cơ bản là đủ để bắt đầu. Bài viết này sẽ giữ mọi thứ thật đơn giản để bạn có thể áp dụng ngay.

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.
^	Khớp với phần bắt đầu của chuỗi.
$	Khớp với phần cuối của chuỗi.
.	Khớp với bất kỳ ký tự nào trừ ký tự xuống dòng mới.
*	Khớp với 0 hoặc nhiều lần xuất hiện của ký tự đứng trước.
+	Khớp với 1 hoặc nhiều lần xuất hiện của ký tự đứng trước.
()	Nhóm các ký tự khớp lại với nhau.
[]	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 bên 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 hơn 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 này:

Mẫu Regex	Mô tả
[0-9]	Khớp một chữ số duy nhất từ 0 đến 9.
[a-zA-Z0-9]	Đây là sự kết hợp phạm vi 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 lần xuất hiện của chữ a (ví dụ: a, aa, hoặc aaa).

Các hàm Regex trong Excel là các công thức được định nghĩa trước, giúp bạn xác định một mẫu để tìm kiếm và thao tá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 riêng lẻ cũng như kết hợp với các hàm khác.

Hàm REGEXTEST: Kiểm Tra Mẫu Dữ Liệu

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), khá dễ hiểu. Tham số [case_sensitivity] là tùy chọn (bất kỳ thứ gì đặt trong dấu ngoặc vuông khi nói về cú pháp Excel đều là tùy chọn) và cho biết bạn có muốn tìm kiếm phân biệt chữ hoa/thường hay không (0 cho phân biệt, 1 cho không phân biệt). 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 để kiểm tra xem người dùng đã nhập một đị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 liệu nó có chứa một đị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 này vào ô C3 và nhập [email protected] vào ô B3, nó sẽ trả về TRUE vì chuỗi đó khớp với định dạng của một email.

Minh họa hàm REGEXTEST trong Excel kiểm tra định dạng địa chỉ email hợp lệ

Hàm REGEXEXTRACT: Trích Xuất Dữ Liệu Theo Mẫu

Tiếp theo, hãy cùng xem 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ụ 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ào:

=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. Mẫu ([^@]+) có nghĩa là khớp và nhóm bất kỳ ký tự nào (trừ @) xuất hiện một hoặc nhiều lần.

Ví dụ về hàm REGEXEXTRACT trong Excel để trích xuất tên người dùng từ địa chỉ email

Hàm REGEXREPLACE: Tìm và Thay Thế Bằng Regex

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ó thêm hỗ trợ Regex. Nó nhận 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 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_sensitivity])

Đâ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ẽ thay thế chuỗi con khớp với mẫu Regex.
• number_of_occurrences: (Tùy chọn) Chỉ định lần xuất hiện chính xác bạn muốn thay thế.

Dưới đây là một 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à [email protected], và sau khi chúng ta nhập công thức trên vào ô C3, nó sẽ trả về [email protected]. Mẫu ^[^@]+ khớp với bất kỳ ký tự nào không phải @ từ đầu chuỗi (^), xuất hiện một hoặc nhiều lần (+).

Cách dùng hàm REGEXREPLACE trong Excel để thay thế phần tên người dùng của email

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 thức mạnh mẽ hơn. Ví dụ, bạn có thể kết hợp hàm REGEXTEST với một câu lệnh IF của Excel để 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à đị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 trong ô B3 có hợp lệ hay không. Nếu REGEXTEST trả về TRUE, nó sẽ hiển thị "Đây là địa chỉ email hợp lệ!", nếu không, nó sẽ hiển thị "Địa chỉ email không hợp lệ!". Ngoài ra, bạn có thể kết hợp các hàm Regex này với hàm FIND để nhanh chóng tìm kiếm dữ liệu trong Excel theo các mẫu phức tạp.

Mặc dù đây không phải là một hướng dẫn toàn diện về biểu thức chính quy – vì điều đó sẽ cần nhiều bài viết riêng biệt – nhưng nó là một cách tuyệt vời để 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 sự sáng tạo của bạn. Hãy thử áp dụng ngay để nâng cao hiệu suất làm việc với dữ liệu trong Excel!