Danh sách khách hàng gom từ form đăng ký, tin nhắn Zalo và file cũ thường có họ tên gõ lộn xộn, số điện thoại lẫn dấu chấm, ngày tháng lưu dạng chữ và khách bị nhập 2 lần. Bài viết này hướng dẫn bạn làm sạch dữ liệu trong Excel trên một danh sách khách hàng như vậy, theo từng bước, mỗi bước có ảnh chụp màn hình.

Bạn làm sạch dữ liệu theo 3 việc chính: gõ công thức làm sạch ở cột mới cạnh dữ liệu gốc, thay công thức bằng giá trị, rồi xóa dòng trùng. Ví dụ, ô B2 chứa " nguyễn văn an " có dấu cách thừa và chữ thường, công thức =PROPER(TRIM(B2)) trả về "Nguyễn Văn An". Sau khi làm sạch cả bảng, danh sách 9 dòng trong bài còn 8 khách hàng, vì một khách bị nhập 2 lần dưới 2 dạng khác nhau.

Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Bảng gốc nằm ở cột A đến E, bảng sạch nằm ở cột G đến M. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy, ví dụ =LEFT(I2,1) đổi thành =LEFT(I2;1).

Làm sạch dữ liệu trong Excel gồm những bước nào?

Bài viết đi qua 9 bước theo thứ tự sau:

  1. Bạn nhận biết dữ liệu bẩn và sao lưu bản gốc.

  2. Bạn chuẩn hóa họ tên bằng =PROPER(TRIM()).

  3. Bạn xóa dấu cách copy từ web và ký tự xuống dòng bằng SUBSTITUTE, CLEAN.

  4. Bạn chuẩn hóa mã khách hàng bằng UPPER.

  5. Bạn chuẩn hóa số điện thoại bằng SUBSTITUTE, IF, LEFT và kiểm tra bằng LEN.

  6. Bạn đổi ngày và số đang lưu dạng chữ về ngày, số thật.

  7. Bạn thay công thức bằng giá trị và xóa các cột không cần.

  8. Bạn xóa dòng trùng bằng Remove Duplicates.

  9. Bạn tách tên ra cột riêng bằng Flash Fill.

Thứ tự này quan trọng ở 2 điểm. Thứ nhất, bạn luôn làm ở cột mới và giữ cột gốc cho tới khi kiểm tra xong. Thứ hai, bạn làm sạch xong rồi mới xóa trùng. Hai dòng của cùng một khách chỉ giống hệt nhau sau khi đã bỏ dấu cách thừa, bỏ dấu chấm và đưa số về cùng một dạng.

Bước 1: Bạn nhận biết dữ liệu bẩn như thế nào?

Bảng gốc có 9 dòng khách hàng ở vùng A1:E10. Bạn nhìn từng cột để tìm các dấu hiệu bẩn trước khi gõ công thức.

Bảng gốc 9 khách hàng có mã lúc hoa lúc thường, họ tên thừa dấu cách, số điện thoại lẫn dấu chấm, ngày và doanh số lệch trái phải
Thanh công thức của ô B2 cho thấy dấu cách thừa ở đầu và giữa họ tên

Bảng này có các lỗi sau:

  • Cột Mã KH gốc có mã lúc viết hoa, lúc viết thường. Ô A4 và A8 còn có dấu cách ở cuối, mắt thường không thấy.

  • Cột Họ tên gốc có dấu cách thừa ở đầu và giữa, chữ hoa, chữ thường lộn xộn. Ô B4 có một dấu cách lạ ở cuối do copy từ trang web, còn ô B7 có một ký tự xuống dòng ở cuối. Hai ký tự này cũng không nhìn thấy.

  • Cột Điện thoại gốc có số lẫn dấu chấm, dấu cách và đầu số +84. Ô C4 nằm sát lề phải vì Excel đã hiểu đây là số và bỏ mất số 0 ở đầu. Ô C7 chỉ có 9 chữ số.

  • Cột Ngày mua gốc nằm sát lề trái vì cả cột là chữ. Ngày thật trong Excel nằm sát lề phải.

  • Cột Doanh số gốc có ô nằm sát lề trái là chữ, ví dụ "1.250.000", và ô nằm sát lề phải là số. Nếu bạn gõ =SUM(E2:E10) vào ô E12, Excel chỉ cộng 3 ô số và trả về 8,250,000 thay vì 18,180,000. Bạn xóa ô E12 sau khi xem.

  • Dòng 2 và dòng 8 là cùng một khách KH0158, nhưng Excel không coi là trùng vì 2 dòng khác nhau ở dấu cách, dấu chấm và dạng số.

