Làm sạch danh sách khách hàng bằng REGEXTEST và REGEXREPLACE

Danh sách khách gom từ phiếu đăng ký, danh thiếp và tệp của ba nhân viên kinh doanh hiếm khi sạch. Cột email có ô thiếu @, có ô thừa dấu cách. Cột điện thoại có số viết 0912.345.678, số ghi +84 912 345 678, số khác mất số 0 đầu vì từng bị gõ như một con số. Trước khi gửi thư hàng loạt hay nhập sang phần mềm khác, bạn cần lọc email sai và chuẩn hoá số điện thoại. Bài này làm cả hai việc bằng REGEXTEST và REGEXREPLACE trong MORA Sheet; số liệu trong bài là ví dụ.

Chuẩn bị bảng và các cột phụ

Giả sử họ tên ở cột A, email ở cột B, số điện thoại ở cột C, dữ liệu bắt đầu từ dòng 2. Thêm bốn cột phụ, gồm D “Email sạch”, E “Email hợp lệ”, F “Điện thoại chuẩn” và G “Điện thoại hợp lệ”. Cột gốc giữ nguyên để đối chiếu.

Hai hàm có cú pháp như Excel 365. REGEXTEST(văn bản; mẫu) trả TRUE khi văn bản khớp mẫu, FALSE khi không. REGEXREPLACE(văn bản; mẫu; thay bằng) thay mọi chỗ khớp bằng chữ khác; trong chữ thay, $1 là phần nằm trong cặp ngoặc tròn thứ nhất của mẫu. Gõ =reg vào ô là danh sách gợi ý hiện tên hàm và cú pháp ngay dưới ô.

Kiểm email bằng REGEXTEST

1. Ở D2, gõ =LOWER(REGEXREPLACE(B2;"\s";"")) để bỏ mọi dấu cách và đổi về chữ thường.
2. Ở E2, gõ =REGEXTEST(D2;"^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$") rồi nhấn Enter.
3. Chọn D2:E2, kéo xuống hết danh sách.

Mẫu ở bước 2 đọc từ trái sang. Phần trước @ gồm chữ không dấu, số và vài dấu như chấm, gạch dưới; sau @ là tên miền có ít nhất một dấu chấm, đuôi từ hai chữ cái trở lên. Ô ra FALSE là email thiếu @, thiếu đuôi tên miền, hoặc lẫn dấu phẩy, chữ có dấu. Mẫu chỉ bắt lỗi gõ, không biết địa chỉ đó có thật hay không.

Chuẩn hoá số điện thoại bằng REGEXREPLACE

Ba ô gốc “0912.345.678”, “+84 912 345 678” và 912345678 đều phải thành 0912345678. Việc này cần ba lần thay, làm lần lượt để dễ kiểm từng bước.

1. Bỏ mọi ký tự không phải chữ số hay dấu +. Ở F2, gõ =REGEXREPLACE(C2;"[^0-9+]";""). Ba ô ví dụ lúc này ra 0912345678, +84912345678 và 912345678.
2. Đổi đầu số +84 hoặc 84 thành 0 bằng cách bọc thêm một lớp, sửa F2 thành =REGEXREPLACE(REGEXREPLACE(C2;"[^0-9+]";"");"^\+{0,1}84(\d{9})$";"0$1").
3. Thêm số 0 cho số chỉ còn chín chữ số bằng lớp thứ ba, sửa F2 thành =REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(C2;"[^0-9+]";"");"^\+{0,1}84(\d{9})$";"0$1");"^([35789]\d{8})$";"0$1").
4. Ở G2, gõ =REGEXTEST(F2;"^0[35789]\d{8}$") để kiểm kết quả, rồi kéo F2:G2 xuống hết danh sách.

Ở bước 2, “(\d{9})” giữ lại chín chữ số sau đầu 84, “0$1” dán chúng trở lại sau số 0. Kết quả ở cột F là chữ, không phải số, nên số 0 đầu không bị mất. Mẫu ở G2 chỉ nhận số di động mười chữ số; danh sách có số máy bàn thì để riêng một cột, đừng ép chung mẫu.

Tô đỏ những ô còn sai

1. Chọn vùng email gốc từ B2 tới dòng cuối có dữ liệu, ví dụ B2:B500.
2. Trên thẻ Trang chủ, bấm Có điều kiện › Điều kiện › Quy tắc khác....
3. Ở điều kiện 1, đổi Giá trị ô thành Công thức là, gõ NOT($E2) vào ô bên cạnh.
4. Ở Áp dụng kiểu, chọn Xấu rồi bấm OK.

Làm tương tự cho vùng điện thoại gốc C2:C500 với công thức NOT($G2). Đừng chọn thừa xuống các dòng trống bên dưới, vì ở đó cột phụ chưa có công thức nên ô cũng bị tô đỏ. Ô tô đỏ là dòng cần gọi lại khách hoặc tra lại danh thiếp. Sửa xong ở cột gốc, các cột phụ tự tính lại và màu đỏ mất đi, nên nhìn bảng là biết còn bao nhiêu dòng phải xử lý.

Đưa kết quả sạch sang bảng mới

Khi không còn ô đỏ, chọn cột D và F, nhấn Ctrl+C. Sang trang tính mới, chuột phải vào ô đích, trỏ vào Dán đặc biệt rồi chọn dòng cuối có dấu ba chấm để mở hộp thoại. Trong hộp thoại, bấm Chỉ giá trị để dán kết quả chứ không dán công thức. Bảng này dùng để trộn thư hàng loạt hay nhập vào phần mềm khác.

Tệp lưu .xlsx mở trên Excel 365 vẫn giữ công thức, vì Excel 365 có cùng các hàm này. Muốn tách mã số thuế, số điện thoại ra khỏi một chuỗi dài, xem bài tách mã số thuế bằng REGEXEXTRACT.