Regular expressions (regex) có thể trông phức tạp đầu tiên, nhưng là công cụ mạnh khớp mẫu và thao tác văn bản. Trong Excel, chúng cho phép tìm kiếm, trích xuất, thay thế dữ liệu dựa trên mẫu phức tạp, làm tác vụ như làm sạch dữ liệu, validation, parsing hiệu quả hơn nhiều. Chúng tiết kiệm giờ làm sạch, validate, trích xuất thông tin khi nắm cơ bản. Để hỗ trợ regex, Excel cung cấp hàm như REGEXTEST, REGEXEXTRACT, REGEXREPLACE.
Đây là khóa học regex tốc hành cho power user Excel.
Regex Là Gì?
Regex (biểu thức chính quy) là ngôn ngữ mẫu cho văn bản. Thay vì tìm/khớp từ chính xác, regex cho bạn mô tả hình dạng văn bản. Trong Excel, regex như FIND + MID + SUBSTITUTE siêu sức mạnh, nơi một mẫu có thể validate, trích xuất, hoặc làm sạch văn bản.
Bạn khớp cấu trúc thường gặp Excel dùng regex: email, số điện thoại, ngày nhiều định dạng, hoặc ID tuân theo quy tắc.
Trong Excel hiện đại, dùng chủ yếu với:
- REGEXTEST (kiểm tra mẫu tồn tại)
- REGEXEXTRACT (kéo phần khớp)
- REGEXREPLACE (thay thế/làm sạch khớp)
Cơ Bản Regex: Xây Dựng Mẫu
Mẫu regex là chuỗi định nghĩa bạn tìm gì. Chúng kết hợp ký tự literal (ví dụ: "abc") với metacharacter đặc biệt. Bạn dùng bảng này tra cứu nhanh.
Metacharacter Và Cú Pháp Then Chốt:
| Khối Xây Dựng | Ý Nghĩa | Ví Dụ Mẫu Nhanh | Khớp |
|---|---|---|---|
| abc | Văn bản literal | TX- | TX- |
| . | Bất kỳ ký tự đơn | a.c | abc, a-c |
| \d | Chữ số (0–9) | \d{4} | 2026 |
| \D | Không phải chữ số | \D+ | TX- |
| \w | Ký tự từ (A–Z, a–z, 0–9, _) | \w+ | amina_01 |
| \W | Không ký tự từ | \W+ | – ( ) . |
| \s | Whitespace | \s+ | spaces/tabs |
| \S | Không whitespace | \S+ | [email protected] |
| [ABC] | Một trong các ký tự | [AEIOU] | A |
| [A-Z] | Khoảng (chữ hoa) | [A-Z]{2} | TX |
| [a-z] | Khoảng (chữ thường) | [a-z]+ | rahman |
| [0-9] | Khoảng chữ số | [0-9]+ | 1054 |
| [^0-9] | KHÔNG phải这些 (lớp phủ định) | [^0-9]+ | TX- |
| * | 0 hoặc nhiều | \d* | "", 7, 1054 |
| + | 1 hoặc nhiều | \d+ | 7, 1054 |
| ? | Tùy chọn (0 hoặc 1) | -? | – hoặc nothing |
| {n} | Đúng n lần | \d{2} | 12 |
| {n,} | Ít nhất n lần | \d{4,} | 202601 |
| {n,m} | Giữa n và m | \d{2,4} | 12, 2026 |
| ^ | Đầu văn bản | ^TX- | TX-1054 (ở đầu) |
| $ | Cuối văn bản | \.com$ | kết thúc .com |
| \b | Ranh giới từ | \bTX-\d{4}\b | TX-1054 làm token toàn |
| ( … ) | Nhóm bắt | (\d{4}) | bắt 1054 |
| (?: … ) | Nhóm không bắt | (?:TX|CA)-\d{4} | TX-1054 hoặc CA-1054 |
| \ | Thoát ký tự đặc biệt | company\.com | company.com literal |
| .* | Bất kỳ ký tự (tham lam) | \(.*\) | khớp từ ( đầu đến ) cuối |
| .*? | Bất kỳ ký tự (không tham lam) | \(.*?\) | khớp nhỏ nhất ( … ) |
| (?i) | Chế độ không phân biệt hoa thường (không hỗ trợ Excel regex pattern; dùng tham số case_sensitivity hàm thay vào) — Dùng REGEXTEST(text, pattern, 1) | — | — |
- Quantifiers (*, +, ?, {}) áp cho phần tử trước chúng.
- Flags: Hàm regex Excel không hỗ trợ inline flag như (?i) trong pattern; dùng tham số hàm cho case sensitivity (ví dụ: tham số thứ 3 REGEXTEST), hoặc dùng lớp ký tự (ví dụ: [Aa]).
- Tham Lam vs Lười: Mặc định quantifiers tham lam (khớp nhiều nhất). Thêm ? sau quantifier làm lười (ví dụ: .*?).
Hàm Regex Của Excel
Excel cung cấp ba hàm regex gốc. Các hàm nhận chuỗi văn bản và mẫu regex làm đầu vào và validate hoặc biến đổi văn bản dựa trên mẫu. Hãy biết từng hàm trước khi đi sâu khóa học.
Cú Pháp:
=REGEXTEST(text, pattern, [case_sensitivity])
Hàm này kiểm tra mẫu khớp bất kỳ phần văn bản cung cấp. Trả TRUE nếu mẫu khớp đâu đó trong văn bản; FALSE ngược lại.
- text: Văn bản hoặc tham chiếu ô muốn test.
- pattern: Mẫu regular expression (regex) mô tả văn bản muốn khớp.
- [case_sensitivity]: Xác định khớp có phân biệt hoa thường. Mặc định phân biệt hoa thường.
- 0: Phân biệt hoa thường
- 1: Không phân biệt hoa thường
Hàm này trả TRUE nếu A1 chứa ba chữ số liên tiếp.
Cú Pháp:
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
Hàm này trích xuất văn bản khớp.
- [return_mode]: Số chỉ định chuỗi gì muốn trích xuất. Mặc định return mode 0.
- 0: Trả chuỗi đầu khớp mẫu
- 1: Trả tất cả chuỗi khớp mẫu dạng mảng
- 2: Trả nhóm bắt từ khớp đầu dạng mảng
=REGEXEXTRACT(A1, "\d{3}-\d{3}-\d{4}") Trích xuất số điện thoại như "123-456-7890".
Cú Pháp:
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
Hàm này thay khớp bằng văn bản mới.
- replacement: Văn bản dùng thay mẫu. Bạn tham chiếu nhóm bắt với $1, $2, v.v.
- occurrence: Chỉ định instance mẫu muốn thay. Mặc định occurrence 0, thay tất cả. Số âm thay instance đó, tìm từ cuối.
=REGEXREPLACE(A1, "\d{3}", "***") Che mọi chuỗi ba chữ số.
Các hàm này spill nếu trả mảng (ví dụ: trích xuất nhiều).
Ví Dụ Thực Tế Cho Power User Excel
Giả sử dữ liệu lộn xộn. Áp dụng regex kịch bản thường gặp.
Ví Dụ 1: Validate Địa Chỉ Email
Bạn dùng REGEXTEST gắn cờ email công ty hợp lệ (khớp không phân biệt hoa thường thường an toàn hơn).
Dùng mẫu sau validate email.
- Mẫu: ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$
=REGEXTEST(A2, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$") Dùng Conditional Formatting Highlight Email Không Hợp Lệ:
- Đi tab Home >> chọn Conditional Formatting >> chọn New Rule
- Chọn Use a formula to determine which cells to format
- Chèn công thức sau
- Chọn Format >> chọn màu Fill >> click OK
- Địa chỉ email không hợp lệ highlight đỏ
Bạn cũng dùng REGEXTEST gắn cờ email công ty hợp lệ.
=REGEXTEST(B2,"@company\.com$",1)
- Tham số thứ ba (1) làm khớp không phân biệt hoa thường, nên Company.com vẫn pass
- \. thoát dấu chấm. . thuần nghĩa "bất kỳ ký tự"
Ví Dụ 2: Trích Xuất Dữ Liệu Từ Ghi Chú Lộn Xộn
Giả sử dữ liệu hỗn hợp, như mã đơn hoặc số điện thoại, trong ghi chú. Bạn trích số điện thoại hoặc mã đơn dùng REGEXEXTRACT.
Trích Mã Đơn:
=REGEXEXTRACT(D2,"TX-\d{4}") - Nếu vài hàng không có, bọc IFERROR:
=IFERROR(REGEXEXTRACT(D2,"TX-\d{4}"),"") 
Trích Số Điện Thoại:
- Dữ liệu: "Gọi tôi 123-456-7890 hoặc (987) 654-3210."
- Mẫu: (\d{3}[-. )]+){2}\d{4}
=REGEXEXTRACT(A2, "(\d{3}[-. )]+){2}\d{4}", 0, 1) Công thức này trích số điện thoại từ văn bản.
Ví Dụ 3: Chuẩn Hóa Số Điện Thoại Bằng Xóa Ký Tự Không Phải Chữ Số
- Tạo phiên bản chỉ chữ số trước.
=REGEXREPLACE(C2,"[^\d]","")
- Giờ định dạng (ví dụ: 10 chữ số cuối kiểu US khi có):
=LET(x,REGEXREPLACE(C2,"[^\d]",""), d,RIGHT(x,10), IF(LEN(d)=10,"("&LEFT(d,3)&") "&MID(d,4,3)&"-"&RIGHT(d,4),"")) Cách này xóa tất cả ký tự không phải chữ số và định dạng số điện thoại kết quả.
Ví Dụ 4: Parse Ngày Và Định Dạng Lại
Bạn parse ngày và định dạng lại dùng regex.
- Mẫu: (\d{1,2})/(\d{1,2})/(\d{4})
=REGEXREPLACE(A2, "(\d{1,2})/(\d{1,2})/(\d{4})", "$3-$2-$1") Công thức parse ngày sau đó định dạng lại dạng ISO-like hợp lệ. Bạn cũng dùng nhóm bắt () tham chiếu phần trong thay thế.
Ví Dụ 5: Làm Sạch Dữ Liệu Lộn Xộn (Ví Dụ: Xóa Khoảng Trắng Thừa)
Bạn dùng regex làm sạch dữ liệu lộn xộn, như xóa khoảng trắng thừa và chuẩn hóa định dạng.
Để xóa khoảng trắng thừa, dùng mẫu sau.
- Mẫu: \s+
=REGEXREPLACE(A2, "\s+", " ")
Công thức thay chuỗi whitespace bằng một khoảng trắng.
Ví Dụ 6: Xóa Bất Cứ Gì Trong Ngoặc Đơn (Kể Cả Ngoặc)
- Chọn ô chèn công thức sau:
=REGEXREPLACE(D2,"\s*\(.*?\)","")
- .*? là khớp không tham lam. Dừng ở ngoặc đóng đầu tiên thay vì cuối.
Công thức xóa tất cả dữ liệu trong ngoặc đơn, kể cả ngoặc.
Ví Dụ 7: Lọc Hàng Chứa Mã Đơn
=FILTER(A2:F16,REGEXTEST(D2:D16,"TX-\d{4}")) Công thức lọc dữ liệu dựa trên mẫu regex.
Kỹ Thuật Regex Nâng Cao Trong Excel
- Lookaheads/Lookbehinds: Khẳng định điều kiện không tiêu thụ văn bản.
- Positive lookahead: (?=…) ví dụ: \d+(?=%)$ cho số trước dấu %.
- Dùng REGEXEXTRACT trích giá: \d+\.\d{2}(?=\sUSD)
- Non-Capturing Groups: (?:…) nhóm không bắt.
- Alternation: Cho lựa chọn, ví dụ: (http|https)://\S+ cho URL.
- Xử Lý Mảng: Nếu REGEXEXTRACT trả nhiều nhóm, dùng INDEX hoặc spill range.
- Kết Hợp Hàm Khác: Lồng IF, FILTER, LAMBDA cho quy trình mạnh.
Mẹo Và Thực Hành Tốt Nhất
- Test Mẫu: Dùng công cụ online như regex101.com flavor "ECMAScript" (gần nhất regex Excel).
- Hiệu Suất: Regex chậm tập dữ liệu lớn; test mẫu trước.
- Lỗi: Nếu không khớp, REGEXEXTRACT trả #N/A; xử lý IFNA hoặc IFERROR.
- Hạn Chế: Regex Excel dựa subset, không hỗ trợ PCRE đầy đủ, tránh tính năng nâng cao như recursion.
- Học Thêm: Thực hành dataset thực. Regex dễ hơn với thực hành!
Tải Workbook Thực Hành
Tổng Kết
Power user Excel theo khóa học regex tốc hành để tăng tốc làm sạch, validate, tự động hóa dữ liệu. Khi thoải mái khối xây dựng cơ bản, khám phá lookaheads/lookbehinds khớp mẫu tinh vi hơn. Khóa học này khởi động: trước hiểu biểu thức, sau thử nghiệm pattern workbook trống. Regex biến đổi cách bạn xử lý văn bản Excel!
Nhận Bài Tập Excel Nâng Cao Miễn Phí Kèm Giải Pháp!