Trước khi làm, bạn nên sao lưu sheet gốc: bạn nhấn chuột phải vào tên sheet, chọn Move or Copy, tích Create a copy rồi nhấn OK. Sau đó, bạn làm sạch ở các cột G đến M, không sửa thẳng lên cột gốc.

Bạn dựng lại bảng bẩn này để làm theo như thế nào?

Nếu bạn gõ lại bảng như bình thường, Excel tự đổi một phần dữ liệu và bạn sẽ không thấy đúng các lỗi trong bài. Bạn dựng bảng theo 4 bước sau:

  1. Bạn chọn vùng C2:E10, vào tab Home, ở ô định dạng số trong nhóm Number bạn chọn Text. Sau đó, bạn chọn riêng các ô C4, E3, E6, E8 và đổi lại thành General. Ô định dạng Text giữ nguyên chữ bạn gõ, nên số 0 ở đầu, ngày "05/11/2026" và "1.250.000" không bị Excel đổi thành số. Bạn cũng có thể gõ dấu nháy đơn trước nội dung, ví dụ '05/11/2026.

  2. Bạn gõ dữ liệu theo ảnh ở trên. Ô A4 và A8 có thêm một dấu cách ở cuối. Ô B2 có 2 dấu cách ở đầu, 3 dấu cách giữa "nguyễn" và "văn", 1 dấu cách ở cuối. Ô B6 có 2 dấu cách giữa 2 chữ và ô B10 có 1 dấu cách ở đầu.

  3. Bàn phím không gõ được dấu cách mã 160 và ký tự xuống dòng ở cuối ô, nên bạn tạo bằng công thức. Bạn gõ ="lê hoa"&CHAR(160) vào ô B4 và ="vũ thị mai"&CHAR(10) vào ô B7.

  4. Bạn gõ tiêu đề của bảng sạch vào vùng G1:M1: Mã KH, Họ tên, ĐT bỏ ký tự, Điện thoại, Số ký tự, Ngày mua, Doanh số. Bạn chọn cột L, nhấn Ctrl + 1, chọn Custom rồi gõ dd/mm/yyyy. Bạn chọn cột M, nhấn Ctrl + 1, chọn Custom rồi gõ #,##0.

Bước 2: Bạn chuẩn hóa họ tên bằng PROPER(TRIM()) như thế nào?

Bạn ghép 2 hàm để sửa họ tên:

  • Hàm TRIM xóa dấu cách ở đầu, ở cuối và chỉ giữ 1 dấu cách giữa các chữ. Ví dụ, TRIM(" nguyễn văn an ") trả về "nguyễn văn an".

  • Hàm PROPER viết hoa chữ cái đầu của mỗi chữ và viết thường các chữ cái còn lại.

Bạn chọn ô H2, gõ công thức sau rồi nhấn Enter:

=PROPER(TRIM(B2))

Bạn đang gõ vào ô H2 công thức =PROPER(TRIM(B2))
Hàm TRIM chạy trước để bỏ dấu cách thừa, rồi PROPER sửa chữ hoa, chữ thường

Tiếp theo, bạn đưa chuột tới chấm vuông ở góc dưới bên phải ô H2. Khi con trỏ thành dấu cộng đen mảnh, bạn kéo xuống ô H10.

Cột H có 9 họ tên đã viết hoa chữ đầu, ô H4 đang chọn hiện Lê Hoa
Cột H nhìn đã sạch, nhưng ô H4 và H7 vẫn còn ký tự không nhìn thấy

Cột H đã đúng ở dấu cách và chữ hoa. Ô H4 trông như "Lê Hoa" nhưng vẫn chưa sạch, bước 3 cho bạn cách phát hiện và xử lý.

Bước 3: Bạn xóa dấu cách copy từ web và ký tự xuống dòng thế nào?

Bạn phát hiện ký tự không nhìn thấy bằng LEN như thế nào?

Hàm LEN đếm số ký tự trong ô, kể cả ký tự không nhìn thấy. Bạn tạm dùng cột K để kiểm tra. Bạn chọn ô K2, gõ công thức sau rồi nhấn Enter:

=LEN(H2)

