Có thể bạn quan tâm: Công Thức Excel Cho Khoảng Thời Gian: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Giới thiệu chung về vấn đề khoảng trắng trong Excel
Trong quá trình làm việc với dữ liệu trên Excel, việc gặp phải các ký tự khoảng trắng (space) không mong muốn là điều rất phổ biến. Những ký tự này có thể xuất hiện ở đầu, cuối, hoặc giữa các từ và thường gây ra những rắc rối như:
- Sai lệch dữ liệu khi thực hiện so sánh, lọc hay tra cứu.
- Lỗi công thức do Excel không nhận dạng đúng giá trị văn bản.
- Khó khăn trong việc chuẩn hoá dữ liệu trước khi đưa vào các hệ thống phân tích khác.
Vì vậy, việc nắm vững các công thức (formula) để xóa khoảng trắng trong Excel là một kỹ năng thiết yếu đối với mọi người dùng, dù bạn là người mới bắt đầu hay một chuyên gia dữ liệu. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, từ những hàm cơ bản như TRIM (hoặc LỌC.KHOẢNG.TRẮNG trong phiên bản tiếng Việt) đến các giải pháp phức tạp hơn dùng kết hợp SUBSTITUTE, REPLACE, TEXTJOIN, và thậm chí là các công thức mảng (array formulas) để xử lý các trường hợp đặc biệt.
Có thể bạn quan tâm: Excel Formula Does Not Calculate: Nguyên Nhân, Giải Pháp Và Cách Khắc Phục Chi Tiết
1. Hiểu rõ các loại khoảng trắng
Trước khi đi vào các công thức, chúng ta cần phân biệt ba loại khoảng trắng thường gặp trong Excel:
| Loại khoảng trắng | Mô tả | Ví dụ trong ô |
|---|---|---|
| Khoảng trắng đơn | Dấu cách tiêu chuẩn (ASCII 32). | "Hello World" |
| Khoảng trắng không phá vỡ (non‑breaking space) | Dấu cách không cho phép ngắt dòng (ASCII 160). Thường xuất hiện khi sao chép dữ liệu từ web. | "Hello World" (với ký tự 160) |
| Khoảng trắng đa dạng | Bao gồm tab (CHAR(9)), xuống dòng (CHAR(10)), ký tự đặc biệt (CHAR(13))… | "Hello\tWorld" hoặc "Hello\nWorld" |
Mỗi loại khoảng trắng cần cách xử lý khác nhau, vì vậy công thức chuẩn sẽ phải có khả năng nhận diện và loại bỏ chúng một cách hiệu quả.
Có thể bạn quan tâm: Excel Cho Quản Lý Chuỗi Cung Ứng: Công Cụ Đắc Lực Đưa Doanh Nghiệp Lên Tầm Cao Mới
2. Hàm TRIM (LỌC.KHOẢNG.TRẮNG) – Giải pháp nhanh nhất cho khoảng trắng đơn
2.1. Cú pháp và cách hoạt động
- Tiếng Anh:
TRIM(text) - Tiếng Việt (Excel 365/2026):
LỌC.KHOẢNG.TRẮNG(văn_bản)
Hàm này thực hiện ba công việc:
- Loại bỏ tất cả khoảng trắng ở đầu và cuối chuỗi.
- Rút gọn các khoảng trắng liên tiếp ở giữa thành một khoảng trắng duy nhất.
- Bảo toàn các ký tự khác (số, ký tự đặc biệt).
Lưu ý:
TRIMchỉ xóa khoảng trắng ASCII 32. Các ký tự không phá vỡ (ASCII 160) hay ký tự tab sẽ không bị loại bỏ.
2.2. Ví dụ thực tế
Giả sử ô A2 chứa dữ liệu: " Excel Formula " (có 3 khoảng trắng ở đầu và cuối, và 3 khoảng trắng liên tiếp giữa các từ). Công thức:
=TRIM(A2)
Kết quả trả về: "Excel Formula" (đã loại bỏ khoảng trắng đầu/cuối và giảm các khoảng trắng liền nhau thành một).
2.3. Khi nào TRIM không đủ?
Nếu dữ liệu chứa:
- Ký tự không phá vỡ (
CHAR(160)) – ví dụ"Hello World"(được sao chép từ một trang web). - Tab (
CHAR(9)) hoặc xuống dòng (CHAR(10)) – ví dụ"Hello World"với tab giữa.
Trong trường hợp này, TRIM sẽ không xóa được các ký tự trên, và chúng ta cần tới các hàm khác.
3. Thay thế ký tự không phá vỡ và các ký tự đặc biệt bằng SUBSTITUTE
3.1. Hàm SUBSTITUTE (THAY.THẾ)
- Tiếng Anh:
SUBSTITUTE(text, old_text, new_text, instance_num) - Tiếng Việt:
THAY.THẾ(văn_bản, văn_bản_cũ, văn_bản_mới, số_lần)
Hàm này cho phép thay thế một chuỗi ký tự bằng một chuỗi khác. Khi kết hợp với CHAR() để chỉ định mã ký tự, chúng ta có thể xóa hoặc thay thế các khoảng trắng đặc biệt.
3.2. Xóa ký tự không phá vỡ (non‑breaking space)
Ký tự không phá vỡ có mã ASCII là 160. Công thức:
=SUBSTITUTE(A2, CHAR(160), "")
Nếu muốn đồng thời loại bỏ khoảng trắng đầu/cuối bằng TRIM:
=TRIM(SUBSTITUTE(A2, CHAR(160), ""))
3.3. Xóa tab, xuống dòng và các ký tự điều khiển
| Ký tự | Mã ASCII | Công thức thay thế |
|---|---|---|
| Tab | CHAR(9) | =SUBSTITUTE(A2, CHAR(9), "") |
| Dòng mới (LF) | CHAR(10) | =SUBSTITUTE(A2, CHAR(10), "") |
| Carriage Return (CR) | CHAR(13) | =SUBSTITUTE(A2, CHAR(13), "") |
Bạn có thể nối nhiều hàm SUBSTITUTE lại để xóa đồng thời nhiều ký tự:
=TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A2, CHAR(160), ""), CHAR(9), ""), CHAR(10), "")
)
3.4. Khi cần thay thế các khoảng trắng liền nhau thành một khoảng trắng duy nhất
Nếu chuỗi có nhiều dấu cách liên tiếp (ví dụ "Hello World" với 4 dấu cách), TRIM sẽ giảm chúng còn một dấu cách, nhưng nếu bạn muốn xóa hoàn toàn các dấu cách thừa, có thể dùng công thức mảng (array) hoặc hàm TEXTJOIN kết hợp FILTERXML. Tuy nhiên, trong hầu hết các trường hợp, TRIM đã đáp ứng đủ.
4. Công thức mảng (Array Formula) để xóa tất cả các khoảng trắng (kể cả trong giữa)
4.1. Tại sao cần công thức mảng?
Khi muốn loại bỏ mọi khoảng trắng trong chuỗi, bao gồm cả các khoảng trắng ở giữa (không chỉ rút gọn thành một), hàm TRIM không đủ. Chúng ta cần một công thức có khả năng lặp lại việc loại bỏ ký tự " " cho tới khi không còn ký tự nào.
4.2. Sử dụng SUBSTITUTE lồng nhau với REPT
Một cách đơn giản (không cần công thức mảng) là lặp lại SUBSTITUTE một số lần đủ lớn để chắc chắn rằng mọi dấu cách đã bị xóa:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, " ", ""), CHAR(160), ""), CHAR(9), "")
Tuy nhiên, nếu có hàng trăm khoảng trắng liên tiếp, cách trên sẽ không thực hiện hết. Vì thế, một giải pháp mạnh mẽ hơn là dùng công thức mảng kết hợp MID và ROW.
4.3. Công thức mảng truyền thống (Excel 2026 và trước)
=TEXTJOIN("", TRUE, IF(MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1) <> " ", MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), "")
)
- Giải thích:
ROW(INDIRECT("1:"&LEN(A2)))tạo một mảng các số từ 1 tới độ dài của chuỗi.MID(A2, …, 1)lấy từng ký tự một.IF(... <> " ", …, "")giữ lại ký tự nếu không phải là dấu cách.TEXTJOIN("", TRUE, …)nối lại các ký tự đã lọc thành chuỗi mới.
Cách nhập: Đối với Excel 2026 và các phiên bản trước, bạn phải nhấn Ctrl + Shift + Enter (đánh dấu công thức là mảng). Excel sẽ tự thêm dấu
{}bao quanh công thức.
4.4. Công thức mảng đơn giản trong Excel 365 / Excel 2026 (Dynamic Arrays)
Có thể bạn quan tâm: Cách Tính Lãi Suất Kép (compound Interest) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
Trong các phiên bản hỗ trợ Dynamic Array, công thức trên không cần Ctrl+Shift+Enter. Bạn chỉ cần nhập:
=TEXTJOIN("", TRUE, FILTER(MID(A2, SEQUENCE(LEN(A2)), 1), MID(A2, SEQUENCE(LEN(A2)), 1) <> " ")
)
SEQUENCE(LEN(A2))tạo một mảng số 1‑n.FILTERgiữ lại các ký tự không phải dấu cách.TEXTJOINnối lại.
Kết quả: Chuỗi đã loại bỏ hoàn toàn mọi khoảng trắng, kể cả trong giữa.
4.5. Xử lý đồng thời các ký tự không phá vỡ và tab
Bạn có thể mở rộng công thức bằng cách sử dụng LET (Excel 365) để tránh lặp lại:
=LET( txt, A2, chars, MID(txt, SEQUENCE(LEN(txt)), 1), clean, FILTER(chars, (chars <> " ") (chars <> CHAR(160)) (chars <> CHAR(9))), TEXTJOIN("", TRUE, clean)
)
Ở đây, hoạt động như AND trong mảng logic, loại bỏ các ký tự " ", CHAR(160), và CHAR(9).
5. Xóa khoảng trắng trong nhiều cột cùng một lúc với Power Query
Khi làm việc với bảng dữ liệu lớn, việc áp dụng công thức cho từng ô sẽ rất mất thời gian. Power Query (tên khác là Get & Transform) là công cụ mạnh mẽ tích hợp trong Excel, cho phép bạn:
- Nhập dữ liệu từ nhiều nguồn (CSV, SQL, web…).
- Biến đổi dữ liệu (xóa khoảng trắng, thay thế ký tự…) một cách tự động.
- Lưu kết quả trở lại bảng tính.
5.1. Các bước thực hiện
- Chọn bảng dữ liệu →
Data→From Table/Range. - Khi Power Query Editor mở ra, chọn các cột cần làm sạch.
- Vào
Transform→Format→Trim(đối với khoảng trắng đầu/cuối) và/hoặcClean(loại bỏ ký tự không in được). - Để xóa mọi khoảng trắng, vào
Add Column→Custom Columnvà nhập công thức M:
m = Text.Remove(ColumnName, {" ", Character.FromNumber(160), Character.FromNumber(9)})
- Nhấn
Close & Loadđể đưa dữ liệu đã xử lý trở lại Excel.
5.2. Ưu điểm của Power Query
- Xử lý hàng nghìn, hàng triệu bản ghi nhanh chóng.
- Tự động cập nhật khi nguồn dữ liệu thay đổi (Refresh).
- Không làm thay đổi dữ liệu gốc, giúp duy trì tính nguyên vẹn.
6. Các trường hợp thực tiễn và giải pháp tối ưu
6.1. Dữ liệu nhập từ web có ký tự không phá vỡ
- Triệu chứng: Khi lọc hoặc so sánh, Excel trả về “#N/A” hoặc “FALSE” dù nội dung trông giống nhau.
- Giải pháp: Dùng công thức kết hợp
TRIM+SUBSTITUTE:
excel =TRIM(SUBSTITUTE(A2, CHAR(160), ""))
Hoặc trong Power Query, dùng Text.Replace:
m = Table.TransformColumns(Source, {{"Column1", each Text.Replace(_, Character.FromNumber(160), ""), type text}})
6.2. Mã số hoặc ID có khoảng trắng không mong muốn
Ví dụ: Mã sản phẩm "AB 123 " (có khoảng trắng ở cuối). Khi dùng VLOOKUP với giá trị không có khoảng trắng, kết quả trả về lỗi.
- Công thức nhanh:
excel =TRIM(A2)
- Nếu có ký tự tab:
excel =TRIM(SUBSTITUTE(A2, CHAR(9), ""))
6.3. Xóa khoảng trắng trong địa chỉ email (đảm bảo không có ký tự thừa)
=LOWER(TRIM(SUBSTITUTE(A2, " ", "")))
LOWERchuyển về chữ thường, tránh lỗi so sánh không phân biệt chữ hoa/thường.
6.4. Xử lý dòng mới trong ô (khi nhập dữ liệu từ CSV)
Nếu ô có ký tự xuống dòng (CHAR(10)), có thể gây lỗi khi xuất ra file CSV.
=TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(10), ""), CHAR(13), ""))
Hoặc dùng CLEAN (Excel 365) để loại bỏ tất cả ký tự không hiển thị:
=CLEAN(A2)
CLEANchỉ loại bỏ các ký tự ASCII 0‑31, không bao gồmCHAR(160)nên vẫn cầnSUBSTITUTEnếu muốn xóa ký tự không phá vỡ.
7. So sánh tốc độ và hiệu suất giữa các phương pháp
| Phương pháp | Độ phức tạp | Tốc độ (trên 100,000 dòng) | Khi nào dùng |
|---|---|---|---|
TRIM | Rất đơn giản | Rất nhanh | Chỉ cần xóa khoảng trắng đầu/cuối, giảm nhiều khoảng trắng thành một |
SUBSTITUTE (đơn) | Trung bình | Nhanh | Xóa ký tự đặc biệt (CHAR(160), CHAR(9),…) |
TRIM + SUBSTITUTE (nối) | Trung bình | Nhanh‑trung bình | Kết hợp cả hai loại ký tự |
Công thức mảng TEXTJOIN + FILTER | Cao | Trung bình‑chậm (do tính toán mảng) | Xóa tất cả khoảng trắng, bao gồm trong giữa |
| Power Query | Cao (cần học) | Rất nhanh (được tối ưu cho dữ liệu lớn) | Xử lý bảng dữ liệu lớn, nhiều cột, hoặc khi muốn tự động hoá quy trình |
Lưu ý: Khi dữ liệu lên tới hàng triệu bản ghi, việc dùng công thức mảng trong ô sẽ làm workbook chậm. Trong trường hợp này, Power Query hoặc VBA là lựa chọn tốt hơn.
8. Tự động hoá bằng VBA: Macro xóa khoảng trắng toàn bộ sheet
Đối với người dùng muốn một nút “Clean Spaces” hoạt động trên toàn bộ sheet, VBA cung cấp giải pháp nhanh chóng.
8.1. Mã VBA mẫu
Sub RemoveAllSpaces() Dim rng As Range Dim cell As Range ' Chọn toàn bộ vùng dữ liệu đã sử dụng Set rng = ActiveSheet.UsedRange Application.ScreenUpdating = False For Each cell In rng If Not IsError(cell) Then With cell ' Xóa khoảng trắng đầu/cuối .Value = Application.WorksheetFunction.Trim(.Value) ' Thay thế ký tự không phá vỡ và tab .Value = Replace(.Value, Chr(160), "") .Value = Replace(.Value, Chr(9), "") .Value = Replace(.Value, Chr(10), "") .Value = Replace(.Value, Chr(13), "") End With End If Next cell Application.ScreenUpdating = True MsgBox "Đã xóa khoảng trắng trong toàn bộ sheet!", vbInformation
End Sub
8.2. Hướng dẫn sử dụng
- Nhấn Alt + F11 → Insert → Module → dán mã trên.
- Đóng VBA editor, vào Developer → Macros, chọn
RemoveAllSpaces→ Run. - Hoặc gán macro này vào một Button trên sheet để người dùng cuối chỉ cần click.
8.3. Khi nào nên dùng VBA?
- Khi cần chạy một thao tác trên nhiều sheet cùng lúc.
- Khi muốn tự động hoá quy trình (ví dụ: mỗi khi nhập dữ liệu mới, macro sẽ chạy ngay).
- Khi dữ liệu quá lớn để công thức mảng hoạt động mượt mà.
9. Kiểm tra và xác nhận kết quả đã xóa khoảng trắng
Sau khi áp dụng các công thức hoặc macro, việc kiểm tra là rất quan trọng để chắc chắn không còn ký tự ẩn. Dưới đây là một số cách:
9.1. Sử dụng hàm LEN và LEN(TRIM)
Nếu LEN(A2) = LEN(TRIM(A2)), thì không có khoảng trắng đầu/cuối. Đối với toàn cột:
=SUMPRODUCT(--(LEN(A2:A1000) <> LEN(TRIM(A2:A1000))))
Kết quả > 0 nghĩa là còn ô nào đó có khoảng trắng đầu/cuối.
9.2. Kiểm tra ký tự không phá vỡ
=SUMPRODUCT(--(LEN(A2:A1000) - LEN(SUBSTITUTE(A2:A1000, CHAR(160), "")) > 0))
Nếu trả về số > 0, có ký tự CHAR(160) tồn tại.
9.3. Kiểm tra toàn bộ ký tự trắng bằng công thức mảng
=LET( txt, A2, spaces, LEN(txt) - LEN(SUBSTITUTE(txt, " ", "")), nbsp, LEN(txt) - LEN(SUBSTITUTE(txt, CHAR(160), "")), tabs, LEN(txt) - LEN(SUBSTITUTE(txt, CHAR(9), "")), spaces + nbsp + tabs
)
Nếu kết quả > 0, vẫn còn khoảng trắng.
10. Tổng kết và lời khuyên thực tiễn
- Bắt đầu với
TRIM– đây là công cụ nhanh nhất để làm sạch dữ liệu cơ bản. - Kết hợp
SUBSTITUTEkhi gặp ký tự không phá vỡ, tab, hoặc xuống dòng. - Sử dụng công thức mảng (
TEXTJOIN+FILTER) nếu muốn loại bỏ mọi khoảng trắng trong chuỗi, kể cả ở giữa. - Power Query là giải pháp tối ưu cho dữ liệu lớn hoặc khi cần áp dụng cùng một quy tắc lên nhiều cột.
- VBA giúp tự động hoá quy trình và xử lý toàn bộ sheet nhanh chóng.
- Luôn kiểm tra bằng các hàm
LEN,SUBSTITUTE, hoặc công cụ “Find & Replace” để đảm bảo không còn ký tự ẩn.
Tip cuối cùng: Khi làm việc với dữ liệu nhập từ nguồn bên ngoài (web, hệ thống ERP, file CSV), luôn kiểm tra xem có ký tự không hiển thị (non‑breaking space, tab) hay không. Việc này sẽ tiết kiệm rất nhiều thời gian trong các bước xử lý tiếp theo như
VLOOKUP,XLOOKUP,MATCHhay tạo PivotTable.
Hy vọng bài viết này đã cung cấp cho bạn một bộ công cụ toàn diện để xóa khoảng trắng trong Excel, từ những công thức đơn giản cho tới các giải pháp nâng cao. Hãy áp dụng và tùy biến cho phù hợp với nhu cầu thực tế của mình. Chúc bạn làm việc hiệu quả và dữ liệu luôn sạch sẽ!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.