Hàm REGEX Trong Excel: REGEXEXTRACT, REGEXREPLACE, REGEXTEST

Ba hàm REGEXEXTRACT, REGEXREPLACE và REGEXTEST cho phép dùng biểu thức chính quy ngay trong công thức Excel. Chúng giải quyết những việc mà LEFT, MID, FIND làm rất vất vả: lấy số điện thoại lẫn trong một đoạn văn, kiểm tra định dạng email, dọn ký tự lạ theo quy luật.

Phiên bản nào có nhóm hàm này?

Đây là nhóm hàm mới nhất trong Excel và thuộc bản thuê bao Microsoft 365. Bản mua một lần, kể cả bản mới, thường không có.

Kiểm tra trên máy bạn: gõ =REGEXTEST("abc", "[a-z]+") vào ô trống. Ra TRUE nghĩa là có, báo #NAME? nghĩa là không.

Nếu công việc của bạn phụ thuộc vào nhóm hàm này, hãy nhờ kiểm tra trên đúng phiên bản trước khi mua Office.

Ba hàm và công dụng

REGEXTEST(chuỗi, mẫu) trả về TRUE hoặc FALSE, dùng để kiểm tra định dạng.

REGEXEXTRACT(chuỗi, mẫu, [chế_độ]) lấy ra phần khớp với mẫu.

REGEXREPLACE(chuỗi, mẫu, thay_thế) thay thế phần khớp bằng nội dung khác.

Các mẫu hay dùng

• \d một chữ số, \d+ một hoặc nhiều chữ số liên tiếp.
• \w một ký tự chữ, số hoặc gạch dưới.
• \s một khoảng trắng.
• [a-z] một chữ cái thường bất kỳ.
• . một ký tự bất kỳ.
• ^ đầu chuỗi, $ cuối chuỗi.

Ví dụ thực tế

Lấy số điện thoại trong một đoạn văn:

=REGEXEXTRACT(A2, "0\d{9}")

Kiểm tra email có đúng định dạng không:

=REGEXTEST(A2, "^[\w.-]+@[\w.-]+\.\w+$")

Xóa mọi ký tự không phải chữ số, dùng để dọn mã số thuế hoặc số tài khoản:

=REGEXREPLACE(A2, "[^\d]", "")

Dọn nhiều khoảng trắng liên tiếp thành một:

=REGEXREPLACE(A2, "\s+", " ")

Cách làm thay thế trên bản vĩnh viễn

Dọn khoảng trắng thừa: hàm TRIM làm đúng việc này, có ở mọi phiên bản.

Lấy phần trước hoặc sau một ký tự: kết hợp LEFT, MID với FIND. Ví dụ lấy tên miền email: =MID(A2, FIND("@",A2)+1, LEN(A2)). Chi tiết ở bài tách chuỗi trong Excel.

Xóa ký tự không phải số: dùng Flash Fill. Gõ tay kết quả ở một hai dòng đầu rồi bấm Ctrl+E, Excel tự suy ra quy luật.

Làm sạch dữ liệu theo quy luật phức tạp: dùng Power Query. Công cụ này có sẵn từ bản 2016 và có hẳn các bước trích xuất, thay thế, tách theo ký tự, xem Power Query trong Excel. Với dữ liệu lớn cần làm sạch định kỳ, Power Query còn tiện hơn công thức vì thiết lập một lần rồi chỉ bấm Refresh.

Viết hàm riêng bằng VBA. VBA có thư viện biểu thức chính quy, tạo được hàm tự dùng tương đương ba hàm trên. File phải lưu dạng .xlsm, xem bật macro VBA bị chặn trong Office.

Các cách trên chạy được trên mọi phiên bản, kể cả key Office 2016. Nếu bạn cần các hàm mảng động để kết hợp xử lý dữ liệu thì bản thấp nhất có là key Office 2021.

Câu hỏi thường gặp

Biểu thức chính quy trong Excel có giống ngôn ngữ lập trình không?

Cú pháp cơ bản giống nhau nên kiến thức chuyển đổi được, nhưng một số cú pháp nâng cao có thể không hỗ trợ.

REGEXEXTRACT lấy được nhiều kết quả cùng lúc không?

Được, qua tham số chế độ. Kết quả tràn ra nhiều ô nên cần chỗ trống, xem mảng động và lỗi #SPILL.

Mẫu của tôi đúng mà vẫn không khớp?

Kiểm tra dấu gạch chéo ngược có bị Excel hiểu nhầm không, và thử bỏ bớt các ký tự neo đầu cuối để thu hẹp nguyên nhân.