Kiểm tra xem 500 địa chỉ email trong bảng tính có hợp lệ hay không từng tốn của tôi 45 phút mỗi ngày. Phương pháp cũ là sao chép từng dòng vào công cụ trực tuyến, kiểm tra thủ công ký tự đặc biệt, hoặc dùng công thức phức tạp với FIND kết hợp IF lồng nhau. Giờ đây, cùng một công việc chỉ mất 4 phút nhờ hàm REGEXTEST trong Microsoft 365.

Tại sao FIND và SEARCH không đủ mạnh

Các hàm truyền thống trong Excel như FIND và SEARCH chỉ thực hiện tìm kiếm văn bản đơn giản. FIND phân biệt chữ hoa chữ thường nhưng không hỗ trợ ký tự đại diện. SEARCH hỗ trợ ký tự đại diện cơ bản như dấu hỏi chấm và dấu sao, nhưng không thể xử lý các mẫu phức tạp.

Để kiểm tra một email hợp lệ với phương pháp cũ, tôi phải viết công thức kết hợp nhiều hàm. Đầu tiên dùng FIND để tìm ký tự @ trong chuỗi. Tiếp theo kiểm tra có dấu chấm sau @ hay không. Sau đó đếm số lượng @ để đảm bảo chỉ có một. Cuối cùng kiểm tra không có khoảng trắng trong địa chỉ. Công thức kết quả dài 8 dòng và dễ bị lỗi khi dữ liệu thay đổi.

Hạn chế cụ thể của phương pháp cũ:

• Không kiểm tra được cấu trúc phức tạp
• Công thức dài và khó bảo trì
• Không xử lý được nhiều điều kiện đồng thời
• Báo sai khi gặp trường hợp đặc biệt

Cú pháp cơ bản của REGEXTEST

Hàm REGEXTEST kiểm tra xem một phần văn bản có khớp với mẫu biểu thức chính quy hay không. Kết quả trả về là TRUE nếu tìm thấy khớp và FALSE nếu không.

Cú pháp đầy đủ: REGEXTEST(text, pattern, [case_sensitivity])

Ba tham số chính:

• text: Chuỗi văn bản cần kiểm tra
• pattern: Mẫu biểu thức chính quy để tìm kiếm
• case_sensitivity: Tùy chọn phân biệt chữ hoa chữ thường (0 = có phân biệt, 1 = không phân biệt)

Tham số thứ ba là tùy chọn. Mặc định, hàm phân biệt chữ hoa chữ thường. Để bỏ qua sự phân biệt này, thêm số 1 vào cuối công thức.

Điểm mạnh của REGEXTEST là khả năng sử dụng biểu thức chính quy theo chuẩn PCRE2. Đây là phiên bản hiện đại và linh hoạt, hỗ trợ đầy đủ các tính năng nâng cao như nhóm bắt giữ, điều kiện phức tạp, và tìm kiếm theo vị trí.

Kiểm tra email hợp lệ trong 2 giây

Ứng dụng đầu tiên tôi thử là kiểm tra địa chỉ email. Mẫu biểu thức chính quy cho email chuẩn trông phức tạp nhưng rất mạnh mẽ.

Giả sử cột A chứa các địa chỉ email cần kiểm tra. Trong ô B2, nhập công thức:

