Regex Trong Excel: Cách Tìm Kiếm, Trích Xuất Và Thay Thế Dữ Liệu Hiệu Quả

Khi làm việc với các bảng tính Excel chứa lượng lớn dữ liệu, việc lọc và tìm kiếm thông tin theo các tiêu chí phức tạp thường trở thành một thách thức lớn. Các phương pháp truyền thống đôi khi không đủ linh hoạt để xử lý các mẫu dữ liệu không đồng nhất hoặc yêu cầu trích xuất cụ thể. Đây chính là lúc biểu thức chính quy (Regex) và các hàm Regex trong Excel phát huy tối đa sức mạnh của mình. Với Regex, bạn có thể dễ dàng xác định chính xác những gì mình cần – dù đó là các mẫu phức tạp, các phần khớp một phần hay việc trích xuất dữ liệu có cấu trúc – một cách nhanh chóng và hiệu quả, giúp nâng cao đáng kể khả năng quản lý và phân tích dữ liệu của bạn.

Regex Là Gì Và Tại Sao Cần Dùng Trong Excel?

Regex (viết tắt của Regular Expression), hay biểu thức chính quy, là một chuỗi các ký tự đặc biệt tạo thành một mẫu tìm kiếm. Mẫu này được sử dụng để tìm kiếm, trích xuất hoặc thay thế các chuỗi văn bản hoặc cá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 trên trang đăng nhập không hợp lệ không? Đó chính là một ví dụ về việc sử dụng Regex để kiểm tra một “chữ ký” email cụ thể.

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, môi trường phát triển tích hợp (IDE), và thậm chí cả đối thủ cạnh tranh của Excel là Google Sheets.

Nghe có vẻ phức tạp, và thực tế là Regex khá phức tạp nếu bạn muốn khai thác toàn bộ tiềm năng của nó. Tuy nhiên, bạn không cần phải là một lập trình viên chuyên nghiệp để sử dụng Regex một cách hiệu quả trong Excel. Trong nhiều trường hợp, việc nắm vững một vài ký hiệu và mẫu cơ bản là đủ để bắt đầu. Để bạn có thể dễ dàng áp dụng Regex vào công việc ngay lập tức, phần hướng dẫn này sẽ trình bày một cách đơn giản nhất có thể.

Dưới đây là bảng tổng hợp các ký hiệu Regex cơ bản mà chúng ta sẽ sử dụng trong bài viết 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 của ký tự đứng trước.
+	Khớp 1 hoặc nhiều lần của ký tự đứng trước.
()	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 có bên 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 n lần xuất hiện của ký tự đứng trước.

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

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à một phạm vi kết hợp khớp một ký tự duy nhất từ chữ cái 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 3 lần xuất hiện của chữ a (ví dụ: a, aa, hoặc aaa).

Các hàm Regex trong Excel là các công thức được định nghĩa sẵn, giúp bạn xác định một mẫu để tìm kiếm và thao tác với 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ẻ cũng như kết hợp với các hàm khác.

Cách Tìm Kiếm Các Mẫu Dữ Liệu Với Hàm REGEXTEST

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ự khớp trong chuỗi. Kết quả trả về của hàm sẽ là TRUE (nếu tìm thấy sự 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_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), khá dễ hiểu. Tham số [case_sensitivity] là tùy chọn (bất kỳ tham số nào đặt trong dấu ngoặc vuông trong cú pháp Excel đều là tùy chọn). Tham số này dùng để 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 chữ hoa/thường (mặc định) hoặc 1 cho không phân biệt chữ hoa/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, 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 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 chúng ta đặ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 địa chỉ email.

Minh họa hàm REGEXTEST trong Excel kiểm tra định dạng email hợp lệ.

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 sẽ 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_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 phần 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 vào ô B3.

Ví dụ hàm REGEXEXTRACT trong Excel để trích xuất 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ẽ tìm hiểu là REGEXREPLACE. Hàm này tương tự như hàm REPLACE của Excel, nhưng hỗ trợ thêm biểu thức chính quy. Hàm này nhận một 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_senstivity])

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ẽ được dùng để thay thế chuỗi con khớp.
• number_of_occurrences: 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 địa chỉ email bằng một chuỗi văn bản khác:

=REGEXREPLACE(B3, "^[^@]+", "jane.doe")

Giá trị trong ô 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ướng dẫn sử dụng hàm REGEXREPLACE trong Excel để thay thế phần username của email.

Kết Hợp Các 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 công thức 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 để hiển thị thông báo phù hợp dựa trên kết quả kiểm tra.

Đâ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 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 hàm này với hàm FIND để tìm kiếm dữ liệu nhanh chóng trong Excel.

Mặc dù đây không phải là một hướng dẫn chuyên sâu toàn diện 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 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 ứng dụng của Regex chỉ bị giới hạn bởi trí tưởng tượng và nhu cầu xử lý dữ liệu của bạn. Hãy thực hành và khám phá để biến Excel thành công cụ mạnh mẽ hơn nữa!