Cách xóa khoảng trắng trong Excel: TRIM(), SUBSTITUTE() và hơn thế nữa

Tôi từng nhận một bảng tính hơn 500 dòng trông ổn lúc đầu nhưng lại không tính tổng chính xác. Lúc đó tôi chưa hiểu vì sao, sau này mới phát hiện các khoảng trắng ẩn gây đủ kiểu rắc rối. Nếu bạn xử lý dữ liệu trong Excel, hẳn đã gặp tình huống tương tự. Những khoảng trắng này thường xuất hiện khi bạn nhập dữ liệu từ nguồn khác.  

Trong hướng dẫn này, tôi sẽ chỉ cho bạn các cách khác nhau để loại bỏ khoảng trắng không mong muốn trong Excel. Bạn sẽ có hướng dẫn từng bước và ví dụ thực tế cho mỗi phương pháp để tránh những rắc rối mà tôi từng gặp. Tuy nhiên, nếu bạn mới bắt đầu, hãy xem khóa Phân tích dữ liệu trong Excel hoặc lộ trình kỹ năng Những điều cơ bản về Excel.

TL;DR

• Khoảng trắng ẩn làm hỏng công thức — khoảng trắng ở đầu, cuối hoặc thừa khiến VLOOKUP() lệch kết quả, SUM() trả về 0 và so sánh thất bại.
• TRIM() — dùng cho hầu hết trường hợp; xóa khoảng trắng đầu/cuối/thừa, giữ một khoảng trắng giữa các từ.
• SUBSTITUTE() — dùng để xóa TẤT CẢ khoảng trắng kể cả giữa các từ, hoặc nhắm mục tiêu ký tự cụ thể.
• CLEAN() — kết hợp với TRIM() để loại bỏ ký tự không thể in từ dữ liệu nhập.
• Khoảng trắng không ngắt dòng? Dùng =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) — giải pháp tất cả trong một.
• Tìm & Thay thế (Ctrl+H) — nhanh nhất để xóa khoảng trắng ngay tại chỗ, không cần cột phụ.

Tìm hiểu về các loại khoảng trắng trong Excel

Trước khi xóa, hãy hiểu các loại khoảng trắng có thể ẩn trong dữ liệu của bạn:

• Khoảng trắng đầu dòng: Khoảng trắng ở đầu ô.
• Khoảng trắng cuối dòng: Khoảng trắng ở cuối ô.
• Khoảng trắng thừa giữa các từ: Nhiều hơn một khoảng trắng giữa các từ.
• Khoảng trắng không ngắt dòng (non-breaking): Khoảng trắng đặc biệt trông bình thường nhưng hành xử khác.

Những khoảng trắng này có thể lọt vào dữ liệu khi nhập tệp, lỗi nhập tay, định dạng không nhất quán, hoặc sao chép dán từ nguồn khác.

Các loại khoảng trắng. Ảnh: Tác giả. 

Các cách xóa khoảng trắng trong Excel

Dù có vẻ nhỏ nhặt, khoảng trắng có thể gây rắc rối lớn. Công thức có thể không chạy, so sánh thất bại và phép tính sai lệch. Trước khi áp dụng giải pháp, tôi khuyên bạn nên:

• Sao lưu dữ liệu: Luôn tạo bản sao trước khi thay đổi. Điều này giúp khôi phục dữ liệu gốc nếu có sự cố.
• Kiểm tra định dạng: Đảm bảo định dạng nhất quán như chữ hoa/thường, định dạng số để kết quả sạch và đáng tin cậy hơn.

Giờ hãy khám phá những phương pháp tốt nhất để xóa khoảng trắng trong Excel.

Phương pháp 1: Dùng hàm TRIM() để xóa khoảng trắng thừa

Hàm TRIM() xóa các khoảng trắng không cần thiết như ở đầu, cuối và nhiều khoảng trắng giữa các từ, ngoại trừ một khoảng trắng đơn. Tham khảo đầy đủ tại hướng dẫn về hàm TRIM() trong Excel. Cách dùng như sau:

1. Tạo một cột phụ cạnh dữ liệu.

2. Ở ô đầu tiên của cột phụ, nhập công thức: =TRIM(A2).

