Hướng Dẫn Sử Dụng Hàm Regex Trong Excel: Thao Tác Dữ Liệu Mạnh Mẽ

Bạn có bao giờ cảm thấy việc lọc và tìm kiếm dữ liệu trong Excel giống như một trò chơi đoán mò? Đặc biệt với những chuỗi ký tự phức tạp, việc thao tác thủ công có thể tốn rất nhiều thời gian và dễ gây sai sót. Đó là lúc sức mạnh của biểu thức chính quy (Regex) và các hàm Regex chuyên biệt trong Excel phát huy tác dụng. Regex giúp bạn xác định chính xác những gì bạn 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ột cách dễ dàng và hiệu quả. Bài viết này sẽ hướng dẫn chi tiết cách bạn có thể tận dụng các hàm Regex mạnh mẽ này để biến Excel thành công cụ xử lý dữ liệu linh hoạt hơn bao giờ hết.

Regex Là Gì? Sức Mạnh Đằng Sau Các Chuỗi Ký Tự

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 một chuỗi văn bản hoặc một dãy ký tự để tìm sự trùng khớp. Bạn đã 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ệ chưa? Đó 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ó trong Excel mà còn có sẵ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 – đối thủ cạnh tranh của Excel.

Regex nghe có vẻ phức tạp, và đúng là vậy nếu bạn muốn sử dụng nó đế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 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à mẫu cơ bản. Dưới đây là bảng các ký hiệu phổ biến mà 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ự liền trước.
+	Khớp 1 hoặc nhiều lần ký tự liền trước.
()	Nhóm các ký tự khớp lại thành một.
[]	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ự liền trước.
{n,}	Khớp n hoặc nhiều lần của ký tự liền trước.

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

Mẫu Regex	Mô tả
[0-9]	Khớp một chữ số đơn từ 0 đến 9.
[a-zA-Z0-9]	Phạm vi kết hợp 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 ký tự 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 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 riêng lẻ cũng như kết hợp với các hàm khác.

Hàm REGEXTEST: Kiểm Tra Tính Hợp Lệ Của 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ự trùng khớp trong chuỗi. Hàm sẽ trả về kết quả là 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. 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/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 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 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ì chuỗi đó khớp với định dạng của một địa chỉ email.

Kết quả sử dụng hàm REGEXTEST trong Excel để kiểm tra định dạng email hợp lệ, hiển thị TRUE.

Hàm REGEXEXTRACT: Trích Xuất Dữ Liệu Theo Mẫu

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

Công thức sẽ trông như thế này:

=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 đã nhập ở ô B3.

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

Hàm REGEXREPLACE: Tìm Kiếm và Thay Thế Thông Minh

Hàm Regex cuối cùng mà 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ũ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_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 khớp.
• number_of_occurrences: Lần xuất hiện cụ thể 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].

Cách dùng hàm REGEXREPLACE trong Excel để thay thế phần tên người dùng trong email từ "john.doe" thành "jane.doe".

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 phù 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 đã nhập trong ô 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 điều này với hàm FIND để nhanh chóng tìm dữ liệu trong Excel.

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ẽ yêu cầu nhiều bài viết riêng – nhưng đâ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 trí tưởng tượng của bạn. Hãy bắt đầu thực hành ngay hôm nay để khai phá toàn bộ tiềm năng của việc xử lý dữ liệu với Regex trong Excel!