Bạn đang gõ vào ô K2 công thức =LEN(H2)
Hàm LEN đếm cả dấu cách và ký tự đặc biệt

Sau đó, bạn kéo công thức từ ô K2 xuống ô K10.

Cột K đếm được 7 ký tự cho Lê Hoa và 11 ký tự cho Vũ Thị Mai
"Lê Hoa" chỉ có 6 ký tự nhưng ô K4 đếm ra 7

"Lê Hoa" có 6 ký tự, gồm cả dấu cách ở giữa, nhưng ô K4 đếm ra 7. "Vũ Thị Mai" có 10 ký tự nhưng ô K7 đếm ra 11. Mỗi ô còn thừa 1 ký tự mà hàm TRIM không xóa được:

  • Ô B4 có dấu cách không ngắt dòng, mã số 160. Ký tự này hay đi theo dữ liệu copy từ trang web. Hàm TRIM chỉ xóa dấu cách thường có mã số 32.

  • Ô B7 có ký tự xuống dòng, mã số 10. Ký tự này sinh ra khi người nhập nhấn Alt + Enter hoặc copy từ phần mềm khác.

Nếu muốn biết ký tự cuối là gì, bạn gõ =CODE(RIGHT(B4,1)) vào ô O4 và =CODE(RIGHT(B7,1)) vào ô O7. Hai công thức trả về 160 và 10. Bạn xóa 2 ô này sau khi xem.

Bạn xóa 2 ký tự lạ bằng SUBSTITUTE và CLEAN như thế nào?

Bạn sửa lại công thức ở cột H. Bạn chọn ô H2, gõ công thức sau rồi nhấn Enter:

=PROPER(TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))))

Bạn đang gõ vào ô H2 công thức =PROPER(TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))))
Công thức có 4 lớp, Excel tính từ lớp trong cùng ra ngoài

Công thức chạy theo 4 lớp từ trong ra ngoài:

  1. Hàm SUBSTITUTE(B2,CHAR(160)," ") thay dấu cách mã 160 bằng dấu cách thường.

  2. Hàm CLEAN xóa các ký tự không in được có mã từ 0 đến 31, trong đó có ký tự xuống dòng mã 10.

  3. Hàm TRIM xóa dấu cách thừa, kể cả dấu cách vừa được đổi từ mã 160.

  4. Hàm PROPER viết hoa chữ cái đầu mỗi chữ.

Sau đó, bạn kéo lại công thức từ ô H2 xuống ô H10. Cột K tự tính lại theo kết quả mới.

Cột K đếm đúng 6 ký tự cho Lê Hoa và 10 ký tự cho Vũ Thị Mai
Ô K4 còn 6 và ô K7 còn 10, đúng bằng số ký tự nhìn thấy

Kiểm tra xong, bạn xóa công thức ở vùng K2:K10 để dùng cột K cho bước 5.

Hàm CLEAN xóa hẳn ký tự xuống dòng chứ không thay bằng dấu cách. Nếu ký tự xuống dòng nằm giữa 2 chữ, ví dụ "Vũ Thị" xuống dòng "Mai", hàm CLEAN trả về "Vũ ThịMai" và công thức 4 lớp ở trên trả về "Vũ Thịmai". Trường hợp này, bạn thay ký tự xuống dòng bằng dấu cách: =PROPER(TRIM(SUBSTITUTE(B2,CHAR(10)," "))). Bài hàm xử lý chuỗi trong Excel giải thích riêng từng hàm TRIM, SUBSTITUTE, LEFT, MID, FIND.

Bước 4: Bạn chuẩn hóa mã khách hàng bằng UPPER thế nào?

Hàm UPPER đổi mọi chữ cái thành chữ hoa. Mã khách hàng, mã hàng, biển số xe thường cần viết hoa hết. Bạn chọn ô G2, gõ công thức sau rồi nhấn Enter:

=UPPER(TRIM(A2))

Bạn đang gõ vào ô G2 công thức =UPPER(TRIM(A2))
Hàm TRIM bỏ dấu cách ở cuối mã, hàm UPPER viết hoa mã

Sau đó, bạn kéo công thức từ ô G2 xuống ô G10.

Cột G có 9 mã viết hoa từ KH0158 đến KH0165, ô G4 đang chọn
Ô G4 trả về KH0160 dù ô A4 gốc là "Kh0160 " có dấu cách ở cuối

