Làm Sạch Dữ Liệu Trong Excel: TRIM, CLEAN, SUBSTITUTE Và Các Kỹ Thuật Data Cleaning

1. Tại sao dữ liệu luôn bẩn? #

Dữ liệu thực tế KHÔNG BAO GIỜ sạch: import từ hệ thống khác có khoảng trắng thừa, copy từ web có ký tự ẩn, nhập tay bị sai chính tả, format không đồng nhất. "Garbage in, garbage out" — dữ liệu bẩn cho ra kết quả sai.

2. TRIM — Xóa khoảng trắng thừa #

=TRIM(text)

Xóa tất cả khoảng trắng thừa:

• Đầu chuỗi: " An Nguyễn" → "An Nguyễn"

• Cuối chuỗi: "An Nguyễn " → "An Nguyễn"

• Giữa (chỉ giữ 1): "An Nguyễn" → "An Nguyễn"

2.1. TRIM không xóa được #

TRIM chỉ xóa ký tự space (ASCII 32). Không xóa:

• Non-breaking space (ASCII 160) — hay gặp từ web

• Tab, line break

Giải pháp: =TRIM(SUBSTITUTE(A1, CHAR(160), " "))

3. CLEAN — Xóa ký tự không in được #

=CLEAN(text)

Xóa ký tự ASCII 0-31 (non-printable): line break, tab, null character.

3.1. Kết hợp TRIM + CLEAN #

=TRIM(CLEAN(A1))

Công thức "quét sạch" cơ bản: xóa ký tự ẩn → xóa khoảng trắng thừa.

4. SUBSTITUTE — Thay thế text #

=SUBSTITUTE(text, old_text, new_text, [instance_num])

4.1. Xóa ký tự cụ thể #

=SUBSTITUTE(A1, "-", "")        → Xóa tất cả dấu gạch ngang
=SUBSTITUTE(A1, " ", "")        → Xóa tất cả khoảng trắng
=SUBSTITUTE(A1, CHAR(10), " ")  → Thay line break bằng space

4.2. Thay thế lần xuất hiện thứ N #

=SUBSTITUTE(A1, ".", ",", 1)    → Chỉ thay dấu chấm đầu tiên

4.3. Nhiều SUBSTITUTE lồng nhau #

Xóa nhiều ký tự cùng lúc:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), " ", ""), ".", "")

5. UPPER, LOWER, PROPER — Chuẩn hóa chữ hoa/thường #

=UPPER("an nguyễn")   → "AN NGUYỄN"
=LOWER("AN NGUYỄN")   → "an nguyễn"
=PROPER("an nguyễn")  → "An Nguyễn"

5.1. Lưu ý PROPER với tiếng Việt #

PROPER viết hoa chữ đầu mỗi từ. Với tiếng Việt thường OK, nhưng cần kiểm tra:

• "TP.HCM" → "Tp.Hcm" (sai!)

• "NGUYỄN VĂN AN" → "Nguyễn Văn An" (đúng!)

6. Remove Duplicates — Xóa trùng lặp #

1. Chọn vùng dữ liệu

2. Data → Remove Duplicates

3. Chọn cột kiểm tra trùng → OK

Excel xóa dòng trùng, giữ dòng đầu tiên.

6.1. Đếm trước khi xóa #

=COUNTIF(A:A, A2)

Nếu > 1 → dòng trùng. Lọc filter > 1 để xem trước khi xóa.

7. Text to Columns — Tách dữ liệu #

Data → Text to Columns

7.1. Tách bằng Delimiter #

"An Nguyễn, Kinh doanh, 10000000" → tách bằng dấu ,

7.2. Tách bằng Fixed Width #

Dữ liệu có cấu trúc cố định → đặt điểm cắt thủ công.

7.3. Ứng dụng: Fix số bị dính text #

Cột số hiện text (căn trái, VLOOKUP lỗi):

1. Chọn cột → Data → Text to Columns

2. Next → Next → chọn General → Finish

Excel parse lại → số trở về đúng format.

8. Flash Fill — Tự đoán pattern #

Ctrl + E hoặc Data → Flash Fill

8.1. Ví dụ: Tách họ tên #

Flash Fill tự đoán: bạn đang lấy từ đầu tiên → áp dụng cho tất cả.

8.2. Ví dụ: Chuẩn hóa số điện thoại #

9. Find & Replace cho Data Cleaning #

Ctrl + H

9.1. Xóa line break #

• Find: Ctrl+J (nhập line break)

• Replace: (để trống hoặc space)

9.2. Xóa tất cả số #

• Find: [0-9] (bật Use wildcards)

• Replace: (để trống)

9.3. Chuẩn hóa spacing #

• Find: 2 spaces " "

• Replace: 1 space " "

• Click Replace All nhiều lần cho đến khi 0 replacements

10. VALUE, TEXT — Chuyển đổi kiểu dữ liệu #

10.1. Chuyển text thành số #

=VALUE("1234")       → 1234 (dạng số)
=VALUE("10/03/2024") → 45361 (serial date)

10.2. Nhân 1 hoặc cộng 0 #

=A1*1    → ép text thành số
=A1+0    → ép text thành số
=A1&""   → ép số thành text

10.3. Chuyển số thành text format #

=TEXT(1234567, "#,##0")      → "1,234,567"
=TEXT(0.85, "0.0%")          → "85.0%"
=TEXT(TODAY(), "DD/MM/YYYY") → "02/03/2024"

11. CHAR và CODE — Xử lý ký tự đặc biệt #

=CODE("A")   → 65
=CHAR(65)    → "A"
=CHAR(10)    → Line break
=CHAR(9)     → Tab
=CHAR(160)   → Non-breaking space

11.1. Tạo line break trong công thức #

="Dòng 1" & CHAR(10) & "Dòng 2"

Bật Wrap Text để hiện 2 dòng.

12. Công thức Data Cleaning tổng hợp #

12.1. Ultimate clean formula #

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1, CHAR(160), " "), CHAR(9), " ")))

Xóa: non-breaking space, tab, ký tự ẩn, khoảng trắng thừa.

12.2. Chỉ giữ số #

=SUMPRODUCT(MID(0&A1, LARGE(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1))*ROW(INDIRECT("1:"&LEN(A1))), ROW(INDIRECT("1:"&LEN(A1))))+1, 1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

Hoặc đơn giản hơn: Flash Fill (Ctrl+E).

13. Mẹo Data Cleaning #

1. Backup trước khi clean: Copy sheet gốc trước mọi thao tác

2. TRIM+CLEAN luôn: Áp dụng cho MỌI dữ liệu import

3. Text to Columns fix số: Số bị text → Text to Columns → General

4. Flash Fill > công thức: Nhanh hơn viết công thức phức tạp

5. Validate sau khi clean: Dùng COUNTIF kiểm tra trùng, ISBLANK kiểm tra trống

14. Tổng kết #

Data Cleaning chiếm 80% thời gian phân tích dữ liệu. Nắm vững TRIM, CLEAN, SUBSTITUTE, Flash Fill, và Text to Columns giúp bạn biến dữ liệu bẩn thành dữ liệu sạch — nền tảng cho MỌI phân tích chính xác.