Việc lọc và tìm kiếm dữ liệu trong Excel đôi khi có thể trở thành một thách thức, đặc biệt với các chuỗi ký tự phức tạp. Đây là lúc các hàm Regex (biểu thức chính quy) phát huy sức mạnh, biến công việc tưởng chừng khó khăn này trở nên dễ dàng hơn bao giờ hết. Với Regex trong Excel, bạn có thể 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 không hoàn chỉnh 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.
Bài viết này sẽ đưa bạn khám phá thế giới Regex trong Excel, từ những khái niệm cơ bản đến cách áp dụng các hàm chuyên biệt để nâng cao hiệu quả xử lý dữ liệu của bạn.
Regex Là Gì? Vì Sao Nó Quan Trọng Trong Excel?
Regex (Regular Expression) là một dạng 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 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 cho bạn 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 đang hoạt động.
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, IDE (môi trường phát triển tích hợp) và thậm chí cả đối thủ của Excel là Google Sheets.
Regex có thể nghe có vẻ phức tạp, và đúng là vậy nếu bạn muốn sử dụng nó ở mức tối đa tiềm năng. 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 Excel. 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à bạn đã có thể bắt đầu. 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 ứng dụng ngay.
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ự bên trong dấu ngoặc vuông. |
^ |
Khớp với phần đầu của một chuỗi. |
$ |
Khớp với phần cuối của một chuỗi. |
. |
Khớp 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 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 bất kỳ ký tự nào bên trong dấu ngoặc vuông. |
[^] |
Khớp 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 lần hoặc nhiều hơn của ký tự đứng trước. |
Dưới đây là 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:
| Mẫu Regex | Mô tả |
|---|---|
[0-9] |
Khớp một chữ số duy nhất từ 0 đến 9. |
[a-zA-z0-9] |
Phạm vi kết hợp này 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 $. |
(con) |
Nhóm mẫu con. |
a{3,} |
Khớp 3 lần hoặc nhiều hơn của ký tự 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ể 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 độc lập cũng như kết hợp với các hàm khác.
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 bạn muốn tìm kiếm và một mẫu Regex, sau đó sử dụng mẫu này để tìm sự 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 (chuỗi cần tìm) và regex_pattern_to_use (mẫu Regex cần sử dụng), khá rõ ràng. Tham số [case_sensitivity] là tùy chọn (mọi thứ 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 để xem liệu 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 nó có chứa địa chỉ email hay không, sử dụng mẫu Regex sau:
^[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 email hợp lệ.
REGEXEXTRACT: Trích Xuất Dữ Liệu Theo Mẫu
Tiếp theo, chúng ta 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ụ 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 (username) 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 trong Excel trích xuất tên người dùng từ địa chỉ email.
REGEXREPLACE: Tìm Và Thay Thế Dữ Liệu Bằng Biểu Thức Chính Quy
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ó 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 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_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 để thay thế chuỗi con tìm thấy.
- number_of_occurrences: Số 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 (username) 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àm REGEXREPLACE trong Excel thay thế phần tên người dùng trong địa chỉ email.
Kết Hợp Các Hàm Regex Với Công Thức 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 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 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 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 hàm này với hàm FIND để tìm kiếm dữ liệu trong Excel một cách nhanh chóng.
Mặc dù đây không phải là một hướng dẫn chuyên sâu 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 đây 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 bắt đầu thực hành ngay hôm nay để khai thác tối đa sức mạnh của Excel!