Với email, bạn dùng hàm LOWER để viết thường hết. Ví dụ, nếu bảng của bạn có ô N2 chứa email "Lan.Nguyen@Example.COM", bạn gõ =LOWER(TRIM(N2)) vào ô O2 và nhận "lan.nguyen@example.com".

Bước 5: Bạn chuẩn hóa số điện thoại thế nào?

Số điện thoại di động Việt Nam chuẩn gồm 10 chữ số, bắt đầu bằng số 0 và không có dấu chấm, dấu cách. Số máy bàn có 11 chữ số, nên bạn tách riêng nếu danh sách có số máy bàn. Bạn làm theo 3 cột: cột I bỏ ký tự thừa, cột J thêm số 0, cột K kiểm tra đủ 10 số.

Bạn bỏ dấu chấm, dấu cách và đổi +84 thành 0 bằng SUBSTITUTE như thế nào?

Hàm SUBSTITUTE thay một đoạn chữ bằng đoạn chữ khác. Bạn lồng 3 hàm SUBSTITUTE để làm 3 việc. Bạn chọn ô I2, gõ công thức sau rồi nhấn Enter:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"+84","0"),".","")," ","")

Bạn đang gõ vào ô I2 công thức có 3 hàm SUBSTITUTE lồng nhau
Mỗi lớp SUBSTITUTE thay một loại ký tự

Công thức chạy theo 3 lớp từ trong ra ngoài:

  1. Lớp trong cùng thay "+84" bằng "0".

  2. Lớp giữa thay dấu chấm bằng chuỗi rỗng "", tức là xóa dấu chấm.

  3. Lớp ngoài cùng xóa dấu cách.

Sau đó, bạn kéo công thức từ ô I2 xuống ô I10.

Cột I có các số đã bỏ dấu chấm, dấu cách, ô I4 là 903111222 vẫn thiếu số 0
Ô I5 đổi "+84 909 888 777" thành 0909888777, còn ô I4 vẫn thiếu số 0 ở đầu

Ô I4 còn thiếu số 0 vì ô C4 gốc là số, Excel đã bỏ số 0 từ lúc nhập.

Bạn thêm số 0 ở đầu bằng IF và LEFT như thế nào?

Bạn chọn ô J2, gõ công thức sau rồi nhấn Enter:

=IF(LEFT(I2,1)="0",I2,"0"&I2)

Bạn đang gõ vào ô J2 công thức =IF(LEFT(I2,1)="0",I2,"0"&I2)
Hàm LEFT lấy ký tự đầu tiên để kiểm tra có phải số 0 không

Hàm LEFT(I2,1) lấy ký tự đầu tiên của ô I2. Nếu ký tự đó là "0", công thức giữ nguyên số. Nếu không, công thức ghép thêm "0" vào đầu bằng dấu &. Sau đó, bạn kéo công thức từ ô J2 xuống ô J10.

Bạn không nên dùng điều kiện "đủ 9 số thì thêm số 0". Ô I7 có 9 ký tự "098765432" nhưng đã có số 0 ở đầu, tức là khách ghi thiếu một số ở chỗ khác. Nếu thêm số 0, bạn được một số đủ 10 ký tự nhưng sai, và bước kiểm tra sau không phát hiện được.

Bạn kiểm tra đủ 10 số bằng LEN như thế nào?

Bạn chọn ô K2, gõ công thức sau rồi nhấn Enter:

=LEN(J2)

Bạn đang gõ vào ô K2 công thức =LEN(J2)
Số điện thoại di động Việt Nam đúng có 10 ký tự

Sau đó, bạn kéo công thức từ ô K2 xuống ô K10.

Cột J có số điện thoại đủ số 0 ở đầu, cột K ra 10 ở mọi dòng trừ ô K7 ra 9
Ô K7 ra 9, số điện thoại của khách KH0163 bị thiếu một chữ số

Cột J giờ có số 0 ở đầu mọi số điện thoại. Ô K7 ra 9, nên bạn ghi lại khách KH0163 để gọi hoặc nhắn hỏi lại số đúng. Excel không tự đoán được chữ số bị thiếu. Bài hàm LEN trong Excel có thêm cách lọc riêng các số điện thoại bị thiếu số.

Kết quả ở cột I và cột J là chữ, không phải số. Nhờ vậy, Excel giữ nguyên số 0 ở đầu.

Bước 6: Bạn đổi ngày và số dạng chữ về ngày, số thật thế nào?

