Chuẩn hoá số điện thoại là đưa mọi số trong một cột về cùng một dạng, thường là dãy 10 chữ số bắt đầu bằng số 0, không dấu cách, không dấu chấm, lưu ở dạng văn bản để Excel không tự cắt mất số 0 đầu. Nghe đơn giản, nhưng một cột điện thoại gom từ nhiều nguồn thường có đủ kiểu: số mất số 0, số có đầu quốc tế, số chia nhóm bằng dấu chấm, ô ghi hai số kèm chữ "hoặc", thậm chí ô ghi "zalo cùng số".
Cột điện thoại đã chuẩn hoá là khoá tốt nhất để tìm khách trùng, tra lịch sử mua hàng và gửi tin nhắn đúng người. Bài này đi qua từng kiểu lỗi, công thức xử lý trong Excel và Google Sheets, cách nhờ AI viết công thức cho đúng trường hợp của bạn, và cách đánh dấu những số không cứu được. Các số trong bài được viết dạng che, chỉ để minh hoạ cấu trúc.
Các kiểu lỗi hay gặp trong cột số điện thoại
Trước khi viết công thức, hãy lọc cột điện thoại và lướt qua để xem bảng của bạn dính những kiểu nào. Bảng dưới liệt kê các kiểu phổ biến ở danh sách khách hàng Việt Nam.
| Kiểu lỗi | Trông như thế nào | Nguyên nhân thường gặp |
|---|---|---|
| Mất số 0 đầu | Chỉ còn 9 chữ số, bắt đầu bằng 9, 8, 7, 3 hoặc 5 | Excel coi ô là số và bỏ số 0 vô nghĩa ở đầu |
| Đầu quốc tế | Bắt đầu bằng 84 hoặc dấu cộng rồi 84 | Số lấy từ ứng dụng nhắn tin, biểu mẫu trực tuyến hoặc khách nước ngoài nhập |
| Ký tự phân cách | Nhóm số cách nhau bằng dấu chấm, dấu cách hoặc gạch ngang | Người nhập chia nhóm cho dễ đọc |
| Hai số trong một ô | Hai dãy số nối bằng dấu gạch chéo, dấu phẩy hoặc chữ "hoặc" | Khách cho hai số, nhân viên gõ chung một ô |
| Lẫn chữ | Có thêm "zalo", "chồng", "nhà" phía trước hoặc sau | Dùng ô điện thoại làm chỗ ghi chú |
| Hiển thị dạng khoa học | Ô hiện số kèm chữ E và dấu cộng | Dán số dài vào ô định dạng General |
| Đầu số cũ 11 chữ số | Dãy 11 chữ số bắt đầu bằng 01 | Số lưu từ trước đợt chuyển đầu số di động năm 2018 |
Kiểu cuối cùng đáng chú ý ở các danh sách khách lâu năm. Năm 2018, các thuê bao di động 11 số đã được chuyển sang 10 số theo bảng quy đổi đầu số của từng nhà mạng. Nếu bảng của bạn còn số 11 chữ số, hãy tra bảng quy đổi chính thức rồi viết công thức thay đầu số theo bảng đó, đừng để AI tự nhớ và đoán. Số cố định có mã vùng cũng đã đổi từ năm 2017, cùng nguyên tắc tra bảng chính thức.
Giữ số 0 đầu ngay từ khi nhập
Cách rẻ nhất để không phải sửa là không để lỗi xảy ra. Với bảng đang dùng hằng ngày, làm các bước sau một lần:
- Chọn cả cột điện thoại, mở Format Cells, chọn định dạng Text trước khi nhập hoặc dán dữ liệu. Ô định dạng Text giữ nguyên số 0 đầu.
- Khi dán từ nơi khác vào, dùng Paste Special và chọn Values hoặc Text để không kéo theo định dạng số của nguồn.
- Khi mở tệp CSV xuất từ phần mềm bán hàng, đừng nhấp đúp mở thẳng. Dùng lệnh nhập dữ liệu từ tệp văn bản (Data, From Text/CSV) và đặt kiểu cột điện thoại là Text. Mở thẳng thì Excel tự đổi sang số và số 0 đầu biến mất ngay lúc mở.
- Đặt quy tắc kiểm tra dữ liệu (Data Validation) cho cột: độ dài đúng 10 ký tự. Người nhập gõ thiếu hay thừa sẽ bị báo ngay.
- Với Google Sheets, chọn cột rồi vào Format, Number, Plain text. Biểu mẫu Google Forms nên đặt câu hỏi điện thoại dạng trả lời ngắn có kiểm tra độ dài.
Một mẹo cũ là gõ dấu nháy đơn trước số để Excel hiểu là văn bản. Cách này dùng được khi nhập tay vài ô, nhưng với bảng nhiều người nhập thì định dạng Text cho cả cột đáng tin hơn.
Công thức đưa về một dạng chung
Với cột đã lộn xộn, cách làm gọn là thêm một cột mới "điện thoại chuẩn" và xử lý qua từng tầng, mỗi tầng một cột phụ để dễ kiểm. Tầng đầu bỏ ký tự phân cách: =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(B2)," ",""),".",""),"-",""),"+",""). Hàm TRIM chạy trước để bỏ khoảng trắng đầu cuối, các SUBSTITUTE lồng nhau bỏ dấu cách, dấu chấm, gạch ngang và dấu cộng.
Tầng hai xử lý đầu quốc tế: =IF(AND(LEN(C2)=11,LEFT(C2,2)="84"),"0"&MID(C2,3,20),C2). Nếu chuỗi dài 11 ký tự và bắt đầu bằng 84 thì thay 84 bằng 0, giữ phần còn lại. Điều kiện độ dài là để tránh một trường hợp hiếm: số trong nước bị mất số 0 đầu chỉ còn 9 ký tự, và có thể tình cờ bắt đầu bằng 8 rồi 4. Chuỗi đó là số mất số 0, không phải đầu quốc tế, nên không được cắt.
Tầng ba bù số 0 cho số bị cắt: =IF(LEN(D2)=9,"0"&D2,D2). Tầng cuối kiểm hợp lệ: =IF(AND(LEN(E2)=10,LEFT(E2,1)="0",ISNUMBER(--E2)),"hợp lệ","kiểm tra"). Hai dấu trừ trước E2 ép chuỗi thành số để ISNUMBER biết chuỗi chỉ toàn chữ số.
Nếu dùng Excel 365, các tầng có thể gộp lại bằng hàm LET cho gọn, nhưng khi mới làm, để từng tầng ở cột riêng giúp bạn thấy ngay dòng nào bị biến đổi sai. Google Sheets có REGEXREPLACE: =REGEXREPLACE(B2,"[^0-9]","") bỏ mọi ký tự không phải chữ số trong một lần. Công thức trong bài dùng dấu phẩy theo bản tiếng Anh; máy cài vùng Việt Nam thường cần dấu chấm phẩy.
Nhờ AI viết công thức cho đúng bảng của bạn
Mô tả kiểu lỗi, không dán số thật. Bảng của mỗi nơi có kiểu lỗi riêng. Thay vì dán cột điện thoại thật, hãy mô tả bằng lời và đưa vài ví dụ đã che, giữ nguyên cấu trúc. Ví dụ câu lệnh: "Cột B trong Excel chứa số điện thoại di động Việt Nam nhập lộn xộn. Các kiểu gặp: có dấu chấm chia nhóm, bắt đầu bằng 84 hoặc +84, mất số 0 đầu nên còn 9 chữ số, có ô chứa hai số ngăn bằng dấu gạch chéo, có ô kèm chữ zalo. Hãy viết công thức cho cột C trả về số 10 chữ số bắt đầu bằng 0, ô có hai số thì lấy số đầu, ô không xử lý được thì trả về chữ KIỂM TRA. Máy dùng Excel 365, dấu phân cách là chấm phẩy."
Nói rõ phiên bản và dấu phân cách. Câu trả lời của AI thay đổi nhiều tuỳ bạn dùng Excel bản nào. Hàm TEXTBEFORE, LET hay REGEXREPLACE chỉ có ở một số bản; nếu không nói, AI có thể viết hàm mà máy bạn không có, và bạn mất thời gian sửa lỗi tên hàm.
Yêu cầu giải thích từng phần. Thêm câu "giải thích công thức theo từng hàm lồng nhau". Phần giải thích giúp bạn phát hiện chỗ AI hiểu sai đề, chẳng hạn bỏ dấu cộng nhưng quên dấu cách, hoặc thay mọi số 84 trong chuỗi chứ không chỉ ở đầu.
Thử trên bộ ca khó do chính bạn dựng. Tạo mười dòng giả gồm đủ các kiểu lỗi, kể cả ca hiểm như số 9 chữ số bắt đầu bằng 84, ô trống, ô chỉ có chữ. Dán công thức vào và soát từng dòng. Chỉ khi cả mười dòng ra đúng mới kéo công thức cho cả cột.
Ô có hai số, số lẫn chữ và những số không cứu được
Không phải ô nào cũng chuẩn hoá được bằng công thức. Cách xử lý từng nhóm khó:
- Ô có hai số: tách ra hai cột "điện thoại chính" và "điện thoại phụ". Ở Excel 365 có thể dùng TEXTBEFORE và TEXTAFTER với ký tự ngăn cách; bản cũ dùng Text to Columns. Số nào là chính nên hỏi nhân viên phụ trách khách đó, đừng mặc định lấy số đầu.
- Ô lẫn chữ ghi chú: chuyển phần chữ sang cột ghi chú trước khi bỏ ký tự không phải số. Chữ "số của chồng" là thông tin có ích cho người gọi điện, xoá mất là đáng tiếc.
- Ô hiển thị dạng khoa học: nếu ô gốc đã bị Excel làm tròn thì các chữ số cuối đã mất thật, không khôi phục được bằng định dạng. Phải lấy lại từ nguồn gốc như phần mềm bán hàng hoặc sổ ghi tay.
- Số quá ngắn hoặc quá dài: đánh dấu "kiểm tra" và để nguyên. Đừng tự thêm hay bớt chữ số cho đủ mười; một số sai nhìn như số đúng còn nguy hiểm hơn ô trống vì tin nhắn sẽ đến tay người lạ.
- Số trùng giữa nhiều khách: thường là số của cửa hàng hoặc tổng đài bị nhập nhầm. Lọc các số xuất hiện quá nhiều lần để kiểm riêng.
Danh sách các dòng bị đánh dấu "kiểm tra" nên giao cho người bán hàng xác minh dần khi khách quay lại, thay vì cố xử lý hết trong một buổi.
Kiểm kết quả và giữ cột gốc
Sau khi kéo công thức cho cả cột, đếm số dòng "hợp lệ" và "kiểm tra" bằng COUNTIF. Tỷ lệ "kiểm tra" quá cao, ví dụ giả định một phần tư bảng, thường cho thấy công thức thiếu một kiểu lỗi chứ không phải dữ liệu tệ đến thế. Lọc các dòng đó, xem chúng giống nhau ở đâu, bổ sung tầng xử lý rồi chạy lại.
Kiểm thêm bằng mắt khoảng hai mươi dòng ngẫu nhiên, so cột gốc với cột chuẩn. Một kiểm tra nhanh khác là đếm số giá trị không trùng trước và sau: nếu sau chuẩn hoá số lượng số khác nhau giảm, nghĩa là nhiều cách viết của cùng một số đã được gom lại, đúng điều bạn muốn. Nếu giảm quá mạnh, có thể công thức đã cắt cụt nhiều số thành cùng một chuỗi.
Khi đã hài lòng, sao chép cột chuẩn và dán dạng giá trị để công thức không phụ thuộc vào cột phụ nữa. Giữ lại cột gốc, đổi tên thành "điện thoại gốc", ẩn đi nếu thấy rối mắt nhưng đừng xoá. Khi khách phàn nàn không nhận được tin nhắn, cột gốc cho biết số ban đầu khách đưa là gì.
Số điện thoại là dữ liệu cá nhân. Tệp đã chuẩn hoá thường được chia sẻ cho nhiều người hơn vì "sạch rồi, dùng được", nên càng cần phân quyền chặt: ai được xem cả cột, ai chỉ xem bốn số cuối. Phần quy định pháp lý về dữ liệu cá nhân có ở trang AI Bảo Mật.
Câu hỏi thường gặp
Vì sao Excel tự xoá số 0 ở đầu số điện thoại?
Vì ô đang ở định dạng số, và với số thì chữ số 0 ở đầu không mang giá trị nên Excel bỏ đi. Đặt định dạng Text cho cột trước khi nhập hoặc nhập tệp CSV qua lệnh From Text/CSV với kiểu cột Text sẽ giữ được số 0.
Số điện thoại nên lưu dạng số hay dạng văn bản?
Nên lưu dạng văn bản. Số điện thoại không dùng để cộng trừ, và lưu dạng văn bản giữ được số 0 đầu, tránh bị hiển thị dạng khoa học khi chuỗi dài. Các hàm so khớp vẫn chạy bình thường nếu cả hai bên cùng là văn bản.
Có thể nhờ chatbot chuẩn hoá luôn cả cột điện thoại không?
Không nên dán cột điện thoại thật vào chatbot vì đó là dữ liệu cá nhân, và chatbot có thể bỏ sót hoặc đổi số khi danh sách dài. Hãy nhờ AI viết công thức từ mô tả và ví dụ đã che, rồi tự chạy công thức trong tệp của bạn.
Số điện thoại 11 chữ số cũ có tự đổi sang 10 số được không?
Được, nếu bạn có bảng quy đổi đầu số chính thức của từng nhà mạng. Tạo bảng tra đầu số cũ sang đầu số mới, rồi dùng hàm tra cứu để thay phần đầu. Không nên để AI tự nhớ bảng quy đổi vì dễ sai.
Ô điện thoại hiện chữ E kèm dấu cộng thì sửa thế nào?
Đó là hiển thị dạng khoa học của số dài. Nếu dữ liệu gốc còn đủ chữ số, đổi định dạng ô sang Number không có số thập phân hoặc sang Text là thấy lại. Nếu Excel đã làm tròn khi lưu thì các chữ số cuối mất thật, cần lấy lại từ nguồn.
Google Sheets có cách nào gọn hơn Excel để chuẩn hoá số điện thoại không?
Google Sheets có hàm REGEXREPLACE, bỏ mọi ký tự không phải chữ số chỉ trong một công thức. Sau đó vẫn cần bước xử lý đầu 84 và bù số 0 như trong Excel, và đặt cột ở định dạng Plain text.