Có thể bạn quan tâm: Bẻ Khóa Vba Trong Excel: Hướng Dẫn Chi Tiết, Pháp Lý Và Các Phương Pháp Hiệu Quả
Mở Đầu
Trong môi trường làm việc với dữ liệu, việc xử lý và làm sạch dữ liệu (data cleaning) là một trong những bước quan trọng nhất. Đặc biệt, khi làm việc với Excel, chúng ta thường gặp phải các vấn đề như khoảng trắng thừa (leading, trailing hoặc giữa các từ), các ký tự không mong muốn, hoặc thậm chí là các ký tự không hiển thị được. Những vấn đề này không chỉ làm cho dữ liệu trông rối mắt mà còn gây ra lỗi trong các công thức, báo cáo, và quá trình phân tích dữ liệu.
Bài viết này sẽ tập trung vào hàm bỏ khoảng trắng trong Excel, cung cấp cho bạn một hướng dẫn chi tiết, từ các 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 kỹ thuật nâng cao, kết hợp với các hàm khác như SUBSTITUTE, REPLACE, CLEAN, TEXTJOIN, và các công cụ Power Query. Chúng ta sẽ đi qua:
- Khái niệm và các loại khoảng trắng – hiểu rõ khoảng trắng nào cần loại bỏ.
- Hàm TRIM (LỌC.KHOẢNG.TRẮNG) – cách dùng cơ bản, ưu nhược điểm.
- Xử lý khoảng trắng giữa các từ – sử dụng
SUBSTITUTEvà các công thức phức tạp. - Kết hợp hàm CLEAN và TRIM – loại bỏ ký tự không hiển thị.
- Sử dụng Power Query – giải pháp mạnh mẽ cho tập dữ liệu lớn.
- Mẫu thực tế – ví dụ thực tế trong quản lý khách hàng, bán hàng, và phân tích tài chính.
- Lưu ý khi sử dụng – hiệu năng, tính tương thích, và cách tránh lỗi.
- Kết luận – tổng hợp lại các phương pháp và đề xuất cách lựa chọn tối ưu.
Mục tiêu cuối cùng là giúp bạn không chỉ biết cách bỏ khoảng trắng trong Excel, mà còn hiểu tại sao, khi nào nên áp dụng từng phương pháp, và làm sao để tích hợp chúng vào quy trình làm việc hàng ngày một cách hiệu quả.
Có thể bạn quan tâm: Bẻ Khóa Mật Khẩu Excel: Hướng Dẫn Chi Tiết, Pháp Lý Và Các Công Cụ Hiệu Quả
1. Hiểu Rõ Các Loại Khoảng Trắng Trong Excel
Trước khi đưa ra giải pháp, chúng ta cần phân loại các loại khoảng trắng mà người dùng thường gặp:
| Loại khoảng trắng | Mô tả | Ví dụ |
|---|---|---|
| Leading spaces (khoảng trắng đầu) | Khoảng trắng nằm ở đầu chuỗi, trước ký tự đầu tiên. | " Nguyen" |
| Trailing spaces (khoảng trắng cuối) | Khoảng trắng nằm ở cuối chuỗi, sau ký tự cuối cùng. | "Tran " |
| Multiple internal spaces (khoảng trắng thừa giữa) | Nhiều dấu cách liên tiếp giữa các từ. | "Le Van Phuc" |
| Non‑breaking space (NBSP) | Ký tự Unicode U+00A0, thường xuất hiện khi copy‑paste từ web. | "John Doe" (dấu cách là NBSP) |
| Tab, line break | Ký tự tab (CHAR(9)) hoặc xuống dòng (CHAR(10), CHAR(13)). | "ABC\tDEF" hoặc "Hello\r\nWorld" |
| Các ký tự đặc biệt không hiển thị | Như CHAR(0), CHAR(255) thường xuất hiện trong dữ liệu nhập từ hệ thống cũ. | "" (rỗng nhưng có ký tự không hiển thị) |
Việc xác định loại khoảng trắng là bước đầu tiên để chọn hàm hoặc công cụ phù hợp. Ví dụ, hàm TRIM (hoặc LỌC.KHOẢNG.TRẮNG) chỉ loại bỏ leading và trailing spaces và giảm nhiều khoảng trắng liên tiếp thành một – nhưng nó không loại bỏ NBSP hay tab. Đối với các ký tự này, chúng ta cần dùng SUBSTITUTE hoặc CLEAN.
Có thể bạn quan tâm: Bật Gợi Ý Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
2. Hàm TRIM (LỌC.KHOẢNG.TRẮNG) – Giải Pháp Cơ Bản Nhất
2.1. Cú pháp và cách hoạt động
Trong Excel tiếng Anh, hàm này được gọi là TRIM. Trong phiên bản tiếng Việt, hàm tương đương là LỌC.KHOẢNG.TRẮNG. Cú pháp:
TRIM(text)
hoặc
LỌC.KHOẢNG.TRẮNG(văn_bản)
Tham số:
text/văn_bản: chuỗi ký tự cần xử lý.
Kết quả:
- Loại bỏ tất cả các khoảng trắng ở đầu và cuối chuỗi.
- Giảm tất cả các khoảng trắng liên tiếp ở giữa thành một khoảng trắng.
- Không ảnh hưởng đến các ký tự không phải là khoảng trắng (tab, NBSP, ký tự điều khiển).
2.2. Ví dụ thực tế
| Ô A | Công thức | Kết quả |
|---|---|---|
Hà Nội | =TRIM(A1) | Hà Nội |
Sa Pa Pa | =TRIM(A2) | Sa Pa Pa |
John Doe (NBSP) | =TRIM(A3) | John Doe (không thay đổi) |
2.3. Ưu điểm và hạn chế
| Ưu điểm | Hạn chế |
|---|---|
| Đơn giản, nhanh, không cần kết hợp hàm khác. | Không loại bỏ NBSP, tab, ký tự không hiển thị. |
| Tự động giảm nhiều khoảng trắng thành một. | Đối với dữ liệu lớn, việc lặp lại hàm trên nhiều ô có thể làm chậm. |
| Tích hợp sẵn trong mọi phiên bản Excel. | Không hỗ trợ trực tiếp trong Power Query (cần dùng M code). |
3. Xử Lý Khoảng Trắng Nội Bộ – Khi TRIM Không Đủ
3.1. Loại bỏ toàn bộ khoảng trắng (bao gồm nội bộ)
Nếu muốn xóa hết mọi khoảng trắng, kể cả giữa các từ, ta dùng:
=SUBSTITUTE(text, " ", "")
hoặc trong tiếng Việt:
=THAY.THẾ(text; " "; "")
Ví dụ:
| Ô A | Công thức | Kết quả |
|---|---|---|
Hà Nội | =SUBSTITUTE(A1;" ";"") | HàNội |
3.2. Giảm nhiều khoảng trắng nội bộ thành một (cải tiến TRIM)
Trong một số trường hợp, TRIM sẽ làm đúng nhưng nếu dữ liệu chứa NBSP hoặc tab, chúng ta cần một công thức phức tạp hơn:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," "),CHAR(9)," "))
Giải thích:
CHAR(160)là NBSP, thay bằng khoảng trắng thường.CHAR(9)là tab, cũng thay bằng khoảng trắng.- Cuối cùng,
TRIMsẽ loại bỏ leading/trailing và giảm các khoảng trắng liên tiếp.
3.3. Công thức tổng hợp cho mọi loại khoảng trắng
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," "),CHAR(9)," "),CHAR(10)," ")))
CLEANloại bỏ các ký tự điều khiển (ASCII < 32) ngoại trừ tab và line feed.SUBSTITUTE(...,CHAR(10)," ")thay newline bằng khoảng trắng.TRIMcuối cùng để làm sạch.
Lưu ý: Công thức này có thể trở nên dài và khó bảo trì. Khi làm việc với nhiều loại ký tự, việc chuyển sang Power Query sẽ gọn gàng hơn.
4. Kết Hợp CLEAN Với TRIM – Loại Bỏ Ký Tự Không Hiển Thị
CLEAN (tiếng Việt: DỌC) loại bỏ các ký tự không hiển thị trong dải mã ASCII từ 0 tới 31 (trừ CHAR(9) và CHAR(10)). Đây là công cụ quan trọng khi dữ liệu được import từ các hệ thống cũ, file txt hoặc sao chép từ web.
4.1. Cú pháp
CLEAN(text)
hoặc
DỌC(văn_bản)
4.2. Ví dụ
Có thể bạn quan tâm: Gõ Chữ Trong Excel Bị Lỗi: Nguyên Nhân, Hậu Quả Và Cách Khắc Phục Hiệu Quả
Giả sử ô A1 chứa "ABC"&CHAR(0)&CHAR(1)&"DEF" (có ký tự NULL và SOH). Công thức:
=TRIM(CLEAN(A1))
Kết quả sẽ là "ABCDEF".
4.3. Khi nào nên dùng CLEAN?
- Khi dữ liệu được import từ file .txt, .csv có ký tự điều khiển.
- Khi sao chép dữ liệu từ các hệ thống ERP, CRM cũ.
- Khi kiểm tra dữ liệu: nếu hàm
LEN(A1)trả về giá trị lớn hơn số ký tự nhìn thấy, có khả năng có ký tự không hiển thị.
5. Power Query – Giải Pháp Mạnh Mẽ Cho Dữ Liệu Lớn
Khi xử lý hàng nghìn, hàng triệu bản ghi, dùng công thức ô riêng lẻ sẽ làm chậm workbook. Power Query (hoặc Get & Transform) cung cấp một môi trường ETL (Extract‑Transform‑Load) mạnh mẽ, cho phép:
- Xử lý toàn bộ cột một lần.
- Ghi lại các bước trong M code để tái sử dụng.
- Tự động cập nhật khi dữ liệu nguồn thay đổi.
5.1. Các bước cơ bản trong Power Query
- Load dữ liệu: Chọn
Data → From Table/Range(hoặcFrom Text/CSV). - Chọn cột cần làm sạch.
- Transform → Format → Trim: Thực hiện hành động tương tự hàm
TRIM. - Transform → Replace Values: Thay NBSP (
Alt+0160) bằng khoảng trắng. - Add Column → Custom Column: Sử dụng công thức M để loại bỏ các ký tự đặc biệt.
5.2. M code mẫu
let Source = Excel.CurrentWorkbook(){Name="Table1"}Content, #"Trimmed Text" = Table.TransformColumns(Source, {{"Tên", Text.Trim, type text}}), #"Replaced NBSP" = Table.TransformColumns(#"Trimmed Text", {{"Tên", each Text.Replace(_, Character.FromNumber(160), " "), type text}}), #"Removed Non‑Printing" = Table.TransformColumns(#"Replaced NBSP", {{"Tên", each Text.Clean(_), type text}})
in #"Removed Non‑Printing"
Giải thích:
Text.Trimtương đươngTRIM.Character.FromNumber(160)tạo NBSP, được thay bằng khoảng trắng.Text.Cleantương đươngCLEAN.
5.3. Lợi ích
| Lợi ích | Chi tiết |
|---|---|
| Hiệu suất | Xử lý trên engine Power Query, nhanh hơn công thức ô. |
| Tái sử dụng | Các bước được lưu lại, dễ dàng áp dụng cho các file mới. |
| Tích hợp | Có thể xuất dữ liệu đã làm sạch trở lại Excel hoặc vào Power BI. |
| Không làm hỏng dữ liệu gốc | Dữ liệu gốc vẫn giữ nguyên trong sheet ban đầu. |
6. Các Ứng Dụng Thực Tiễn
6.1. Quản lý khách hàng (CRM)
- Vấn đề: Khi nhập tên khách hàng từ form web, thường có khoảng trắng thừa hoặc NBSP.
- Giải pháp: Sử dụng
TRIM+SUBSTITUTEđể chuẩn hoá cộtTên Khách Hàng. Sau khi làm sạch, có thể dùngVLOOKUPhoặcXLOOKUPđể ghép dữ liệu chính xác.
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
6.2. Báo cáo bán hàng
- Vấn đề: Mã sản phẩm (SKU) chứa tab hoặc khoảng trắng cuối, gây lỗi khi nối với bảng giá.
- Giải pháp: Dùng Power Query để
TrimvàReplacetab (CHAR(9)) bằng chuỗi rỗng.
#"Cleaned SKU" = Table.TransformColumns(Source, {{"SKU", each Text.Trim(Text.Replace(_, Character.FromNumber(9), "")), type text}})
6.3. Phân tích tài chính
- Vấn đề: Khi import dữ liệu ngân hàng, có các ký tự line break (
CHAR(10)) trong mô tả giao dịch. - Giải pháp: Dùng
CLEANkết hợpTRIMđể loại bỏ và chuẩn hoá mô tả.
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(10)," ")))
7. Lưu Ý Khi Áp Dụng Các Phương Pháp
- Hiệu năng: Tránh lặp lại công thức phức tạp trên hàng trăm ngàn. Khi dữ liệu lớn, nên chuyển sang Power Query hoặc VBA.
- Ngôn ngữ Excel: Tên hàm thay đổi tùy phiên bản (TRIM ↔ LỌC.KHOẢNG.TRẮNG, SUBSTITUTE ↔ THAY.THẾ, CLEAN ↔ DỌC). Cần chú ý khi chia sẻ file với người dùng khác.
- Ký tự Unicode: Excel 365 hỗ trợ Unicode tốt hơn. Nếu dữ liệu chứa ký tự không phải ASCII, có thể cần hàm
UNICODEvàUNICHARđể kiểm tra. - Bảo toàn dữ liệu gốc: Luôn làm việc trên bản sao hoặc tạo cột phụ để không mất dữ liệu gốc, nhất là khi sử dụng
SUBSTITUTEđể xóa khoảng trắng nội bộ. - Kiểm tra kết quả: Dùng hàm
LENđể so sánh độ dài trước và sau khi làm sạch, hoặcCOUNTIFđể tìm các giá trị vẫn còn chứa khoảng trắng (" ").
8. Tổng Kết
Việc bỏ khoảng trắng trong Excel không chỉ là một thao tác “đánh dấu” mà là một phần thiết yếu của quá trình làm sạch dữ liệu, giúp:
- Đảm bảo tính chính xác của công thức, tra cứu và báo cáo.
- Tiết kiệm thời gian khi không phải xử lý lỗi do dữ liệu không sạch.
- Nâng cao chất lượng dữ liệu cho các công cụ phân tích như Power BI, Tableau hoặc các mô hình Machine Learning.
8.1. Khi nào dùng hàm TRIM (LỌC.KHOẢNG.TRẮNG)?
- Khi dữ liệu chỉ có khoảng trắng thông thường (ASCII 32) ở đầu, cuối hoặc nhiều khoảng trắng liên tiếp.
- Khi muốn giảm nhanh trong một cột nhỏ hoặc trung bình (under 10.000 hàng).
8.2. Khi nào dùng kết hợp SUBSTITUTE, CLEAN, và các hàm khác?
- Khi dữ liệu chứa NBSP, tab, newline hoặc các ký tự không hiển thị.
- Khi cần xóa hoàn toàn khoảng trắng nội bộ hoặc thay thế chúng bằng ký tự khác.
8.3. Khi nào nên chuyển sang Power Query?
- Khi dữ liệu lớn hơn 50.000 hàng và công thức ô làm chậm workbook.
- Khi muốn tự động hoá quy trình làm sạch và tái sử dụng cho các file mới.
- Khi cần kết hợp nhiều bước làm sạch (trim, replace, clean) trong một quy trình ETL.
8.4. Các công cụ bổ trợ
| Công cụ | Mô tả | Khi dùng |
|---|---|---|
| VBA Macro | Tự động áp dụng hàm trên toàn cột, có thể lưu thành nút. | Khi muốn một thao tác “click‑once” cho người không quen công thức. |
| Power Automate Desktop | Tự động hoá quy trình nhập dữ liệu từ các nguồn khác nhau. | Khi dữ liệu đến từ nhiều hệ thống và cần chuẩn hoá trước khi nhập Excel. |
| Add‑in “Kutools for Excel” | Cung cấp nút “Trim Spaces” nhanh. | Khi không muốn viết công thức hoặc Power Query. |
9. Kết Luận
Khả năng bỏ khoảng trắng trong Excel không chỉ dựa vào một hàm duy nhất mà là một bộ công cụ đa dạng, từ TRIM đơn giản, đến các hàm SUBSTITUTE, CLEAN, và cuối cùng là Power Query mạnh mẽ. Việc lựa chọn phương pháp phù hợp phụ thuộc vào:
- Loại khoảng trắng và ký tự không mong muốn trong dữ liệu.
- Khối lượng dữ liệu cần xử lý.
- Môi trường làm việc (Excel Desktop, Excel Online, Power BI).
Bằng cách nắm vững các hàm và kỹ thuật trên, bạn sẽ:
- Giảm thiểu lỗi dữ liệu trong các công thức và báo cáo.
- Tăng tốc độ xử lý và cải thiện hiệu năng workbook.
- Đảm bảo dữ liệu sạch, chuẩn hoá, sẵn sàng cho các bước phân tích tiếp theo.
Hy vọng bài viết đã cung cấp cho bạn một cẩm nang toàn diện về hàm bỏ khoảng trắng trong Excel, giúp bạn tự tin xử lý mọi tình huống dữ liệu thực tế. Chúc bạn làm việc hiệu quả và luôn có dữ liệu sạch, chuẩn!
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ẻ.