Bạn đổi ngày dạng chữ bằng DATE, RIGHT, MID, LEFT như thế nào?

Ngày dạng chữ "05/11/2026" không cộng trừ, lọc theo tháng hay sắp xếp đúng được. Bạn cắt chữ thành 3 phần rồi ghép lại bằng hàm DATE. Bạn chọn ô L2, gõ công thức sau rồi nhấn Enter:

=DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))

Bạn đang gõ vào ô L2 công thức =DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))
Hàm DATE nhận lần lượt năm, tháng, ngày

Với "05/11/2026", các hàm cắt chữ như sau:

  • Hàm RIGHT(D2,4) lấy 4 ký tự cuối là năm "2026".

  • Hàm MID(D2,4,2) lấy 2 ký tự bắt đầu từ vị trí thứ 4 là tháng "11".

  • Hàm LEFT(D2,2) lấy 2 ký tự đầu là ngày "05".

  • Hàm DATE ghép năm, tháng, ngày thành một ngày thật.

Sau đó, bạn kéo công thức từ ô L2 xuống ô L10. Cột L đã được định dạng sẵn kiểu dd/mm/yyyy nên hiện ngày dạng 05/11/2026. Nếu ô của bạn hiện một số như 46331, bạn chọn cột, nhấn Ctrl + 1, chọn Date hoặc Custom rồi gõ dd/mm/yyyy.

Bạn không nên dùng hàm DATEVALUE cho ngày dạng chữ. Hàm DATEVALUE đọc ngày theo cài đặt ngày tháng của Windows: máy đặt kiểu ngày/tháng/năm hiểu "05/11/2026" là ngày 5 tháng 11, còn máy đặt kiểu tháng/ngày/năm hiểu là ngày 11 tháng 5. Công thức DATE ở trên cho cùng kết quả trên mọi máy.

Bạn đổi số dạng chữ có dấu chấm bằng VALUE và SUBSTITUTE như thế nào?

Doanh số "1.250.000" là chữ vì có dấu chấm ngăn hàng nghìn. Bạn chọn ô M2, gõ công thức sau rồi nhấn Enter:

=VALUE(SUBSTITUTE(E2,".",""))

Bạn đang gõ vào ô M2 công thức =VALUE(SUBSTITUTE(E2,".",""))
Hàm SUBSTITUTE xóa dấu chấm, rồi hàm VALUE đổi chữ thành số

Hàm SUBSTITUTE xóa dấu chấm và trả về "1250000". Hàm VALUE đổi chữ "1250000" thành số 1250000. Với ô đã là số như E3, công thức vẫn trả về đúng 2500000. Sau đó, bạn kéo công thức từ ô M2 xuống ô M10.

Cột L có ngày nằm sát lề phải, cột M có doanh số định dạng 1,250,000 nằm sát lề phải
Ngày và doanh số đã nằm sát lề phải, tức là Excel đã hiểu là ngày và số

Giờ bạn gõ =SUM(M2:M10) vào ô M12 sẽ nhận 18,180,000, đúng bằng tổng 9 dòng. Bạn xóa ô M12 sau khi xem. Bạn chỉ dùng cách xóa dấu chấm khi dấu chấm là dấu ngăn hàng nghìn. Nếu cột có số thập phân như "12.5", công thức sẽ trả về 125. Bài chuyển text thành số trong Excel có thêm các cách đổi chữ thành số khác như Paste Special và Text to Columns.

Bước 7: Bạn thay công thức bằng giá trị thế nào?

Bảng sạch ở cột G đến M vẫn là công thức lấy dữ liệu từ cột gốc. Nếu bạn xóa cột gốc lúc này, mọi công thức báo lỗi #REF!. Vì vậy, bạn đổi công thức thành giá trị trước:

  1. Bạn chọn vùng G1:M10 rồi nhấn Ctrl + C.

  2. Bạn nhấn chuột phải vào ô G1, ở mục Paste Options bạn chọn biểu tượng Values (biểu tượng có số 123). Bạn cũng có thể nhấn Ctrl + Alt + V, chọn Values rồi nhấn OK.

  3. Bạn nhấn Esc để bỏ viền nhấp nháy quanh vùng vừa sao chép.

Sau đó, bạn xóa các cột không cần nữa. Bạn chọn cột K, nhấn chuột phải và chọn Delete, rồi làm tương tự với cột I. Cuối cùng, bạn chọn các cột từ A đến F và xóa. Bảng sạch dồn về cột A đến E.