3. Nhấn Enter và kéo xuống để áp dụng cho toàn bộ hàng.

Dùng TRIM() để xóa khoảng trắng. Ảnh: Tác giả.

Mẹo — thay thế dữ liệu gốc: Sau khi áp dụng TRIM() ở cột phụ, sao chép các ô đã làm sạch, sau đó nhấp chuột phải vào cột gốc và chọn Dán đặc biệt > Chỉ giá trị (phím tắt: Alt+E+S+V hoặc Ctrl+Shift+V trên Mac). Cách này chỉ dán giá trị đã làm sạch, cho phép bạn xóa cột phụ sau đó.

Phương pháp 2: Dùng hàm SUBSTITUTE()

Hàm SUBSTITUTE() xóa mọi khoảng trắng (kể cả giữa các từ) trong một ô và thay bất kỳ ký tự nào, kể cả khoảng trắng, bằng ký tự khác. Nó làm sạch dữ liệu số, ví dụ loại bỏ khoảng trắng ngăn cách các chữ số. 

Cú pháp cơ bản:

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

Áp dụng để xóa tất cả khoảng trắng như sau: 

1. Tạo cột phụ.

2. Nhập công thức: =SUBSTITUTE(A2," ","").

3. Nhấn Enter và kéo xuống áp dụng cho tất cả hàng.

Sử dụng hàm SUBSTITUTE(). Ảnh: Tác giả.

Phương pháp 3: Dọn dữ liệu bằng Tìm & Thay thế

Nếu muốn khắc phục nhanh gọn, tôi khuyên dùng công cụ Tìm & Thay thế của Excel — có thể xử lý khoảng trắng thừa giữa các từ. Cách làm:

1. Chọn phạm vi dữ liệu.

2. Nhấn Ctrl+H để mở hộp thoại Tìm & Thay thế.

3. Trong ô Tìm gì, nhập hai khoảng trắng.

4. Trong ô Thay thế bằng, nhập một khoảng trắng.

5. Nhấp Thay thế tất cả.

Dọn khoảng trắng bằng tính năng Tìm & Thay thế. Ảnh: Tác giả.

Lặp lại cho đến khi Excel hiển thị thông báo We couldn't find anything to replace. Lưu ý phương pháp này giảm khoảng trắng đầu/cuối xuống còn một khoảng trắng nhưng không xóa hoàn toàn. Nó chỉ xử lý khoảng trắng giữa các từ. 

Phương pháp 4: Dùng hàm CLEAN() để xóa ký tự không thể in

Khi nhập hoặc sao chép dữ liệu từ nguồn ngoài, ta thường gặp ký tự không thể in gây gián đoạn thao tác. Bạn có thể dùng CLEAN() để xóa chúng, hoặc tốt hơn — kết hợp với TRIM() để dọn khoảng trắng thừa.

Cách thực hiện: 

1. Nhập công thức: =TRIM(CLEAN(A2)) vào cột phụ.

2. Nhấn Enter và kéo công thức xuống.

Kết hợp TRIM() và CLEAN() để xóa khoảng trắng. Ảnh: Tác giả.

Phương pháp 5: Nâng cao — dùng Add-in Excel để nhanh hơn

Bạn có thể dùng add-in bên thứ ba Ablebits Ultimate Suite for Excel. Công cụ Trim Spaces của nó dọn dữ liệu rất nhanh. Chỉ với một cú nhấp, nó xử lý mọi thứ: khoảng trắng đầu/cuối, ngắt dòng, khoảng trắng thừa giữa từ và khoảng trắng không ngắt dòng. Nếu bạn thường xuyên làm với bộ dữ liệu lớn như tôi, nó sẽ tiết kiệm rất nhiều thời gian. 

Cách dùng:

1. Chọn phạm vi ô cần làm sạch.

2. Mở thẻ Ablebits Data và nhấp Trim Spaces.

3. Trong bảng tùy chọn, chọn nội dung cần xóa và nhấp Trim.

Cắt khoảng trắng với add-in của Excel. Ảnh: Tác giả.

Giải pháp cụ thể cho các tình huống xóa khoảng trắng thường gặp trong Excel

Giờ hãy xem vài tình huống phổ biến bạn có thể gặp và cách xử lý nhanh.

