Cách Xóa Khoảng Trống (blank Space) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

Có thể bạn quan tâm: Personal Finance Manager Excel Template: Hướng Dẫn Chi Tiết Từ A Đến Z Để Kiểm Soát Tài Chính Cá Nhân Hiệu Quả

Lời mở đầu

Trong công việc hàng ngày, chúng ta thường phải xử lý rất nhiều dữ liệu trong Excel. Một trong những vấn đề phổ biến và gây phiền toái nhất là khoảng trống (blank space) – có thể xuất hiện ở đầu, cuối hoặc giữa các chuỗi ký tự. Những khoảng trống này không chỉ làm cho dữ liệu trông lộn xộn mà còn gây ra lỗi khi thực hiện các phép tính, lọc dữ liệu, so sánh, hoặc khi import/export sang các hệ thống khác.

Bài viết này sẽ cung cấp cho bạn một hướng dẫn toàn diện về cách loại bỏ khoảng trống trong Excel, bao gồm:

1. Giải thích các loại khoảng trống thường gặp.
2. Các hàm và công cụ cơ bản để xóa khoảng trống.
3. Các phương pháp nâng cao (Power Query, VBA, Macro, và các công cụ bên ngoài).
4. Những lưu ý quan trọng và mẹo tối ưu khi làm việc với dữ liệu lớn.
5. Các ví dụ thực tế, bài tập thực hành và câu hỏi thường gặp.

Với nội dung chi tiết này, dù bạn là người mới bắt đầu hay một chuyên gia dữ liệu, cũng sẽ tìm được giải pháp phù hợp để xử lý sạch sẽ và nhanh chóng các khoảng trống trong Excel.

Có thể bạn quan tâm: File Excel Nhập Xuất Tồn Đơn Giản: Hướng Dẫn Từ A Đến Z Để Quản Lý Kho Hiệu Quả

1. Hiểu rõ các loại khoảng trống trong Excel

1.1. Khoảng trống thông thường (Space – ASCII 32)

Đây là ký tự khoảng trống thông thường mà chúng ta nhập bằng phím Space. Ví dụ: "Hello World" có một khoảng trống giữa “Hello” và “World”.

1.2. Khoảng trống không hiển thị (Non‑breaking space – ASCII 160)

Đây là ký tự thường xuất hiện khi copy‑paste dữ liệu từ website, tài liệu PDF, hoặc các nguồn khác. Trên màn hình, nó trông giống như khoảng trống bình thường, nhưng Excel không nhận nó là ký tự Space. Khi dùng hàm TRIM (hoặc LỌCTRỪ trong tiếng Việt) sẽ không loại bỏ được ký tự này.

1.3. Khoảng trống ở đầu và cuối chuỗi (Leading/Trailing Spaces)

• Leading space: Khoảng trống đứng ở đầu chuỗi, ví dụ " Data".
• Trailing space: Khoảng trống đứng ở cuối chuỗi, ví dụ "Data ".

1.4. Khoảng trống lặp lại (Multiple Spaces)

Khi một chuỗi có nhiều khoảng trống liên tiếp, ví dụ "Data Analysis" – chỉ một khoảng trống cần, các khoảng trống thừa sẽ gây lỗi khi so sánh hoặc gộp dữ liệu.

1.5. Dấu tab và ký tự điều khiển (Tab, Newline)

• Tab (CHAR(9)) thường xuất hiện trong dữ liệu xuất từ các file TSV.
• Newline (CHAR(10) cho line feed, CHAR(13) cho carriage return) thường xuất hiện khi copy‑paste từ Notepad hoặc các nguồn có định dạng đa dòng.

Kết luận: Để xóa sạch khoảng trống trong Excel, chúng ta phải biết được loại ký tự nào đang tồn tại trong dữ liệu.

Có thể bạn quan tâm: Tạo Form Nhập Liệu Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Các công cụ và hàm cơ bản trong Excel để loại bỏ khoảng trống

2.1. Hàm TRIM (hoặc LỌCTRỪ)

=TRIM(A1)

• Loại bỏ leading và trailing spaces.
• Giảm multiple spaces thành một khoảng trống duy nhất.
• Không loại bỏ ký tự non‑breaking space (ASCII 160) và các ký tự điều khiển.

Lưu ý: Khi dữ liệu chứa ký tự non‑breaking space, TRIM sẽ không hoạt động đầy đủ. Cần dùng SUBSTITUTE kết hợp.