=REGEXTEST(A2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")

Phân tích mẫu này:

• ^ đánh dấu bắt đầu chuỗi
• [a-zA-Z0-9._%+-]+ khớp với phần tên người dùng (chữ cái, số, dấu chấm, gạch dưới, phần trăm, dấu cộng, dấu trừ)
• @ ký tự @ bắt buộc
• [a-zA-Z0-9.-]+ khớp với tên miền (chữ cái, số, dấu chấm, dấu gạch ngang)
• \. dấu chấm trước đuôi tên miền (dấu gạch chéo ngược để thoát ký tự đặc biệt)
• [a-zA-Z]{2,} đuôi tên miền tối thiểu 2 ký tự
• $ đánh dấu kết thúc chuỗi

Kéo công thức xuống toàn bộ cột B. Mỗi ô sẽ hiển thị TRUE nếu email hợp lệ hoặc FALSE nếu không đúng định dạng.

Kết quả thực tế:

• Kiểm tra 500 email trong 4 phút thay vì 45 phút
• Độ chính xác 99.8% (so với 87% của phương pháp cũ)
• Không cần sửa công thức khi dữ liệu thay đổi

Tích hợp với Data Validation để ngăn lỗi nhập liệu

REGEXTEST trở nên mạnh mẽ hơn khi kết hợp với tính năng Data Validation của Excel. Thay vì kiểm tra sau khi nhập, có thể ngăn người dùng nhập dữ liệu sai ngay từ đầu.

Các bước thiết lập:

1. Chọn vùng ô cần áp dụng kiểm tra (ví dụ cột A từ A2 đến A100)
2. Vào thẻ Data trên thanh công cụ
3. Nhấn Data Validation trong nhóm Data Tools
4. Chọn Custom trong danh sách Allow
5. Trong ô Formula, nhập: =REGEXTEST(A2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$")
6. Điều chỉnh tham chiếu ô nếu cần (A2 cho dòng đầu tiên của vùng chọn)
7. Nhấn OK

Khi người dùng nhập địa chỉ email không hợp lệ, Excel sẽ hiển thị cảnh báo và không cho phép lưu dữ liệu. Họ phải sửa lại cho đúng định dạng trước khi tiếp tục.

Tôi áp dụng phương pháp này cho form đăng ký khách hàng. Lỗi nhập email giảm từ 23% xuống dưới 2% chỉ sau một tuần.

Các mẫu kiểm tra hữu ích khác

Số điện thoại Việt Nam

Kiểm tra số điện thoại di động Việt Nam với đầu số 03, 05, 07, 08, 09:

=REGEXTEST(A2,"^(03|05|07|08|09)[0-9]{8}$")

Mẫu này yêu cầu bắt đầu bằng một trong các đầu số, theo sau là 8 chữ số. Tổng cộng 10 số đúng theo quy định.

Mã sản phẩm có cấu trúc

Giả sử mã sản phẩm theo định dạng: 2 chữ cái viết hoa, dấu gạch ngang, 4 chữ số (ví dụ: AB-1234).

=REGEXTEST(A2,"^[A-Z]{2}-[0-9]{4}$")

Công thức này đảm bảo mã sản phẩm đúng chuẩn. Bất kỳ mã nào không theo định dạng sẽ được đánh dấu FALSE.

Mã bưu chính 5 hoặc 6 chữ số

Một số hệ thống sử dụng mã bưu chính 5 chữ số hoặc 6 chữ số:

=REGEXTEST(A2,"^[0-9]{5,6}$")

Ký hiệu {5,6} có nghĩa là từ 5 đến 6 lần xuất hiện. Mẫu này chấp nhận cả hai dạng mã bưu chính.

Kiểm tra chứa chữ số

Đơn giản hơn, kiểm tra xem ô có chứa bất kỳ chữ số nào không:

=REGEXTEST(A2,"[0-9]")

Trả về TRUE nếu có ít nhất một chữ số trong chuỗi. Hữu ích khi cần phân loại dữ liệu.

Highlight dữ liệu không hợp lệ với Conditional Formatting

Kết hợp REGEXTEST với Conditional Formatting giúp tô màu các ô không khớp mẫu. Điều này giúp phát hiện lỗi nhanh hơn trong tập dữ liệu lớn.

Quy trình thiết lập:

1. Chọn vùng dữ liệu cần kiểm tra (ví dụ A2:A500)
2. Vào thẻ Home trên thanh công cụ
3. Nhấn Conditional Formatting trong nhóm Styles
4. Chọn New Rule từ menu thả xuống
5. Chọn Use a formula to determine which cells to format
6. Nhập công thức: =NOT(REGEXTEST(A2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$"))
7. Nhấn Format, chọn màu nền đỏ nhạt hoặc màu cảnh báo
8. Nhấn OK hai lần

Hàm NOT đảo ngược kết quả của REGEXTEST. Các ô chứa email không hợp lệ sẽ được tô màu tự động. Khi dữ liệu thay đổi, màu cũng cập nhật theo thời gian thực.

Tôi sử dụng phương pháp này để kiểm tra 2000 mã sản phẩm trong file inventory. Các mã sai định dạng nổi bật ngay lập tức, giúp team sửa lỗi trong 30 phút thay vì 4 giờ kiểm tra thủ công.

So sánh hiệu suất với phương pháp truyền thống

Để kiểm tra 1000 địa chỉ email, đây là thời gian và độ chính xác của từng phương pháp:

Kiểm tra thủ công:

• Thời gian: 90 phút
• Độ chính xác: 82%
• Dễ bỏ sót lỗi khi mệt

Công thức FIND và IF lồng nhau:

• Thời gian thiết lập công thức: 20 phút
• Thời gian tính toán: 8 phút với 1000 dòng
• Độ chính xác: 87%
• Công thức dài 10 dòng, khó bảo trì

REGEXTEST:

• Thời gian thiết lập: 2 phút
• Thời gian tính toán: 0.5 giây với 1000 dòng
• Độ chính xác: 99.8%
• Công thức ngắn gọn, dễ đọc

Chênh lệch rõ ràng nhất là tốc độ tính toán. File có 10000 dòng với phương pháp cũ mất 4 phút để recalculate mỗi khi thay đổi dữ liệu. Với REGEXTEST, recalculation hoàn tất trong 2 giây.

Làm việc với các mẫu phức tạp hơn

REGEXTEST hỗ trợ các tính năng nâng cao của biểu thức chính quy chuẩn PCRE2. Điều này cho phép xử lý các trường hợp phức tạp mà trước đây không thể làm trong Excel.

Nhóm bắt giữ (Capturing Groups):

Kiểm tra email và đảm bảo đuôi tên miền phải là com, net, hoặc org:

=REGEXTEST(A2,"^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.(com|net|org)$")

Dấu ngoặc đơn tạo nhóm lựa chọn. Mẫu chỉ chấp nhận ba đuôi tên miền cụ thể.

Lookahead và Lookbehind:

Kiểm tra mật khẩu phải có ít nhất một chữ cái viết hoa, một chữ số, độ dài tối thiểu 8 ký tự:

=REGEXTEST(A2,"^(?=.*[A-Z])(?=.*[0-9]).{8,}$")

Cú pháp (?=.*[A-Z]) là positive lookahead, kiểm tra có ít nhất một chữ viết hoa mà không tiêu thụ ký tự. Kỹ thuật này cho phép áp dụng nhiều điều kiện độc lập.

Xử lý trường hợp đặc biệt

Một số địa chỉ email hợp lệ có ký tự đặc biệt như dấu ngoặc đơn hoặc dấu ngoặc vuông. Mẫu email cơ bản không nhận diện được những trường hợp này.

Để hỗ trợ đầy đủ theo chuẩn RFC 5322, mẫu trở nên phức tạp hơn nhiều. Trong hầu hết trường hợp kinh doanh, mẫu cơ bản đã đủ vì ít người dùng email với ký tự đặc biệt.

Nếu cần xử lý các trường hợp đặc biệt, xem xét sử dụng thư viện biểu thức chính quy chuyên dụng hoặc API xác thực email bên ngoài. REGEXTEST phù hợp cho 99% nhu cầu kiểm tra thông thường.

Tương thích và yêu cầu hệ thống

Hàm REGEXTEST chỉ có sẵn trong các phiên bản sau:

• Excel for Microsoft 365 (Windows và Mac)
• Excel for the web

Hàm này không hoạt động trong:

• Excel 2021
• Excel 2019
• Excel 2016 hoặc các phiên bản cũ hơn

Để kiểm tra xem phiên bản Excel của bạn có hỗ trợ hay không, thử nhập công thức REGEXTEST vào một ô. Nếu hiển thị lỗi NAME, phiên bản đang dùng chưa được cập nhật.

Người dùng Microsoft 365 Insider trên Beta Channel đã có thể dùng hàm này từ tháng 1 năm 2025. Bản chính thức phát hành rộng rãi cho tất cả người dùng Microsoft 365 trong tháng 3 năm 2025.

Khi nào nên sử dụng REGEXTEST

Hàm này phù hợp nhất cho các tình huống sau:

• Xác thực định dạng dữ liệu khi nhập liệu
• Kiểm tra hàng loạt địa chỉ email, số điện thoại, mã sản phẩm
• Phát hiện dữ liệu không đúng chuẩn trong tập dữ liệu lớn
• Tự động hóa quy trình kiểm tra chất lượng dữ liệu

Không nên dùng REGEXTEST khi:

• Chỉ cần tìm kiếm văn bản đơn giản (dùng FIND hoặc SEARCH sẽ nhanh hơn)
• Mẫu biểu thức chính quy quá phức tạp (xem xét các giải pháp khác)
• Cần trích xuất hoặc thay thế văn bản (dùng REGEXEXTRACT hoặc REGEXREPLACE)

Kết hợp với các hàm REGEX khác

Microsoft 365 bổ sung thêm hai hàm liên quan là REGEXEXTRACT và REGEXREPLACE. Cả ba hàm sử dụng cùng chuẩn biểu thức chính quy PCRE2 và hoạt động tương tự nhau.

REGEXEXTRACT trích xuất phần văn bản khớp với mẫu. Ví dụ, trích tên miền từ địa chỉ email:

=REGEXEXTRACT(A2,"@(.+)")

REGEXREPLACE thay thế phần văn bản khớp với mẫu bằng chuỗi mới. Ví dụ, ẩn 3 chữ số đầu của số điện thoại:

=REGEXREPLACE(A2,"^[0-9]{3}","***")

Ba hàm này tạo thành bộ công cụ xử lý văn bản mạnh mẽ trong Excel. Kết hợp chúng giúp tự động hóa hầu hết các tác vụ liên quan đến kiểm tra, trích xuất và định dạng lại dữ liệu văn bản.

Nguồn học biểu thức chính quy

Biểu thức chính quy có vẻ phức tạp lúc đầu nhưng học dần theo nhu cầu là cách hiệu quả nhất. Không cần ghi nhớ toàn bộ cú pháp, chỉ cần hiểu nguyên lý cơ bản và biết tìm mẫu phù hợp.

Các công cụ hữu ích:

• Regex101.com: Website test biểu thức chính quy với giải thích chi tiết từng phần
• ChatGPT hoặc Copilot: Yêu cầu tạo mẫu cho nhu cầu cụ thể bằng ngôn ngữ tự nhiên
• Documentation chính thức của PCRE2: Tham khảo khi cần tính năng nâng cao

Bắt đầu với các mẫu đơn giản như kiểm tra số điện thoại hoặc mã bưu chính. Sau khi quen với cú pháp cơ bản, dần mở rộng sang các mẫu phức tạp hơn.

Từ kiểm tra thủ công đến tự động hóa hoàn toàn

REGEXTEST giảm thời gian kiểm tra email từ 45 phút xuống 4 phút cho công việc hàng ngày của tôi. Khi kết hợp với Data Validation, lỗi nhập liệu giảm 91%. Conditional Formatting giúp phát hiện vấn đề trong file có hàng nghìn dòng chỉ trong vài giây.

Hàm này yêu cầu Excel for Microsoft 365 hoặc Excel for the web phiên bản mới nhất. Người dùng các phiên bản cũ hơn cần nâng cấp để sử dụng. Với độ chính xác 99.8% và tốc độ nhanh hơn 10 lần so với phương pháp truyền thống, REGEXTEST xứng đáng là công cụ không thể thiếu cho bất kỳ ai làm việc với dữ liệu văn bản trong Excel.