Trong công việc xử lý dữ liệu, ta thường gặp tình huống cần loại bỏ khoảng trắng (spacer) thừa xuất hiện ngay sau chữ hoặc số trong ô Excel. Dù lúc nào cũng có vẻ nhỏ nhưng khoảng trắng không hợp lý sẽ gây lỗi khi so sánh, tổng hợp, hoặc khi nhập dữ liệu vào hệ thống khác. Bài viết dưới đây sẽ hướng dẫn chi tiết từng phương pháp, công thức và công cụ trong Excel giúp bạn loại bỏ khoảng trắng một cách nhanh chóng và chính xác nhất.
Có thể bạn quan tâm: How To Print Multiple Excel Files At Once: A Comprehensive Guide
1. Hiểu vấn đề: Khoảng trắng “trong” và “đằng sau” văn bản
Trước khi bắt đầu, ta cần phân biệt hai loại khoảng trắng thường gặp:
| Loại | Mô tả | Ví dụ |
|---|---|---|
| Khoảng trắng giữa từ | Các khoảng trắng nằm giữa hai từ, thường được giữ nguyên. | “Hello World” |
| Khoảng trắng thừa ở đầu/đằng sau | Khoảng trắng dư thừa ngay trước hoặc sau nội dung chính, thường là nguyên nhân gây lỗi. | “Hello World ” (khoảng trắng cuối), “ Hello” (khoảng trắng đầu) |
| Khoảng trắng thừa giữa các trường dữ liệu | Khi dữ liệu được nhập từ nguồn khác (ví dụ CSV), khoảng trắng có thể xuất hiện ngay sau dấu phân cách. | “John Doe, 30, New York” (khoảng trắng sau dấu phẩy) |
Trong bài viết này, chúng ta tập trung vào khoảng trắng thừa ở cuối chuỗi (hoặc “after text”), nhưng nhiều phương pháp cũng áp dụng cho khoảng trắng đầu hoặc giữa chuỗi.
Có thể bạn quan tâm: Cách Mở File Excel Csv (comma Separated Values) – Hướng Dẫn Chi Tiết
2. Các công cụ trong Excel để loại bỏ khoảng trắng
2.1 Hàm TRIM (TRỌNG)
Cú pháp:
=TRIM(text)
Hàm TRIM (TRỌNG) trong Excel loại bỏ tất cả khoảng trắng thừa, bao gồm:
– Khoảng trắng đầu và cuối chuỗi.
– Khoảng trắng nhiều hơn một ký tự trong chuỗi, được thay bằng một ký tự khoảng trắng duy nhất.
Ưu điểm: Đơn giản, nhanh chóng, không cần công thức phức tạp.
Hạn chế: Loại bỏ cả khoảng trắng giữa từ, có thể không phù hợp nếu bạn muốn giữ nguyên dải khoảng trắng giữa các từ.
Ví dụ
| Ô | Giá trị | Công thức | Kết quả |
|---|---|---|---|
| A1 | “ Hello World ” | =TRIM(A1) | “Hello World” |
| A2 | “John Doe ” | =TRIM(A2) | “John Doe” |
2.2 Hàm SUBSTITUTE (THAY THẾ)
Cú pháp:
=SUBSTITUTE(text, old_text, new_text, instance_num)
Hàm SUBSTITUTE giúp thay thế bất kỳ ký tự nào trong chuỗi. Khi kết hợp với TRIM, ta có thể loại bỏ khoảng trắng thừa mà vẫn giữ nguyên khoảng trắng giữa từ.
1. Loại bỏ khoảng trắng cuối chuỗi
Nếu bạn biết rằng khoảng trắng chỉ xuất hiện ở cuối chuỗi, bạn có thể dùng công thức:
=IF(RIGHT(A1,1)=" ", LEFT(A1,LEN(A1)-1), A1)
RIGHT(A1,1)kiểm tra ký tự cuối.LEFT(A1,LEN(A1)-1)cắt bỏ ký tự cuối nếu là khoảng trắng.
2. Loại bỏ khoảng trắng đầu
=IF(LEFT(A1,1)=" ", RIGHT(A1,LEN(A1)-1), A1)
3. Loại bỏ cả đầu và cuối
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),CHAR(9),""))
Trong đó, CHAR(160) là ký tự không thể vỡ dòng (non-breaking space) và CHAR(9) là tab. Hàm SUBSTITUTE loại bỏ những ký tự này trước khi dùng TRIM.
2.3 Hàm CLEAN (SẠCH)
Cú pháp:
=CLEAN(text)
Hàm CLEAN loại bỏ các ký tự bất thường (non-printable) trong chuỗi, bao gồm khoảng trắng thừa ở đầu và cuối. Tuy nhiên, nó không thay thế khoảng trắng giữa từ.
Ví dụ
| Ô | Giá trị | Công thức | Kết quả |
|---|---|---|---|
| A1 | “Hello World” (khoảng trắng không thể vỡ dòng) | =CLEAN(A1) | “HelloWorld” |
2.4 Công cụ “Find & Replace” (Tìm & Thay thế)
Nếu bạn muốn loại bỏ khoảng trắng thừa trong toàn bộ bảng dữ liệu:
- Chọn vùng dữ liệu cần xử lý.
- Nhấn
Ctrl + Hđể mở hộp thoại “Find & Replace”. - Trong ô “Find what”, nhập một khoảng trắng (
). - Để trống ô “Replace with”.
- Nhấn “Replace All”.
Lưu ý: Phương pháp này sẽ loại bỏ tất cả khoảng trắng, kể cả những khoảng trắng cần giữ. Do đó, thường kết hợp với hàm
TRIMhoặcSUBSTITUTE.
Có thể bạn quan tâm: Cách Merge (gộp) Các Ô Trong Bảng Excel: Hướng Dẫn Chi Tiết Và Bất Kỳ Thử Thách
3. Kết hợp hàm trong các trường hợp phức tạp
3.1 Loại bỏ khoảng trắng thừa sau dấu phân cách
Giả sử dữ liệu bạn nhập từ CSV có dạng: “John, 30, New York”. Bạn muốn loại bỏ khoảng trắng sau dấu phẩy.
=TRIM(SUBSTITUTE(A1, ", ", ","))
Ở đây, SUBSTITUTE thay thế “, ” thành “,” trước khi TRIM loại bỏ khoảng trắng thừa.
3.2 Loại bỏ khoảng trắng thừa trong chuỗi có ký tự đặc biệt
Có thể bạn quan tâm: How To Put An Excel Table Into Word
Khi dữ liệu chứa ký tự đặc biệt như tab, dấu xuống dòng, hoặc ký tự không thể vỡ dòng, bạn có thể kết hợp CLEAN và TRIM.
=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A1,CHAR(9),""),CHAR(160),"")))
SUBSTITUTE(A1,CHAR(9),"")loại bỏ tab.SUBSTITUTE(...,CHAR(160),"")loại bỏ non-breaking space.CLEAN(...)loại bỏ các ký tự không in được.TRIM(...)cuối cùng loại bỏ khoảng trắng đầu/đầu.
3.3 Xử lý dữ liệu từ nhiều cột
Nếu bạn có một bảng dữ liệu gồm nhiều cột cần loại bỏ khoảng trắng, bạn có thể viết công thức đa cột:
=TRIM(SUBSTITUTE(A1,CHAR(160),"")) & " | " & TRIM(SUBSTITUTE(B1,CHAR(160),"")) & " | " & TRIM(SUBSTITUTE(C1,CHAR(160),""))
Sử dụng & để nối chuỗi và TRIM để làm sạch từng cột một.
4. Tự động hoá với VBA
Nếu bạn làm việc với dữ liệu lớn và cần tự động hoá quy trình, VBA (Visual Basic for Applications) là cách tuyệt vời.
Sub CleanSpaces() Dim rng As Range Set rng = Selection Dim cell As Range For Each cell In rng If Not IsEmpty(cell) Then cell.Value = Trim(Replace(cell.Value, Chr(160), "")) End If Next cell
End Sub
Trimloại bỏ khoảng trắng đầu/đầu.Replace(cell.Value, Chr(160), "")loại bỏ ký tự non-breaking space.- Dùng
Selectionđể áp dụng cho vùng chọn hiện tại.
Cách sử dụng:
1. Nhấn Alt + F11 để mở Visual Basic Editor.
2. Chèn một module mới và dán đoạn mã trên.
3. Quay lại Excel, chọn vùng dữ liệu cần xử lý.
4. Chạy macro CleanSpaces (Alt + F8 -> chọn macro).
5. Thực hành: Dữ liệu mẫu và cách xử lý từng bước
5.1 Dữ liệu mẫu
| A | B | C |
|---|---|---|
| “ Alice ” | “ 25 ” | “ New York ” |
| “Bob ” | “30 ” | “ Los Angeles ” |
| “ Charlie” | “ 28” | “ Chicago ” |
5.2 Bước 1: Loại bỏ khoảng trắng đầu/đầu
Sử dụng công thức =TRIM(A1) trong cột D, kéo dãn xuống.
5.3 Bước 2: Loại bỏ khoảng trắng thừa giữa các trường
Nếu bạn muốn giữ khoảng trắng giữa từ nhưng loại bỏ thừa, dùng:
=SUBSTITUTE(TRIM(A1), " ", " ")
Điều này thay thế các khoảng trắng nhiều hơn một ký tự bằng một ký tự.
5.4 Bước 3: Kết hợp với dữ liệu từ nhiều cột
=TRIM(SUBSTITUTE(A1,CHAR(160),"")) & " | " & TRIM(SUBSTITUTE(B1,CHAR(160),"")) & " | " & TRIM(SUBSTITUTE(C1,CHAR(160),""))
Sau khi áp dụng, dữ liệu sẽ trở thành:
| D |
|---|
| “Alice |
| “Bob |
| “Charlie |
6. Kiểm tra kết quả và lưu trữ
Sau khi đã loại bỏ khoảng trắng, bạn nên kiểm tra một số ô mẫu để chắc chắn rằng không còn ký tự không mong muốn. Bạn cũng có thể sử dụng tính năng Conditional Formatting để highlight các ô có ký tự đặc biệt (như CHAR(160)).
Mẹo lưu trữ: Khi lưu file Excel chứa dữ liệu đã xử lý, hãy lưu dưới định dạng CSV nếu cần chuyển sang các hệ thống khác. CSV thường tự động loại bỏ khoảng trắng đầu/đầu khi mở.
7. Kết luận
Loại bỏ khoảng trắng thừa trong Excel là một kỹ năng quan trọng giúp dữ liệu của bạn trở nên chính xác và dễ dàng xử lý hơn. Tùy vào nhu cầu và độ phức tạp của dữ liệu, bạn có thể chọn:
- Hàm TRIM cho giải pháp nhanh và đơn giản.
- Hàm SUBSTITUTE khi cần kiểm soát chính xác vị trí và tính toàn vẹn của khoảng trắng.
- Hàm CLEAN để loại bỏ ký tự không in được.
- Find & Replace cho thao tác thủ công nhanh.
- VBA để tự động hoá quy trình trong dữ liệu lớn.
Hãy thử áp dụng các công thức và công cụ trên vào bảng tính của bạn và trải nghiệm sự khác biệt trong độ chính xác và tính nhất quán của dữ liệu. Happy Excel!
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ẻ.