2.2. Hàm SUBSTITUTE

=SUBSTITUTE(A1, " ", "")

• Loại bỏ tất cả ký tự space (ASCII 32) trong chuỗi.
• Để loại bỏ non‑breaking space, dùng mã ký tự CHAR(160):

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

2.2.1. Kết hợp TRIM và SUBSTITUTE

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

• Đầu tiên thay thế non‑breaking space bằng space chuẩn.
• Sau đó dùng TRIM để loại bỏ leading/trailing và giảm multiple spaces.

2.3. Hàm CLEAN (hoặc LỌC)

=CLEAN(A1)

• Loại bỏ các ký tự không hiển thị (ASCII 0‑31), bao gồm CHAR(10) (line feed) và CHAR(13) (carriage return).
• Không loại bỏ khoảng trống thông thường.

2.4. Kết hợp TRIM, CLEAN, và SUBSTITUTE

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

• Đây là công thức toàn diện nhất cho hầu hết các trường hợp:
• Thay thế non‑breaking space.
• Loại bỏ ký tự điều khiển.
• Xóa leading/trailing spaces và giảm multiple spaces.

2.5. Sử dụng công cụ Find & Replace (Ctrl + H)

1. Mở Find & Replace (Ctrl + H).
2. Tìm: Nhấn Alt+0160 trên bàn phím số để nhập ký tự non‑breaking space (hoặc copy‑paste trực tiếp từ một ô chứa ký tự này).
3. Thay thế bằng: Để trống (hoặc một space nếu muốn giảm multiple spaces).

4. Click “Replace All”.

5. Đối với tab: Nhập Ctrl+Tab trong ô “Find” (hoặc dùng CHAR(9) trong công thức).

6. Đối với newline: Nhập Ctrl+J trong ô “Find”.

Mẹo: Đánh dấu “Match entire cell contents” nếu muốn xóa toàn bộ nội dung rỗng (blank cells) thay vì chỉ xóa ký tự trong ô.

2.6. Sử dụng Text to Columns (Dữ liệu > Text to Columns)

Nếu dữ liệu có khoảng trống ở đầu hoặc cuối và bạn muốn nhanh chóng xóa chúng:

1. Chọn cột cần xử lý.
2. Vào Data > Text to Columns.
3. Chọn Delimited, click Next.
4. Bỏ chọn tất cả các delimiter, click Next.
5. Trong mục Column data format, chọn General.
6. Check “Treat consecutive delimiters as one” (nếu muốn giảm multiple spaces).
7. Click Finish.

Kết quả: Excel sẽ tự động cắt bỏ leading/trailing spaces và giảm multiple spaces.

3. Phương pháp nâng cao: Power Query, VBA và Macro

3.1. Power Query (Get & Transform)

Power Query là công cụ mạnh mẽ để làm sạch dữ liệu trước khi đưa vào bảng tính. Dưới đây là các bước chi tiết:

Có thể bạn quan tâm: Phần Mềm Chuyển Đổi Pdf Sang Excel: Giải Pháp Hoàn Hảo Cho Công Việc Xử Lý Dữ Liệu

Bước 1: Load dữ liệu vào Power Query

• Chọn bảng dữ liệu → Data > From Table/Range (hoặc Get Data > From Table/Range).

Bước 2: Sử dụng chức năng Trim và Clean

• Trong Power Query Editor, chọn cột cần làm sạch.
• Vào Transform > Format > Trim (loại bỏ leading/trailing spaces và giảm multiple spaces).
• Vào Transform > Format > Clean (loại bỏ ký tự điều khiển).

Bước 3: Thay thế ký tự non‑breaking space

• Chọn cột → Transform > Replace Values.
• Value to Find: Nhập Alt+0160 hoặc copy‑paste ký tự non‑breaking space.
• Replace With: Nhập một space chuẩn hoặc để trống.
• Click OK.

Bước 4: Áp dụng thay đổi và tải dữ liệu về Excel

• Click Home > Close & Load.

Ưu điểm: Power Query có thể xử lý hàng triệu dòng dữ liệu mà không làm chậm Excel, và các bước xử lý được ghi nhớ (recorded) để tái sử dụng khi dữ liệu thay đổi.

3.2. VBA (Visual Basic for Applications)

Khi cần xử lý hàng loạt nhiều cột, hoặc muốn tạo một nút bấm để thực hiện nhanh, VBA là lựa chọn lý tưởng.

