Regex trong Excel: Khai Thác Sức Mạnh Biểu Thức Chính Quy Để Xử Lý Dữ Liệu

Trong thế giới Excel, việc lọc và tìm kiếm dữ liệu có thể trở nên phức tạp, đặc biệt khi bạn cần xử lý các chuỗi ký tự với cấu trúc không đồng nhất hoặc yêu cầu độ chính xác cao. Đây chính là lúc Regex trong Excel (hay Biểu thức chính quy) trở thành một công cụ không thể thiếu. Regex giúp bạn xác định chính xác những gì cần tìm – từ các mẫu phức tạp, so khớp một phần đế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ả, nâng tầm khả năng thao tác dữ liệu của bạn lên một cấp độ mới.

Regex Là Gì? Sức Mạnh Của Các Biểu Thức Chính Quy

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 sự khớp trong một chuỗi văn bản hoặc một dãy các 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 là 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 phổ biế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, môi trường phát triển tích hợp (IDE), và thậm chí cả đối thủ của Excel là Google Sheets.

Nghe có vẻ phức tạp, và nó thực sự phức tạp nếu bạn muốn khai thác toàn bộ 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 Regex hiệu quả. Trong nhiều trường hợp, chỉ cần nắm vững vài ký hiệu và mẫu cơ bản là bạn đã có thể bắt đầu ứng dụng chúng. 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ể bắt đầu sử dụng Regex ngay lập tức.

Dưới đây là các ký hiệu Regex cơ bản sẽ được 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.
^	So khớp với phần bắt đầu của chuỗi.
$	So khớp với phần kết thúc của chuỗi.
.	So khớp bất kỳ ký tự nào ngoại trừ ký tự xuống dòng.
*	So khớp 0 hoặc nhiều lần ký tự đứng trước.
+	So khớp 1 hoặc nhiều lần ký tự đứng trước.
()	Nhóm các ký tự được khớp thành một nhóm.
[]	So khớp bất kỳ ký tự nào bên trong dấu ngoặc vuông.
[^]	So khớp bất kỳ ký tự nào không nằm bên trong dấu ngoặc vuông.
{n}	So khớp chính xác n lần ký tự đứng trước.
{n,}	So khớp n hoặc nhiều hơn n lần ký tự đứng trước.

Dưới đây là một số mẫu Regex đơn giản mà bạn có thể xây dựng bằng các ký hiệu trên:

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

Các hàm Regex là các công thức Excel được định nghĩa trước, có thể được sử dụng để định nghĩa 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 riêng lẻ và kết hợp với các hàm khác.

Hàm REGEXTEST: Kiểm Tra Sự Khớp 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 mà bạn muốn tìm kiếm và một mẫu Regex, sau đó sử dụng mẫu này để tìm một sự khớp trong chuỗi đó. Hàm sẽ trả về giá trị 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 và regex_pattern_to_use, là tự giải thích. Tham số [case_sensitivity] là tùy chọn (bất kỳ thứ gì trong dấu ngoặc vuông trong cú pháp Excel đều là tùy chọn) và chỉ định xem bạn muốn tìm kiếm có phân biệt chữ hoa/thường (0) hay không (1). Mặc định là có 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 người dùng đã nhập địa chỉ email hợp lệ hay chưa bằng cách 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 liệu 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 [email protected] vào ô B3, nó sẽ trả về TRUE vì địa chỉ đó khớp với định dạng của một email.

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

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) 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 từ địa chỉ email.

Công thức sẽ trông như sau:

=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 ở ô B3.

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

Hàm REGEXREPLACE: Tìm và Thay Thế Chuỗi Nâng Cao

Hàm Regex cuối cùng chúng ta sẽ tìm hiểu 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 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 để thay thế chuỗi con.
• number_of_occurrences: 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].

Hướng dẫn sử dụng hàm REGEXREPLACE để thay thế phần đầu 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. 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 thích hợp dựa trên kết quả.

Dưới đâ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 ô B6 có hợp lệ hay không, sau đó hiển thị “Đây là địa chỉ email hợp lệ!” nếu nó là TRUE hoặc “Địa chỉ email không hợp lệ!” nếu nó 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 kiếm dữ liệu trong Excel.

Đây không phải là một hướng dẫn chuyên sâu về biểu thức chính quy – điều đó sẽ đòi hỏi nhiều bài viết riêng. Tuy nhiên, đâ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 sự sáng tạo của bạn. Hãy bắt đầu áp dụng Regex vào công việc của mình để thấy sự khác biệt trong hiệu suất xử lý dữ liệu!