Xóa khoảng trắng thừa

Khoảng trắng thừa khiến dữ liệu bừa bộn và khó xử lý. Bạn có thể dùng TRIM() để dọn sạch nhưng vẫn giữ một khoảng trắng giữa các từ. Ví dụ, tôi có vài dòng dữ liệu giãn cách không nhất quán. Để xử lý, tôi nhập công thức sau ở ô B2 và áp dụng cho toàn bộ hàng:

=TRIM(A2)

Dùng TRIM() để xóa khoảng trắng thừa. Ảnh: Tác giả.

Xóa khoảng trắng ở đầu 

Đôi khi ta cần giữ nhiều khoảng trắng giữa từ để dữ liệu dễ đọc hơn, nhưng lại muốn xóa khoảng trắng ở đầu. Hàm TRIM() hữu ích nhưng sẽ xóa tất cả khoảng trắng thừa trong chuỗi. Vì vậy, nếu bạn chỉ muốn xóa khoảng trắng đầu dòng mà giữ nguyên phần còn lại, hãy dùng công thức nâng cao này:

=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))

Cách hoạt động: Công thức xác định ký tự không phải khoảng trắng đầu tiên và giữ mọi thứ từ vị trí đó trở đi. Nhờ vậy, nó giữ khoảng trắng giữa các từ và chỉ cắt ở đầu ô (khoảng trắng đầu dòng).

Chỉ xóa khoảng trắng đầu dòng. Ảnh: Tác giả.

Xóa khoảng trắng ở cuối

Khoảng trắng cuối là khoảng trắng xuất hiện sau văn bản hoặc số trong ô. Những ký tự vô hình này có thể gây lỗi công thức, khiến các ô tưởng như giống hệt lại không khớp trong các hàm như VLOOKUP() hoặc MATCH(). Để tránh điều này, hãy dùng TRIM(). Ví dụ, tôi đặt công thức này ở ô B2 rồi áp dụng cho tất cả hàng để xóa khoảng trắng thừa khỏi dữ liệu.

=TRIM(A2)

Xóa khoảng trắng cuối dòng. Ảnh: Tác giả.

Xóa khoảng trắng trước số

Dù TRIM() rất hiệu quả để dọn khoảng trắng trong văn bản, bạn có thể gặp vấn đề khi dùng với dữ liệu số. Đây là điều xảy ra:

=TRIM(A2)

Hàm TRIM() không xử lý khoảng trắng đúng như mong đợi. Ảnh: Tác giả.

Bạn thấy đấy, TRIM() đã xóa khoảng trắng trước số, nhưng Excel lại không coi kết quả là số nữa — nó coi là văn bản. Dấu hiệu nhận biết:

• Dữ liệu canh trái thay vì canh phải.

• Thanh trạng thái chỉ hiển thị số ô được chọn (COUNT), không có tổng. 

• Nếu cộng các ô này bằng SUM(), bạn sẽ nhận 0.

Bạn cần chuyển văn bản về số để khắc phục. Có thể nhân với 1 hoặc dùng công thức này:

=VALUE(TRIM(A2))

Công thức này xóa khoảng trắng và đảm bảo Excel coi dữ liệu là số.

Kết hợp VALUE() và TRIM() để xóa khoảng trắng trước số. Ảnh: Tác giả.

Xóa khoảng trắng khỏi một chuỗi 

Nếu bạn muốn xóa mọi khoảng trắng, kể cả giữa các từ, hãy dùng hàm SUBSTITUTE():

=SUBSTITUTE(A2, " ", "")

Bạn sẽ thấy công thức loại bỏ toàn bộ khoảng trắng khỏi chuỗi.

Xóa khoảng trắng bằng hàm SUBSTITUTE(). Ảnh: Tác giả.

Xóa ký tự đặc biệt và khoảng trắng không cần thiết  

SUBSTITUTE() rất hữu ích khi bạn muốn xóa ký tự cụ thể khỏi chuỗi. Nhưng bạn có thể kết hợp với TRIM() để dọn ký tự đặc biệt và khoảng trắng trong một lần. Ví dụ, nếu tôi muốn xóa ký tự & và dọn khoảng trắng, tôi dùng công thức này:

=TRIM(SUBSTITUTE(A2, "&", ""))

Bạn có thể thấy TRIM() và SUBSTITUTE() hoạt động khi dùng chung so với chỉ SUBSTITUTE().

Kết hợp TRIM() và SUBSTITUTE() để dọn khoảng trắng. Ảnh: Tác giả.

Xóa ngắt dòng

Đôi khi dữ liệu dán từ web hoặc nguồn khác chứa ngắt dòng (ký tự Alt+Enter, CHAR(10)) trông giống khoảng trắng thừa. Hàm TRIM() sẽ không xử lý chúng. Để xóa:

1. Chọn các ô cần làm sạch.

2. Nhấn Ctrl+H để mở Tìm & Thay thế.

3. Trong ô Tìm gì, nhấn Ctrl+J (con trỏ nhấp nháy xuất hiện — đó là ký tự ngắt dòng).

4. Để trống ô Thay thế bằng (hoặc gõ một khoảng trắng để tránh dính chữ).

5. Nhấp Thay thế tất cả.

Hoặc dùng công thức: =TRIM(CLEAN(A1)) — hàm CLEAN() loại bỏ ngắt dòng (CHAR(10)) và các ký tự không thể in khác (ASCII 1–31) trong một bước.

Vấn đề thường gặp và cách xử lý

Đôi khi khi tôi sao chép hoặc nhập dữ liệu từ nguồn khác, rất nhiều khoảng trắng không ngắt dòng và ký tự lạ xuất hiện. Tôi xử lý như sau: 

Khoảng trắng không ngắt dòng mà TRIM() không xử lý

Hàm TRIM() xóa khoảng trắng thừa, nhưng không xử lý khoảng trắng không ngắt dòng (mã ký tự 160). Chúng thường xuất hiện trong dữ liệu từ trang web hoặc nguồn đã định dạng. Để khắc phục, tôi dùng:

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

Công thức này xóa hoàn toàn khoảng trắng không ngắt dòng. Nếu bạn muốn thay chúng bằng khoảng trắng thường trước (để TRIM() dọn phần còn lại), dùng: =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) — công thức tiêu chuẩn vàng để loại bỏ mọi kiểu khoảng trắng trong một lần.

Ký tự ẩn hoặc vấn đề khi dán dữ liệu

Đôi khi các ký tự ẩn khác len vào, như tab hoặc ngắt dòng. Để tìm chúng, tôi dùng CODE() để kiểm tra mã ký tự:

• Ký tự đầu: =CODE(LEFT(A1,1))

• Ký tự cuối: =CODE(RIGHT(A1,1))

• Vị trí cụ thể: =CODE(MID(A1, n, 1)) (Thay n bằng số vị trí.)

Đây là công thức tôi dùng:

=CODE(RIGHT(C2,1))

Khi biết mã, tôi xóa nó bằng TRIM() và SUBSTITUTE(). Ví dụ, để xóa tab (mã 115), tôi dùng:

(SUBSTITUTE(C2, CHAR(115), “ “))

Dùng CODE(), TRIM() và SUBSTITUTE() để xóa khoảng trắng. Ảnh: Tác giả.

So sánh nhanh các phương pháp

Chưa chắc dùng cách nào? Bảng dưới đây ghép từng tình huống với công cụ phù hợp:

Kết luận

Trong hướng dẫn này, tôi đã giới thiệu các phương pháp như TRIM() cho xử lý đơn giản, SUBSTITUTE() cho vấn đề nâng cao, và add-in nếu bạn thường xuyên phải xóa khoảng trắng và muốn một quy trình khác. Mỗi phương pháp phù hợp với từng loại dữ liệu, hãy chọn cách phù hợp với nhu cầu của bạn. Để có cái nhìn tổng quan về việc giữ bảng tính luôn sạch, xem hướng dẫn làm sạch dữ liệu trong Excel của chúng tôi.

Nếu muốn nâng cao kỹ năng hơn nữa, hãy xem lộ trình kỹ năng Phân tích dữ liệu với các công cụ Power của Excel trên DataCamp. Lộ trình cung cấp các bài học từng bước giúp bạn làm chủ Excel và cải thiện kỹ thuật làm sạch dữ liệu.