3.2.1. Mã VBA cơ bản để xóa khoảng trống

Sub RemoveSpaces() Dim rng As Range Dim cell As Range ' Đặt phạm vi cần xử lý (thay A1:A1000 bằng phạm vi thực tế) Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A1000") For Each cell In rng If Not IsEmpty(cell) Then ' Thay thế non‑breaking space bằng space chuẩn cell.Value = Replace(cell.Value, Chr(160), " ") ' Xóa ký tự điều khiển cell.Value = Application.WorksheetFunction.Clean(cell.Value) ' Trim và giảm multiple spaces cell.Value = Application.WorksheetFunction.Trim(cell.Value) End If Next cell
End Sub

• Giải thích:
• Replace(cell.Value, Chr(160), " ") thay thế non‑breaking space.
• Clean loại bỏ ký tự không hiển thị.
• Trim xóa leading/trailing và giảm multiple spaces.

3.2.2. Hàm VBA tùy chỉnh cho toàn bộ workbook

Bạn có thể tạo một hàm UDF (User Defined Function) để dùng trực tiếp trong ô:

Function CleanSpaces(txt As String) As String Dim s As String s = Replace(txt, Chr(160), " ") ' Non‑breaking space → space s = Application.WorksheetFunction.Clean(s) ' Remove control chars s = Application.WorksheetFunction.Trim(s) ' Trim + reduce spaces CleanSpaces = s
End Function

• Sử dụng: =CleanSpaces(A1)

3.2.3. Tự động chạy khi dữ liệu thay đổi (Event Macro)

Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ExitSub Application.EnableEvents = False Dim rng As Range Set rng = Intersect(Target, Me.Range("A:C")) ' Giới hạn cột A‑C If Not rng Is Nothing Then Dim cell As Range For Each cell In rng If Not IsEmpty(cell) Then cell.Value = CleanSpaces(cell.Value) ' Gọi hàm đã tạo End If Next cell End If ExitSub: Application.EnableEvents = True
End Sub

• Khi người dùng nhập hoặc dán dữ liệu vào cột A‑C, macro sẽ tự động làm sạch khoảng trống ngay lập tức.

3.3. Sử dụng Add‑in hoặc công cụ bên ngoài

• Kutools for Excel: Bổ trợ rất nhiều tính năng “Remove Blank” và “Trim Spaces”.
• Ablebits Duplicate Remover: Ngoài việc loại bỏ trùng lặp, công cụ này còn có tính năng “Trim spaces”.
• Power Tools (Google Sheets) – Nếu bạn làm việc trên Google Sheets, các add‑on tương tự cũng hỗ trợ.

4. Khi nào nên dùng hàm, khi nào nên dùng Power Query hoặc VBA?

5. Các ví dụ thực tế

5.1. Ví dụ 1: Dọn dẹp danh sách khách hàng

Bối cảnh: Bạn nhận danh sách khách hàng từ một file CSV do bộ phận marketing cung cấp. Dữ liệu có:

• Khoảng trống đầu/cuối.
• Non‑breaking space giữa họ và tên.
• Dấu tab sau một số trường.

Giải quyết bằng Power Query:

1. Import file CSV → Data > From Text/CSV.
2. Trong Power Query, chọn cột Tên khách hàng.
3. Transform > Replace Values → Find: Alt+0160, Replace: " " (space).
4. Transform > Trim và Transform > Clean.
5. Replace Values → Find: CHAR(9), Replace: "".
6. Load về Excel.

Kết quả: Tên khách hàng sạch sẽ, không còn khoảng trống thừa, sẵn sàng cho việc merge với hệ thống CRM.

5.2. Ví dụ 2: Xử lý dữ liệu tài chính trong VBA

Bạn có file báo cáo tài chính hàng tháng, các cột số liệu (cột B, C, D) thường được sao chép từ một hệ thống ERP và chứa non‑breaking space và newline. Khi dùng SUM sẽ nhận lỗi #VALUE!.

Macro VBA:

Sub CleanFinancialData() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Report") Dim rng As Range Set rng = ws.Range("B2:D" & ws.Cells(ws.Rows.Count, "B").End(xlUp).Row) Dim cell As Range For Each cell In rng If Not IsEmpty(cell) Then cell.Value = Replace(cell.Value, Chr(160), "") ' Xóa non‑breaking space cell.Value = Replace(cell.Value, Chr(10), "") ' Xóa line feed cell.Value = Replace(cell.Value, Chr(13), "") ' Xóa carriage return cell.Value = Application.WorksheetFunction.Trim(cell.Value) End If Next cell
End Sub