Bảng sạch 5 cột Mã KH, Họ tên, Điện thoại, Ngày mua, Doanh số, ô C2 chọn và thanh công thức hiện 0912345678
Thanh công thức của ô C2 hiện số điện thoại, không còn công thức

Số điện thoại vẫn nằm sát lề trái vì đây là chữ. Bạn giữ nguyên dạng chữ cho cột này. Nếu ô có tam giác xanh nhỏ ở góc trên bên trái, Excel đang nhắc "Number Stored as Text". Bạn bỏ qua nhắc nhở này và không chọn Convert to Number, vì đổi sang số sẽ làm mất số 0 ở đầu.

Bước 8: Bạn xóa dòng trùng bằng Remove Duplicates thế nào?

Bạn chọn một ô bất kỳ trong bảng, ví dụ ô A2. Sau đó, bạn vào tab Data, nhóm Data Tools, bấm Remove Duplicates.

Hộp thoại Remove Duplicates có tích My data has headers và cả 5 cột, con trỏ chỉ vào nút OK
Excel tự chọn cả bảng và tích sẵn 5 cột

Trong hộp thoại, ô My data has headers đã được tích vì dòng 1 là tiêu đề. Cả 5 cột đều được tích, nghĩa là Excel chỉ xóa dòng giống nhau ở cả 5 cột. Bạn nhấn OK.

Thông báo 1 duplicate values found and removed; 8 unique values remain
Excel báo đã xóa 1 dòng trùng và còn lại 8 dòng

Bạn nhấn OK để đóng thông báo.

Bảng còn 8 khách hàng từ KH0158 đến KH0165, mỗi mã một dòng
Dòng KH0158 thứ hai đã bị xóa, Excel giữ lại dòng xuất hiện trước

Excel giữ dòng xuất hiện đầu tiên và xóa các dòng giống nó ở phía dưới. Nếu bạn chạy Remove Duplicates ngay trên bảng gốc ở bước 1, Excel không xóa được dòng nào, vì dòng 2 và dòng 8 khác nhau ở dấu cách, dấu chấm và dạng số. Remove Duplicates không phân biệt chữ hoa, chữ thường, nhưng có phân biệt dấu cách và phân biệt số dạng chữ với số thật.

Nếu muốn mỗi mã khách hàng chỉ còn một dòng dù các cột khác lệch nhau, bạn bấm Unselect All rồi chỉ tích cột Mã KH. Bài lọc dữ liệu trùng trong Excel hướng dẫn thêm cách đếm và lọc dòng trùng bằng COUNTIF, UNIQUE trước khi xóa.

Bước 9: Bạn tách tên ra cột riêng bằng Flash Fill thế nào?

Danh sách khách hàng tiếng Việt thường được sắp xếp theo tên, nên bạn cần cột Tên riêng. Tính năng Flash Fill điền dữ liệu theo mẫu bạn gõ ở ô đầu tiên. Excel có Flash Fill từ bản 2013.

Bạn gõ tiêu đề "Tên" vào ô F1. Bạn gõ "An" vào ô F2, là tên của khách ở ô B2, rồi nhấn Enter.

Ô F2 có chữ An, ô F3 đang chọn, cột F còn trống từ dòng 3
Ô F2 là mẫu cho Excel biết bạn muốn lấy chữ cuối của họ tên

Con trỏ đang ở ô F3, bạn nhấn Ctrl + E. Bạn cũng có thể vào tab Data và bấm Flash Fill.

Cột F điền đủ An, Hà, Hoa, Đức, Khang, Mai, Huy, Lan
Flash Fill lấy đúng chữ cuối cho cả họ tên 2, 3 và 4 chữ

Kết quả của Flash Fill là giá trị, không phải công thức. Nếu bạn sửa họ tên ở cột B, cột F không tự cập nhật. Bạn nên xem lại vài dòng sau khi nhấn Ctrl + E, vì Flash Fill đoán theo mẫu và có thể đoán sai với dữ liệu lạ.

Nếu cần công thức, bạn gõ =LEFT(B2,FIND(" ",B2)-1) vào ô G2 để lấy họ. Công thức này tìm vị trí dấu cách đầu tiên bằng FIND rồi lấy phần đứng trước, nên "Nguyễn Văn An" trả về "Nguyễn". Bạn cũng có thể dùng Data > Text to Columns để tách theo dấu cách, nhưng họ tên 3 chữ và 4 chữ sẽ bị chia thành số cột khác nhau. Bài tách tên trong Excel có công thức lấy tên cho mọi độ dài họ tên.

