Tách mã số thuế, số điện thoại khỏi chuỗi bằng REGEXEXTRACT

Danh sách khách hàng do nhân viên kinh doanh nhập tay thường dồn mọi thứ vào một ô, kiểu "Cty TNHH An Phát - MST 0101234567 - 0912 345 678". Đến lúc xuất hoá đơn hay gọi chăm sóc khách, kế toán phải tách tay từng dòng. MORA Sheet có ba hàm REGEX như Excel 365, trong đó REGEXEXTRACT tách chuỗi theo mẫu chỉ bằng một công thức. Số liệu trong bài là ví dụ.

Vì sao Văn bản thành cột không đủ

Lệnh Văn bản thành cột ở thẻ Dữ liệu hợp khi các phần cách nhau đều bằng một dấu. Danh sách nhập tay thì không: dòng này cách bằng gạch ngang, dòng kia bằng dấu phẩy, có dòng ghi "mst:", có dòng viết số điện thoại liền một mạch. Hàm REGEX tìm theo hình dạng của dữ liệu, như mười chữ số hay số bắt đầu bằng 09, nên các dòng không cần giống nhau.

Ba hàm REGEX và cú pháp

REGEXEXTRACT(văn bản; mẫu; [kiểu trả về]; [phân biệt hoa thường]) lấy phần khớp mẫu. Kiểu trả về 0, mặc định, lấy chỗ khớp đầu tiên; kiểu 2 chỉ lấy phần nằm trong ngoặc tròn của mẫu. Tham số cuối đặt 1 nếu không muốn phân biệt chữ hoa, chữ thường. Không tìm thấy, hàm trả #N/A.

REGEXTEST(văn bản; mẫu) trả TRUE hoặc FALSE, dùng để kiểm. REGEXREPLACE(văn bản; mẫu; thay bằng) thay mọi chỗ khớp bằng chữ khác. Công thức trong MORA Sheet ngăn đối số bằng dấu chấm phẩy; gõ =reg là danh sách gợi ý hàm hiện cú pháp ngay dưới ô.

Tách mã số thuế

1. Giả sử chuỗi gốc nằm ở cột B, bắt đầu từ B2. Bấm vào C2.
2. Gõ =REGEXEXTRACT(B2;"MST\D*(\d{10}(?:-\d{3}){0,1})";2;1) rồi bấm Enter.
3. Kéo công thức ở C2 xuống hết danh sách.

Đọc mẫu từ trái sang. "MST\D*" tìm chữ MST và mọi ký tự không phải số ngay sau nó, như dấu cách, dấu hai chấm, gạch ngang. Phần trong ngoặc là mười chữ số, có thể kèm đuôi "-" và ba số của đơn vị phụ thuộc. Kiểu trả về 2 lấy riêng phần trong ngoặc, tức là mã số thuế; tham số cuối bằng 1 cho "mst", "Mst" cũng khớp.

Tách số di động

Số di động hay viết có dấu cách, dấu chấm, và dễ lẫn với mã số thuế vì cũng bắt đầu bằng 0. Cách gọn là làm hai bước với cột phụ.

1. Ở D2, gõ =REGEXREPLACE(B2;"MST\D*[\d-]+";"";0;1) để bỏ phần mã số thuế khỏi chuỗi.
2. Ở E2, gõ =REGEXEXTRACT(D2;"0[35789]\d([ .]{0,1}\d){7}") để lấy số di động từ phần còn lại. Mẫu này nhận số 10 chữ số bắt đầu bằng 03, 05, 07, 08, 09, kể cả khi viết cách nhau.
3. Ở F2, gõ =REGEXREPLACE(E2;"[ .]";"") để bỏ dấu cách, dấu chấm cho gọn.
4. Kéo ba công thức xuống, rồi ẩn hai cột phụ D, E.

Quen tay rồi thì gộp ba bước vào một ô, mỗi hàm bọc lấy hàm trước. Công thức gộp là =REGEXREPLACE(REGEXEXTRACT(REGEXREPLACE(B2;"MST\D*[\d-]+";"";0;1);"0[35789]\d([ .]{0,1}\d){7}");"[ .]";""), đặt ở một cột trống và không cần cột phụ.

Dòng không khớp và bước kiểm lại

Dòng không có mã số thuế hay số di động sẽ báo #N/A. Muốn để trống thay vì báo lỗi, bọc công thức bằng IFNA, ví dụ =IFNA(REGEXEXTRACT(B2;"MST\D*(\d{10}(?:-\d{3}){0,1})";2;1);"").

Tách xong nên kiểm một lượt. Ở cột G, gõ =REGEXTEST(C2;"^\d{10}(-\d{3}){0,1}$") để biết mã số thuế có đúng mười chữ số, hoặc mười chữ số kèm đuôi ba số hay không. Dòng ra FALSE là chỗ nhập sai, tô màu chúng bằng định dạng có điều kiện để dò cho nhanh.

Tệp lưu .xlsx gửi cho người dùng Excel 365 vẫn giữ công thức, vì Excel 365 có cùng ba hàm này. Danh sách gốc ở cột B vẫn nguyên, nên khi kinh doanh nhập thêm khách, chỉ cần kéo công thức xuống tiếp.