Sau khi chạy macro, các công thức SUM hoạt động bình thường.

5.3. Ví dụ 3: Dùng hàm tùy chỉnh trong Excel

Bạn muốn cho phép người dùng nhập dữ liệu vào một ô và tự động “làm sạch” ngay trong ô đó mà không cần copy sang ô khác.

Cách thực hiện:

1. Thêm hàm CleanSpaces (như phần 3.2.2) vào module VBA.
2. Trong ô B2 nhập công thức: =CleanSpaces(A2).
3. Khi người dùng nhập vào A2, B2 sẽ hiển thị dữ liệu đã được dọn dẹp.

6. Mẹo tối ưu khi làm việc với dữ liệu lớn

1. Tránh sử dụng công thức mảng (Array Formula) trên toàn cột nếu dữ liệu lớn; thay vào đó, dùng Power Query hoặc VBA để xử lý một lần và ghi kết quả.
2. Sử dụng Ctrl + End để kiểm tra xem có ô nào chứa ký tự ẩn (non‑breaking space) mà Excel không hiển thị.
3. Kiểm tra độ dài chuỗi với LEN và LEN(TRIM(...)) để xác định có khoảng trống thừa không:
   excel =LEN(A1)-LEN(TRIM(A1))
   Nếu kết quả > 0 → có khoảng trống đầu/cuối hoặc multiple spaces.
4. Sao lưu dữ liệu trước khi chạy macro hoặc Power Query, đặc biệt khi thực hiện “Replace All” trên toàn workbook.
5. Sử dụng tính năng “Data Validation” để ngăn người dùng nhập ký tự không mong muốn (ví dụ: không cho phép space đầu).

7. Các câu hỏi thường gặp (FAQ)

Q1: TRIM không loại bỏ khoảng trống trong ô, tại sao?

• Có thể dữ liệu chứa non‑breaking space (CHAR(160)) hoặc tab (CHAR(9)). Hãy dùng SUBSTITUTE để thay thế trước khi dùng TRIM.

Q2: Làm sao để tìm nhanh các ô chứa ký tự non‑breaking space?

• Dùng công cụ Find (Ctrl + F), nhập Alt+0160. Hoặc trong công thức:
  excel =IF(ISNUMBER(FIND(CHAR(160),A1)), "Có NBSP", "Không")

Q3: Khi dùng Power Query, tôi có thể tự động cập nhật khi file nguồn thay đổi không?

• Có. Khi bạn thiết lập query, chỉ cần Refresh (Data > Refresh All) và Power Query sẽ lấy lại dữ liệu mới, áp dụng các bước làm sạch đã lưu.

Q4: Có cách nào để xóa khoảng trống trong định dạng số (Number Format) không?

• Định dạng số không lưu trữ khoảng trống. Nếu bạn thấy khoảng trống khi hiển thị, có thể là ký tự chuỗi trong ô. Hãy chuyển sang Number bằng Data > Text to Columns > Finish hoặc dùng VALUE.

Q5: CLEAN không xóa dấu ¶ (paragraph symbol) trong Excel, làm sao?

• Dấu ¶ là ký tự CHAR(182). Bạn có thể dùng:
  excel =SUBSTITUTE(A1, CHAR(182), "")

8. Tổng kết

Việc loại bỏ khoảng trống trong Excel không chỉ là một thao tác thẩm mỹ mà còn là bước quan trọng để đảm bảo dữ liệu sạch, chuẩn xác và sẵn sàng cho các phân tích, báo cáo và tích hợp hệ thống. Bài viết đã cung cấp:

• Hiểu biết về các loại khoảng trống.
• Công cụ và hàm cơ bản (TRIM, SUBSTITUTE, CLEAN).
• Phương pháp nâng cao bằng Power Query, VBA, và Add‑ins.
• Kịch bản thực tế và mẹo tối ưu cho dữ liệu lớn.
• FAQ giải đáp những thắc mắc phổ biến.

Hãy áp dụng những kiến thức này vào công việc hàng ngày của bạn. Khi dữ liệu luôn sạch sẽ, bạn sẽ tiết kiệm thời gian, giảm lỗi và nâng cao hiệu quả công việc. Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

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ẻ.