Trong thế giới dữ liệu khổng lồ của Excel, việc tìm kiếm và lọc thông tin đôi khi giống như mò kim đáy bể. Tuy nhiên, sự xuất hiện của các hàm Biểu thức chính quy (Regex) đã thay đổi hoàn toàn cục diện này. Giờ đây, bạn có thể dễ dàng xác định chính xác những gì mình cần – từ các mẫu phức tạp, các phần khớp một phần cho đến việ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 chính là công cụ mạnh mẽ giúp bạn làm chủ dữ liệu một cách hiệu quả và nâng cao năng suất làm việc.
Biểu Thức Chính Quy (Regex) Là Gì?
Regex, viết tắt của Regular Expression (Biểu thức chính quy), là một chuỗi ký tự được sử dụng để định nghĩa một mẫu tìm kiếm. 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 một “chữ ký” email đang hoạt động.
Biểu thức chính quy không chỉ có trong Excel mà còn được 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 (môi trường phát triển tích hợp) và thậm chí cả Google Sheets – đối thủ cạnh tranh của Excel.
Mặc dù Regex nghe có vẻ phức tạp, đặc biệt 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 để áp dụng nó hiệu quả. Trong nhiều trường hợp, chỉ cần nắm vững một vài ký hiệu và mẫu cơ bản là đủ. 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 chúng 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 mới. |
* |
Khớp 0 hoặc nhiều lần ký tự đứng trước nó. |
+ |
Khớp 1 hoặc nhiều lần ký tự đứng trước nó. |
() |
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 trong dấu ngoặc vuông. |
{n} |
Khớp chính xác n lần ký tự đứng trước nó. |
{n,} |
Khớp n hoặc nhiều hơn số lần xuất hiện của ký tự đứng trước nó. |
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ố 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ự 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 số 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 được định nghĩa trước trong Excel, có thể được sử dụng để xác định một mẫu nhằm 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 Định Dạng Dữ Liệu Bằng Regex
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ự trùng khớp trong chuỗi. Hàm sẽ trả về kết quả là TRUE (Đúng) hoặc FALSE (Sai).
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 (chuỗi cần tìm kiếm) và regex_pattern_to_use (mẫu Regex cần sử dụng), rất dễ hiểu. Tham số [case_sensitivity] (phân biệt chữ hoa/chữ thường) là tùy chọn – bất cứ 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 muốn tìm kiếm phân biệt chữ hoa/chữ thường (0) hay không phân biệt (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 để kiểm tra 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 liệu nó có chứa địa chỉ email sử dụng mẫu Regex dưới đây hay không:
^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+.[a-zA-Z]{2,}$Nếu bạn đặ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.
Kết quả sử dụng hàm REGEXTEST để kiểm tra tính hợp lệ của địa chỉ email trong Excel
Hàm REGEXEXTRACT: Trích Xuất Dữ Liệu Theo Mẫu Bằng Regex
Tiếp theo, hãy cùng xem xét 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_senstivity])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 tên người dùng 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 ở ô B3.
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ế Dữ Liệu 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ũ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ế 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])Đâ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 khớp với mẫu Regex.
- number_of_occurrences: Số lần xuất hiện chính xác mà 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].
Sử dụng hàm REGEXREPLACE trong Excel để thay thế phần tên người dùng của email
Tận Dụng Sức Mạnh Regex Cùng 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, sau đó hiển thị “Đây là địa chỉ email hợp lệ!” nếu kết quả là TRUE hoặc “Địa chỉ email không hợp lệ!” nếu kết quả 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 theo các mẫu phức tạp.
Kết Luận
Mặc dù bài viết này không phải là một hướng dẫn mở rộng về biểu thức chính quy – điều đó đòi hỏi nhiều bài viết riêng biệt – nhưng đây là một khởi đầu 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 là vô hạn, chỉ bị giới hạn bởi trí tưởng tượng và sự sáng tạo của bạn. Hãy thử áp dụng Regex vào các tác vụ xử lý dữ liệu hàng ngày của bạn để thấy được sự khác biệt vượt trội về hiệu quả và độ chính xác!