Những hàm nào hay dùng khi làm sạch dữ liệu trong Excel?

Bảng sau tóm tắt các hàm làm sạch dữ liệu trong Excel đã dùng trong bài.

Hàm

Việc hàm làm

Ví dụ trong bài

TRIM

Hàm này xóa dấu cách ở đầu, cuối và giữ 1 dấu cách giữa các chữ.

=TRIM(B2)

CLEAN

Hàm này xóa ký tự không in được có mã 0 đến 31, gồm ký tự xuống dòng.

=CLEAN(B2)

SUBSTITUTE

Hàm này thay một đoạn chữ bằng đoạn chữ khác hoặc xóa đi.

=SUBSTITUTE(C2,".","")

PROPER

Hàm này viết hoa chữ cái đầu mỗi chữ.

=PROPER(TRIM(B2))

UPPER, LOWER

Hai hàm này viết hoa hết hoặc viết thường hết.

=UPPER(TRIM(A2))

LEFT, RIGHT, MID

Ba hàm này lấy ký tự ở đầu, ở cuối hoặc ở giữa chuỗi.

=LEFT(D2,2)

FIND

Hàm này trả về vị trí của một ký tự trong chuỗi.

=FIND(" ","Nguyễn Văn An") trả về 7

LEN

Hàm này đếm số ký tự, dùng để phát hiện ký tự thừa hoặc thiếu.

=LEN(J2)

VALUE

Hàm này đổi chữ trông như số thành số thật.

=VALUE(SUBSTITUTE(E2,".",""))

DATE

Hàm này ghép năm, tháng, ngày thành ngày thật.

=DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))

IF

Hàm này chọn kết quả theo điều kiện.

=IF(LEFT(I2,1)="0",I2,"0"&I2)

Khi nào bạn nên làm sạch dữ liệu bằng Power Query?

Nếu mỗi tháng bạn nhận một file xuất từ phần mềm với cùng kiểu lỗi, bạn có thể dùng Power Query. Power Query ghi lại từng bước làm sạch. Tháng sau, bạn dán dữ liệu mới vào bảng gốc rồi vào Data > Refresh All, các bước tự chạy lại. Power Query có sẵn từ Excel 2016 ở tab Data. Bản 2016 gọi nhóm này là Get & Transform và nút là From Table, các bản mới hơn gọi là Get & Transform Data và From Table/Range.

Power Query có sẵn nhiều lệnh làm việc giống các hàm trong bài:

  • Tab Transform > Format có Trim, Clean, Capitalize Each Word (giống PROPER) và UPPERCASE (giống UPPER).

  • Tab Transform có Replace Values để thay một ký tự bằng ký tự khác. Muốn xóa ký tự, bạn để trống ô Replace With.

  • Tab Home có Remove Rows > Remove Duplicates để xóa dòng trùng và Close & Load để đưa bảng sạch ra sheet mới.

Các lệnh có sẵn này chưa làm đủ việc như các công thức trong bài. Nếu bạn chỉ chạy Clean, Trim, Capitalize Each Word, UPPERCASE, Replace Values cho dấu chấm và dấu cách, rồi Remove Duplicates trên danh sách ở bước 1, bảng vẫn còn 9 dòng vì 4 lý do sau:

  • Lệnh Trim của Power Query chỉ xóa dấu cách ở đầu và cuối, không gộp nhiều dấu cách ở giữa thành 1 như hàm TRIM. Ô B2 vẫn ra "Nguyễn Văn An".

  • Cột mã cần chạy thêm Trim, vì UPPERCASE không xóa dấu cách ở cuối "KH0158 ".

  • Cột điện thoại cần thêm Replace Values đổi "+84" thành "0" và một cột tùy chỉnh (Custom Column) để thêm số 0 cho số bị mất 0.

  • Remove Duplicates của Power Query phân biệt chữ hoa, chữ thường, khác với Remove Duplicates trên sheet. Bạn phải chuẩn hóa chữ hoa, chữ thường trước khi xóa trùng.

