Việc lọc và tìm kiếm dữ liệu trong Excel đôi khi có thể giống như một trò đoán mò, đặc biệt khi bạn cần xử lý các chuỗi ký tự phức tạp hoặc mẫu dữ liệu không đồng nhất. Đây là lúc các hàm Regex (Biểu thức chính quy) phát huy sức mạnh, giúp bạn 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ả.
Regex Là Gì Và Tại Sao Nó Quan Trọng Trong Excel?
Regex (Regular Expression – Biểu thức chính quy) là một chuỗi các ký tự dùng để tạo ra một mẫu tìm kiếm, giúp bạn tìm kiếm và khớp các chuỗi văn bản hoặc dãy ký tự cụ thể. 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 vào trang đăng nhập không hợp lệ? Đó chính là một ví dụ về việc sử dụng Regex để kiểm tra định dạng email.
Biểu thức chính quy không chỉ có 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, IDE và thậm chí cả Google Sheets. Mặc dù Regex nghe có vẻ phức tạp và thực sự là như vậy 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 chúng một cách hiệu quả trong Excel. 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.
Để giúp bạn bắt đầu sử dụng Regex trong Excel một cách đơn giản nhất, dưới đây là những ký hiệu cơ bản thường được sử dụng:
| Ký hiệu | Mô tả |
|---|---|
- |
Chỉ định một phạm vi ký tự bên 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 kết thúc của chuỗi. |
. |
Khớp với bất kỳ ký tự nào ngoại trừ ký tự xuống dòng. |
* |
Khớp 0 hoặc nhiều lần xuất hiện của ký tự đứng trước. |
+ |
Khớp 1 hoặc nhiều lần xuất hiện của ký tự đứng trước. |
() |
Nhóm các ký tự được khớp thành một. |
[] |
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 hơn lần xuất hiện của ký tự đứng trước. |
Với các ký hiệu này, bạn có thể xây dựng các mẫu Regex đơn giản như sau:
| Mẫu Regex | Mô tả |
|---|---|
[0-9] |
Khớp một chữ số duy nhất từ 0 đến 9. |
[a-zA-z0-9] |
Một phạm vi kết hợp khớp một ký tự duy nhất 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 ký tự a (ví dụ: a, aa, hoặc aaa). |
Hiện tại, Excel cung cấp ba hàm Regex được định nghĩa sẵn, giúp bạn xác định mẫu để tìm kiếm và thao tác với các chuỗi văn bản. Chúng ta sẽ cùng tìm hiểu cách sử dụng từng hàm một cách riêng lẻ và kết hợp với các hàm khác.
Tìm Kiếm Các Mẫu Ký Tự Với Hàm REGEXTEST
Hàm đầu tiên mà chúng ta sẽ tìm hiểu là REGEXTEST. Hàm này nhận một chuỗi văn bản để tìm kiếm và một mẫu Regex, sau đó sử dụng mẫu đó để tìm sự trùng khớp trong chuỗi. Hàm sẽ trả về giá trị TRUE nếu tìm thấy sự trùng khớp hoặc FALSE nếu không tìm thấy.
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 (những gì đặt trong 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/chữ thường (0) hay không phân biệt (1). Giá trị mặc định là phân biệt chữ hoa/chữ thường.
Ví dụ, chúng ta sẽ sử dụng REGEXTEST để kiểm tra xem 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 liệu nó có chứa địa chỉ email phù hợp với mẫu Regex dưới đây hay không:
^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$Nếu chúng ta đặt công thức này vào ô C3 và nhập [email protected] vào ô B3, kết quả sẽ trả về TRUE vì chuỗi đó khớp với định dạng của một địa chỉ email.
Minh họa hàm REGEXTEST trong Excel kiểm tra định dạng email hợp lệ trả về giá trị TRUE
Trích Xuất Dữ Liệu Cụ Thể Bằng 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 phần tên người dùng (username) của đị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 @ từ địa chỉ email được nhập trong ô B3.
Ví dụ hàm REGEXEXTRACT trong Excel trích xuất phần tên người dùng từ địa chỉ email
Tìm Và Thay Thế Dữ Liệu Với Hàm REGEXREPLACE
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 hỗ trợ thêm 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 sẽ thay thế chuỗi con tìm được.
- number_of_occurrences: Số lần chính xác mà bạn muốn thay thế (ví dụ: chỉ thay thế lần xuất hiện thứ nhất, thứ hai, v.v.).
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].
Ứng dụng hàm REGEXREPLACE trong Excel để thay thế phần tên người dùng trong địa chỉ 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 giải pháp 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 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 công thức ví dụ:
=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. Nếu hợp lệ, 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ũng có thể kết hợp các hàm Regex với hàm FIND để tìm dữ liệu nhanh chóng trong Excel.
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ì chủ đề này đòi hỏi 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 của Regex chỉ bị giới hạn bởi sự sáng tạo của bạn trong việc xử lý và tối ưu hóa dữ liệu. Nếu bạn có bất kỳ thắc mắc hay muốn chia sẻ các ứng dụng thú vị khác của Regex trong Excel, đừng ngần ngại để lại bình luận phía dưới!
