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 với các chuỗi văn bản phức tạp. Nhưng với các hàm Regex trong Excel, mọi thứ trở nên dễ dàng hơn bao giờ hết. 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, các phần 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 quá nhiều công sức. Hướng dẫn chi tiết này sẽ giúp bạn làm chủ biểu thức chính quy trong Excel, biến bạn thành chuyên gia xử lý dữ liệu chỉ với vài bước đơn giản.
Regex là gì? Sức mạnh của biểu thức chính quy trong xử lý văn bản
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ự trùng 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 cho bạn rằng mẫu 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 định dạng email hoạt động.
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, IDE và thậm chí cả Google Sheets. Mặc dù Regex nghe có vẻ phức tạp, và nó thực sự phức tạp 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 nhiều trường hợp, bạn chỉ cần biết cách sử dụng một vài ký hiệu và các mẫu cơ bản là đủ. Bài viết này sẽ giữ mọi thứ đơn giản nhất có thể để 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 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 một chuỗi. |
| $ | Khớp với phần 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. |
| * | Khớp 0 hoặc nhiều lần ký tự đứng trước. |
| + | Khớp 1 hoặc nhiều lần ký tự đứng trước. |
| () | Nhóm các ký tự khớp lại thành 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 bên trong dấu ngoặc vuông. |
| {n} | Khớp chính xác n lầ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 với các ký hiệu này bao gồm:
| 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 $. |
(con) |
Nhóm mẫu con. |
a{3,} |
Khớp 3 hoặc nhiều lần xuất hiện của chữ a (ví dụ: a, aa, hoặc aaa). |
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 để 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, và chúng ta sẽ tìm hiểu cách sử dụng chúng một cách riêng biệt và kết hợp với các hàm khác.
Hàm REGEXTEST: Kiểm tra mẫu dữ liệu trong Excel
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 kiếm sự trùng khớp trong chuỗi văn bản. 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, đã tự giải thích rõ ràng. 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à chỉ định xem bạn có 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.
Trong ví dụ của chúng ta, chúng ta sẽ sử dụng REGEXTEST để xem một 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 một địa chỉ email hay không bằng cách 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.
REGEXTEST trong Excel kiểm tra tính hợp lệ của địa chỉ email.
Hàm REGEXEXTRACT: Trích xuất thông tin cụ thể từ chuỗi văn bản
Tiếp theo, hãy 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 của đị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.
REGEXEXTRACT trong Excel trích xuất tên người dùng từ email.
Hàm REGEXREPLACE: Tìm và thay thế dữ liệu nâng cao với 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ũng 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 dùng để thay thế chuỗi con.number_of_occurrences: Lần xuất hiện chính xác mà bạn muốn thay thế.
Đây là một ví dụ 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].
REGEXREPLACE trong Excel thay thế phần username của đị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. 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 vào ô B6 có hợp lệ hay không và 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 dữ liệu trong Excel.
Bài viết này không nhằm mục đích trở thành 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. 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 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 những kiến thức này để tối ưu hóa quy trình làm việc và nâng cao hiệu quả xử lý dữ liệu của mình ngay hôm nay!