Vì vậy, bạn nên làm sạch bằng hàm trước để biết dữ liệu cần sửa những gì. Khi chuyển sang Power Query, bạn thêm đủ các bước còn thiếu ở trên và đặt Remove Duplicates ở cuối cùng.

Chuẩn hóa dữ liệu trong Excel khác làm sạch dữ liệu thế nào?

Nhiều người dùng 2 cụm từ này thay cho nhau. Làm sạch dữ liệu là sửa nội dung bị sai trong từng ô, như dấu cách thừa, ký tự lạ, số dạng chữ và dòng trùng. Chuẩn hóa dữ liệu là đưa mọi ô trong cột về cùng một quy ước, ví dụ họ tên viết hoa chữ đầu, số điện thoại đủ 10 số bắt đầu bằng 0, ngày theo dạng dd/mm/yyyy. Các bước 2 đến 6 trong bài vừa làm sạch vừa chuẩn hóa.

Chuẩn hóa còn gồm việc đưa bảng về đúng cấu trúc: mỗi cột chỉ chứa một loại thông tin, mỗi dòng là một khách hàng, không gộp ô và không có dòng trống giữa bảng. Bảng đúng cấu trúc giúp các bước lọc, Pivot Table và hàm tra cứu chạy đúng.

Bạn hay gặp lỗi gì khi làm sạch dữ liệu trong Excel?

Lỗi

Nguyên nhân

Cách sửa

Bạn dùng TRIM rồi mà LEN vẫn đếm thừa ký tự

Ô có dấu cách mã 160 copy từ web hoặc ký tự xuống dòng mã 10.

Bạn dùng =TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," "))).

Hai chữ bị dính vào nhau sau khi dùng CLEAN

Ký tự xuống dòng nằm giữa 2 chữ, CLEAN xóa hẳn ký tự đó.

Bạn dùng =TRIM(SUBSTITUTE(B2,CHAR(10)," ")).

PROPER trả về "NguyễN Thị ÁNh"

Họ tên được gõ bằng bảng mã Unicode tổ hợp, dấu thanh là một ký tự riêng nên PROPER viết hoa chữ đứng sau dấu.

Bạn đổi dữ liệu sang Unicode dựng sẵn bằng công cụ chuyển mã của Unikey (Ctrl + Shift + F6) rồi dùng lại PROPER.

Số điện thoại mất số 0 ở đầu

Cột điện thoại bị đổi sang số, hoặc dữ liệu được nhập như số từ đầu.

Bạn giữ cột điện thoại ở dạng chữ và thêm số 0 bằng =IF(LEFT(I2,1)="0",I2,"0"&I2).

Ngày ra năm 6333

Ô ngày gốc đã là ngày thật, công thức LEFT, MID, RIGHT cắt nhầm con số bên trong ngày.

Bạn dùng =IF(ISNUMBER(D2),D2,DATE(RIGHT(D2,4),MID(D2,4,2),LEFT(D2,2))).

Ngày báo #VALUE!

Ngày gõ thiếu số 0, ví dụ "5/11/2026", nên MID cắt sai vị trí.

Bạn dùng Data > Text to Columns, ở bước 3 chọn Date kiểu DMY.

VALUE báo #VALUE!

Ô còn ký tự khác số, ví dụ chữ "đ" hoặc dấu cách mã 160.

Bạn xóa thêm ký tự đó bằng một lớp SUBSTITUTE nữa.

Ô trống ra "0" hoặc #VALUE!

Công thức vẫn chạy trên ô gốc trống.

Bạn bọc công thức bằng IF kiểm tra ô gốc của từng cột, ví dụ =IF(C2="","",công thức cũ) cho cột điện thoại và =IF(D2="","",công thức cũ) cho cột ngày.

Remove Duplicates không xóa được dòng nào

Bạn xóa trùng trên dữ liệu chưa làm sạch.

Bạn làm sạch xong, thay công thức bằng giá trị rồi mới xóa trùng.

Bảng sạch báo #REF! sau khi xóa cột gốc

Bảng sạch vẫn là công thức tham chiếu cột gốc.

Bạn nhấn Ctrl + Z, thay công thức bằng giá trị ở bước 7 rồi mới xóa cột gốc.

Số thập phân bị sai sau khi xóa dấu chấm

Dấu chấm trong ô là dấu thập phân, không phải dấu ngăn hàng nghìn.

Bạn chỉ xóa dấu chấm ở cột chắc chắn là số nguyên.

Bạn muốn học Excel bài bản hơn?

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.