REGEXEXTRACT, REGEXMATCH và REGEXREPLACE là ba hàm của Google Sheets làm việc với biểu thức chính quy: hàm thứ nhất trích phần chữ khớp mẫu, hàm thứ hai trả về đúng hoặc sai khi chuỗi có khớp mẫu hay không, hàm thứ ba thay phần khớp bằng chữ khác. Chúng giải quyết việc mà LEFT, MID, FIND làm rất vất vả: lấy mã đơn, số lượng, mã hàng nằm lẫn trong một câu ghi chú viết tự do.
Biểu thức chính quy có cú pháp khó nhớ, và đây đúng là phần trợ lý AI giúp được nhiều nhất. Nhưng Google Sheets dùng một bộ quy tắc biểu thức chính quy riêng, thiếu vài tính năng mà AI hay mặc định có, và cư xử khá đặc biệt với chữ tiếng Việt có dấu. Bài này đi qua cách ba hàm hoạt động, các mẫu hay dùng, những chỗ AI hay viết sai và cách kiểm trước khi áp cho cả cột.
Ba hàm, ba việc khác nhau
Cả ba hàm nhận vào một chuỗi chữ và một biểu thức chính quy đặt trong ngoặc kép. Khác nhau ở thứ chúng trả về.
| Hàm | Trả về | Dùng khi | Ví dụ |
|---|---|---|---|
| REGEXEXTRACT | Phần chữ đầu tiên khớp mẫu, hoặc các nhóm trong ngoặc tròn | Cần lấy mã, số, từ khoá ra cột riêng | Lấy mã đơn dạng hai chữ hoa và sáu chữ số từ ghi chú |
| REGEXMATCH | Đúng hoặc sai | Cần đánh dấu, lọc, kiểm dòng hợp lệ | Đánh dấu ghi chú có chữ đổi trả |
| REGEXREPLACE | Chuỗi sau khi thay mọi phần khớp | Cần dọn ký tự thừa, đổi định dạng | Bỏ mọi ký tự không phải chữ số trong cột số tiền dạng chữ |
Khi mẫu có nhiều nhóm trong ngoặc tròn, REGEXEXTRACT trả về mỗi nhóm một cột liền kề. Chẳng hạn mẫu tách số lượng và đơn vị từ cụm như 3 hộp sẽ cho hai cột: số 3 và chữ hộp. Đây là cách tách một ghi chú thành nhiều cột chỉ bằng một công thức.
Ba hàm đều chỉ nhận chữ. Đưa vào một ô chứa số, REGEXEXTRACT báo lỗi cần giá trị dạng chữ. Cách xử lý là chuyển sang chữ trước bằng TO_TEXT, hoặc nối với một chuỗi rỗng. Ngược lại, kết quả trích ra luôn là chữ; muốn cộng được thì bọc thêm VALUE.
Sáu mẫu hay dùng với ghi chú đơn hàng và danh sách khách
Lấy mã đơn. Ghi chú kiểu ví dụ giả định khách đổi màu đơn DH204517 giao trước 5 giờ. Mẫu [A-Z]{2}\d{6} lấy đúng hai chữ hoa theo sau là sáu chữ số. Nếu mã có độ dài thay đổi, dùng DH\d+ để lấy chữ DH và mọi chữ số liền sau.
Lấy số lượng và đơn vị. Mẫu (\d+)\s*(cái|hộp|chiếc|bộ) trả về hai cột: số và đơn vị. Danh sách đơn vị phải liệt kê đủ các cách khách hay viết; đây là chỗ nên hỏi AI liệt kê thêm từ dữ liệu mẫu của bạn.
Đánh dấu dòng cần xử lý. Kết hợp với FILTER: lọc mọi ghi chú có chữ đổi hoặc trả, không phân biệt hoa thường, bằng mẫu (?i)đổi|trả trong REGEXMATCH. Cờ (?i) ở đầu mẫu bật chế độ không phân biệt hoa thường.
Giữ lại chữ số. REGEXREPLACE với mẫu [^0-9] và chuỗi thay rỗng biến một ô số tiền gõ lẫn dấu chấm, chữ đ, khoảng trắng thành chuỗi chỉ có chữ số; bọc VALUE để thành số. Cẩn thận với số có phần thập phân: cách này xoá luôn dấu thập phân.
Dọn khoảng trắng thừa giữa các từ. REGEXREPLACE với mẫu \s+ và chuỗi thay là một dấu cách gom nhiều dấu cách liên tiếp thành một. TRIM làm được phần đầu cuối, còn mẫu này xử lý cả khoảng trắng giữa chữ và các ký tự cách lạ dán từ trang web.
Đảo vị trí hai phần. REGEXREPLACE hỗ trợ tham chiếu nhóm bằng $1, $2 trong chuỗi thay. Ví dụ đổi mã dạng 2025-CN3 thành CN3-2025 bằng mẫu (\d{4})-(\w+) và chuỗi thay $2-$1. Kiểm kỹ trên vài dòng vì một nhóm khớp sai là cả cột đảo sai.
Google Sheets dùng RE2: những thứ AI hay viết mà không chạy
Biểu thức chính quy có nhiều phương ngữ. Google Sheets dùng bộ quy tắc RE2, một thư viện do Google phát triển, ưu tiên tốc độ và an toàn. Đổi lại, RE2 không hỗ trợ vài tính năng mà các ngôn ngữ lập trình phổ biến có. Trợ lý AI học từ rất nhiều mã Python, JavaScript nên hay viết mẫu dùng những tính năng đó, và Google Sheets sẽ báo biểu thức không hợp lệ.
Hai tính năng hay gặp nhất là nhìn trước, nhìn sau, tức các cụm bắt đầu bằng ngoặc tròn, dấu hỏi rồi dấu bằng hoặc dấu chấm than, và tham chiếu ngược đến nhóm đã khớp ngay trong mẫu, dạng gạch chéo ngược kèm số. Cả hai không có trong RE2. Nếu AI đưa ra mẫu có các cụm này, yêu cầu nó viết lại cho RE2, thường bằng cách dùng nhóm bắt và lấy đúng nhóm cần trong REGEXEXTRACT.
Cách phòng ngay từ đầu là ghi trong câu lệnh: biểu thức chính quy dùng trong hàm REGEXEXTRACT của Google Sheets, cú pháp RE2, không dùng nhìn trước nhìn sau, không dùng tham chiếu ngược. Thêm một câu nữa giúp ích: yêu cầu AI giải thích từng phần của mẫu bằng lời. Một mẫu bạn đọc hiểu được thì bạn sửa được khi dữ liệu đổi; một mẫu dài bạn không hiểu là món nợ để lại cho người sau.
Chữ tiếng Việt có dấu: những bẫy riêng
Phần lớn ví dụ biểu thức chính quy trên mạng viết cho tiếng Anh. Với dữ liệu tiếng Việt, có vài điều cần biết trước khi tin một mẫu:
- Trong RE2, ký hiệu \w chỉ khớp chữ cái Latin không dấu, chữ số và dấu gạch dưới. Chữ có dấu như ă, ơ, đ không được tính. Mẫu lấy một từ bằng \w+ sẽ cắt đôi chữ Đường thành phần trước và sau chữ có dấu. Muốn khớp mọi chữ cái kể cả có dấu, dùng \p{L}.
- Một chữ có dấu có thể lưu theo hai cách mã hoá: dạng dựng sẵn một ký tự, hoặc dạng tổ hợp chữ gốc cộng dấu. Nhìn giống hệt nhau nhưng máy coi là khác. Dữ liệu dán từ nhiều nguồn, nhất là từ tệp xuất của phần mềm cũ hay từ máy Mac, hay lẫn hai dạng. Mẫu có chữ đổi gõ từ bàn phím có thể không khớp chữ đổi trong dữ liệu.
- Cờ không phân biệt hoa thường (?i) áp dụng cho chữ có dấu, nhưng chỉ khi hai bên cùng dạng mã hoá.
- Khoảng trắng dán từ trang web hay tin nhắn có thể là ký tự cách không ngắt dòng, không khớp dấu cách thường trong mẫu; \s trong RE2 cũng có thể không bắt nó. Khi mẫu có vẻ đúng mà không khớp, nghi ngờ khoảng trắng lạ trước tiên.
- Mẫu bỏ dấu tiếng Việt bằng biểu thức chính quy rất dài và dễ sót; nếu chỉ cần so sánh không dấu, cân nhắc làm một bảng quy đổi hoặc dùng công cụ làm sạch dữ liệu thay vì một công thức khổng lồ.
Khi kết quả không như ý với dữ liệu tiếng Việt, cách thử nhanh là gõ lại từ khoá ngay trong ô dữ liệu bằng bàn phím, xem công thức có khớp không. Khớp sau khi gõ lại nghĩa là vấn đề nằm ở mã hoá ký tự chứ không phải ở mẫu.
Khi nào không cần đến biểu thức chính quy
Biểu thức chính quy mạnh nhưng khó đọc với người cùng phòng. Nhiều việc tách chữ có cách đơn giản hơn, dễ bàn giao hơn. Trước khi nhờ AI viết một mẫu dài, xem dữ liệu của bạn có rơi vào các trường hợp dưới đây không.
| Tình huống | Cách đơn giản hơn | Vì sao nên chọn |
|---|---|---|
| Chuỗi có ký tự phân cách cố định, như mã chi nhánh, gạch nối, số thứ tự | Hàm SPLIT với ký tự phân cách, hoặc tính năng tách văn bản thành cột trong menu Dữ liệu | Ai cũng đọc hiểu, không có bẫy cú pháp |
| Phần cần lấy luôn nằm ở vị trí cố định, như ba ký tự đầu của mã hàng | LEFT, RIGHT, MID | Nhanh, rõ, không phụ thuộc phương ngữ biểu thức |
| Cần kiểm chuỗi có chứa một từ cố định không | SEARCH hoặc COUNTIF với ký tự đại diện dấu sao | Đủ dùng, không phân biệt hoa thường sẵn với SEARCH |
| Cần thay một chuỗi cố định bằng chuỗi khác | SUBSTITUTE, hoặc công cụ Tìm và thay thế | Không lo ký tự đặc biệt như dấu chấm, dấu ngoặc bị hiểu thành lệnh |
| Ghi chú tự do, nhiều kiểu viết, cần lấy mã có quy luật | REGEXEXTRACT | Đây là chỗ biểu thức chính quy thật sự đáng dùng |
Một bẫy nhỏ khi chuyển từ SUBSTITUTE sang REGEXREPLACE: trong biểu thức chính quy, dấu chấm nghĩa là một ký tự bất kỳ, dấu ngoặc, dấu cộng, dấu hỏi đều có nghĩa đặc biệt. Muốn thay đúng dấu chấm thì phải viết gạch chéo ngược trước nó. Nhiều người dùng REGEXREPLACE để bỏ dấu chấm trong số tiền và vô tình xoá sạch cả ô vì quên chi tiết này.
Nguyên tắc chọn: dùng cách đơn giản nhất làm được việc, và chỉ dùng biểu thức chính quy khi dữ liệu thật sự không theo vị trí hay ký tự phân cách cố định. Bảng tính bạn làm hôm nay sẽ có người khác mở ra sửa vào năm sau.
Quy trình nhờ AI viết biểu thức chính quy và kiểm trước khi áp cả cột
Biểu thức chính quy sai thường không báo lỗi mà lặng lẽ trích nhầm hoặc bỏ sót. Vì vậy phần kiểm quan trọng hơn phần viết.
- Gom một tập mẫu khoảng hai mươi dòng ghi chú thật, rồi thay tên khách, số điện thoại, địa chỉ bằng dữ liệu giả định. Giữ nguyên cách viết lộn xộn của ghi chú, vì đó chính là thứ mẫu cần xử lý.
- Ghi rõ cho AI: Google Sheets, hàm nào, cú pháp RE2, cần trích phần nào, các biến thể có thể gặp, và trường hợp không khớp thì muốn ra gì.
- Xin AI kèm ba thứ: mẫu, giải thích từng phần, và danh sách các ví dụ mà mẫu sẽ không bắt được.
- Đặt công thức cạnh tập mẫu trên một trang tính thử, bọc IFERROR để dòng không khớp hiện chữ Không khớp thay vì lỗi #N/A.
- Soi từng dòng: dòng nào trích đúng, dòng nào trích nhầm, dòng nào bỏ sót. Đưa các dòng sai lại cho AI kèm kết quả mong muốn.
- Khi tập mẫu sạch, áp cho cả cột và đếm số dòng Không khớp bằng COUNTIF. Con số này cho biết còn bao nhiêu dòng phải xử lý tay.
- Giữ nguyên cột ghi chú gốc; kết quả trích để ở cột mới. Không bao giờ dùng REGEXREPLACE đè thẳng lên dữ liệu gốc mà không có bản sao.
Với ghi chú quá tự do, không mẫu nào bắt được mọi trường hợp. Khi số dòng không khớp vẫn lớn, có lẽ vấn đề nằm ở khâu nhập: một biểu mẫu có ô riêng cho mã đơn và số lượng sẽ tiết kiệm hơn mọi biểu thức chính quy. Việc trích thông tin từ tin nhắn, email đặt hàng có bàn riêng ở danh mục nhập liệu và số hoá.
Câu hỏi thường gặp
REGEXEXTRACT trả về lỗi #N/A nghĩa là gì?
Nghĩa là chuỗi trong ô không có phần nào khớp mẫu. Đây không phải lỗi công thức mà là tín hiệu dữ liệu. Bọc IFERROR với một chữ rõ nghĩa như Không khớp, rồi đếm số dòng đó để biết mẫu đã bao phủ được bao nhiêu phần dữ liệu.
Làm sao lấy tất cả các lần khớp chứ không chỉ lần đầu?
REGEXEXTRACT chỉ trả về lần khớp đầu tiên hoặc các nhóm của lần khớp đó. Muốn lấy mọi lần khớp, một cách thường dùng là REGEXREPLACE để chèn ký tự phân cách quanh các phần khớp rồi dùng SPLIT, hoặc nhờ AI viết công thức kết hợp phù hợp với dữ liệu của bạn và thử kỹ.
Excel có các hàm REGEX giống Google Sheets không?
Excel bản mới đã bổ sung nhóm hàm biểu thức chính quy với tên và tham số khác, và mức độ có sẵn tuỳ phiên bản. Khi chuyển công thức giữa hai bên, không nên chép nguyên; nhờ AI chuyển đổi và kiểm phiên bản Excel đang dùng.
Vì sao mẫu chạy đúng trên trang thử biểu thức chính quy nhưng sai trong Google Sheets?
Các trang thử thường mặc định phương ngữ của JavaScript hoặc Python, có những tính năng RE2 không có, và cách hiểu \w hay khoảng trắng cũng khác. Hãy chọn chế độ RE2 hoặc Golang trên trang thử nếu có, và luôn kiểm lại ngay trong Google Sheets.
Có nên dùng REGEXMATCH để kiểm số điện thoại hợp lệ không?
Có thể dùng để bắt lỗi rõ ràng như thiếu chữ số hoặc lẫn chữ cái, nhưng một mẫu đúng định dạng không chứng minh số đó có thật. Quy tắc đầu số có thể thay đổi theo thời gian, nên chỉ kiểm độ dài và ký tự, phần còn lại để người phụ trách xác minh.
Dán danh sách khách hàng cho AI để nó tự tìm mẫu có ổn không?
Không nên dán danh sách thật. Hãy thay tên, số điện thoại, địa chỉ bằng dữ liệu giả định nhưng giữ nguyên kiểu viết, độ dài và các lỗi thường gặp. AI cần hình dạng dữ liệu để đoán mẫu, không cần danh tính khách hàng.