Trong công việc xử lý dữ liệu thường xuyên, chúng ta gặp phải nhiều trường hợp dữ liệu mang tới những ký tự không mong muốn – những ký tự đặc biệt, ký tự kiểm soát, dấu cách thừa, ký tự Unicode không chuẩnPUR, ký tự phụ thuộc vào ngôn ngữ, hay những ký tự được chèn vô tình qua quá trình sao chép‑dán từ các nguồn khác nhau. Nếu không loại bỏ kịp thời, những ký tự này có thể gây ra lỗi tính toán, làm rối dữ liệu khi phân tích, hoặc thậm chí làm hỏng quy trình tự động hoá khi chạy macro hay Power Query.

Bài viết dưới đây sẽ hướng dẫn chi tiết cách loại bỏ ký tự đặc biệt trong Excel bằng nhiều phương pháp khác nhau, từ công thức đơn giản, công cụ “Replace”, Power Query, VBA cho tới giải pháp sử dụng regex. Bạn sẽ szybko học được cách chọn phương pháp phù hợp nhất với quy mô và tính chất dữ liệu mà mình đang làm việc.

Có thể bạn quan tâm: Liên Kết Dữ Liệu Từ Nhiều File Excel: Hướng Dẫn Chi Tiết Và Các Ứng Dụng Thực Tế

Mục lục bài viết

1. Hiểu rõ ký tự đặc biệt là gì?

Ký tự đặc biệt (special characters) trong Excel thường được phân loại như sau:

Loại ký tự	Mô tả	Ví dụ	Ảnh hưởng khi không loại bỏ Motors
Ký tự kiểm soát	Ký tự không thể hiển thị, thường là ký tự ASCII từ 0–31	`Chr(13)` (Carriage return), `Chr(10)` (Line feed)	Gây lỗi trong công thức, làm dữ liệu split thành nhiều dòng
Ký tự không chuẩn Unicode	Ký tự mà Excel không thể giải mã đúng	`U+FFFD` (Replacement character)	Hiển thị “?” hoặc “”, gây nhầm lẫn
Ký tự không mong muốn	Dấu cách thừa, dấu gạch chéo, dấu chấm phẩy, ký tự đặc biệt trong chuỗi	`;`, `,`, `#`, `$`	Gây lỗi khi tính toán, lọc dữ liệu
Dấu gạch ngang, dấu gạch chéo ngược	Dùng trong các tên file, đường dẫn	`-`, `/`, `\`	Khi dùng trong công thức, có thể bị hiểu nhầm

Việc loại bỏ những ký tự này giúp:

Đảm bảo tính nhất quán của dữ liệu – các giá trị trong cột có cùng định dạng, không còn “lệch” do ký tự thừa.
Ngăn ngừa lỗi trong công thức – công thức SUM, VLOOKUP, IFERROR thường bị lỗi khi gặp ký tự kiểm soát.
Tăng tốc độ xử lý – Excel xử lý nhanh hơn khi dữ liệu sạch, không bị “xáo trộn” bởi ký tự không mong muốn.
Dễ dàng nhập dữ liệu mới – khi dữ liệu đã được chuẩn hoá, người dùng mới có thể copy‑paste mà không lo lắng về lỗi.

Có thể bạn quan tâm: Lisp Xuất Cao Độ Trắc Ngang Sang Excel: Hướng Dẫn Chi Tiết Và Thực Tiễn

2. Phương pháp 1: Sử dụng công thức Moderately simple

2.1. Hàm SUBSTITUTE

Hàm SUBSTITUTE trong Excel cho phép thay thế một chuỗi ký tự bằng một chuỗi khác. Khi loại bỏ ký tự đặc biệt, ta thường dùng "" (chuỗi rỗng) làm giá trị thay thế.

Ví dụ: Loại bỏ dấu gạch ngang `-`

Giả sử ô A2 chứa dữ liệu Dois: "123-456-789". Ta muốn loại bỏ dấu gạch ngang:

=SUBSTITUTE(A2, "-", "")

Kết quả: "123456789".

2.2. Hàm CLEAN

Hàm CLEAN loại bỏ tất cả ký tự kiểm soát (ASCII 0–31). Đây là công cụ nhanh chóng khi dữ liệu có thể chứa các ký tự thô như Chr(10) hoặc Chr(13).

=CLEAN(A2)

Nếu ô A2 chứa "Tên<CR>Địa chỉ", hàm sẽ trả về "TênĐịa chỉ".

2.3. Hàm TRIM

Hàm TRIM loại bỏ khoảng trắng thừa (có thể là khoảng trắng chuẩn hoặc khoảng trắng Unicode). Khi dữ liệu copy‑paste từ web, thường có khoảng trắng thừa ở đầu/đầu cuối.

=TRIM(A2)

2.4. Kết hợp nhiều hàm

Đối với dữ liệu có nhiều loại ký tự đặc biệt, ta có thể kết hợp:

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "#", "")))

Công thức này loại bỏ vegada “-” và “#”, sau đó loại bỏ ký tự kiểm soát và khoảng trắng thừa.

2.5. Lưu ý

Công thức chỉ thay đổi giá trị hiển thị, dữ liệu gốc vẫn giữ nguyên. Nếu muốn sửa dữ liệu gốc, copy kết quả rồi dán giá trị (Paste Special → Values) vào ô gốc.
Khi dữ liệu lớn (thousands rows), công thức có thể làm chậm Excel. Trong trường hợp này, hãy sử dụng Power Query hoặc VBA.

Có thể bạn quan tâm: Kết Nối Máy Quét Mã Vạch Với Excel: Hướng Dẫn Chi Tiết Cho Doanh Nghiệp

3. Phương pháp 2: Replace trong Excel (Find & Replace)

3.1. Mở hộp thoại Find & Replace

Chọn vùng dữ liệu cần làm sạch.
Nhấn Ctrl + H (Find & Replace).
Nhập ký tự cần thay thế vào ô Find what.
Để trống ô Replace with nếu muốn loại bỏ ký tự.

3.2. Thay thế nhiều ký tự đồng thời

Excel không hỗ trợ thay thế nhiều ký tự cùng lúc trong một thao tác. Tuy nhiên, ta có thể thực hiện các bước lặp:

Ctrl + H → nhập ký tự 1 → Replace All.
Ctrl + H → nhập ký tự 2 → Replace All.
Lặp lại cho tới hết.

3.3. Thay thế ký tự kiểm soát

Để nhập ký tự kiểm soát, dùng hàm CHAR. Ví dụ:
Ctrl + H → Find what: =CHAR(10) (Line feed) → Replace with: (để trống).
Hoặc dùng Ctrl + J trong ô Replace để nhập Chr(10).

3.4. Ưu điểm & nhược điểm

Ưu điểm: nhanh, không cần công thức.
Nhược điểm: không tự động, phải thực hiện nhiều lần, không tốt cho dữ liệu lớn.

4. Phương pháp 3: Power Query – Xử lý dữ liệu chuyên sâu

Power Query (trong Excel 2026+ hoặc Add brasile) cung cấp công cụ mạnh mẽ để làm sạch dữ liệu. Dưới đây là quy trình chi tiết.

4.1. Nhập dữ liệu vào Power Query

Chọn vùng dữ liệu → Data → From Table/Range (đảm bảo vùng có tiêu đề).
Nếu chưa có tiêu đề, chọn My table has headers.

4.2. Sử dụng `Remove Characters` (loại bỏ ký tự)

Power Query cung cấp các hàm:

Text.Remove – loại bỏ ký tự khỏi chuỗi.
Text.Replace – thay thế chuỗi.

Ví dụ: Loại bỏ dấu gạch ngang

= Table.TransformColumns(#"Previous Step", {{"Column1", each Text.Replace(_, "-", ""), type text}})

Ví dụ: Loại bỏ ký tự kiểm soát

= Table.TransformColumns(#"Previous Step", {{"Column1", each Text.Remove(_, {"\r", "\n"}), type text}})

4.3. Loại bỏ khoảng trắng thừa

= Table.TransformColumns(#"Previous Step", {{"Column1 flame asem", each Text.Trim(_), type text}})

4.4. Sử dụng `Replace Values` (thay thế giá trị)

Chọn cột → Transform → Replace Values.
Nhập ký tự cần thay thế → để trống (loại bỏ).

4.5. Thêm bước tự động

Có thể bạn quan tâm: Loại Bỏ Dữ Liệu Trùng Lặp Trong Excel

Sau khi các bước đã được thiết lập, bạn có thể lưu và tải dữ liệu trở lại Excel. Mỗi lần dữ liệu cập nhật, bạn chỉ cần nhấn Refresh để Power Query tự động áp dụng các bước làm sạch.

4.6. Ưu điểm & hạn chế

Ưu điểm: tự động, hiệu năng cao, khả năng xử lý dữ liệu lớn, dễ dàng mở rộng quy trình.
Hạn chế: cần hiểu ngôn ngữ M (Power Query Language), có thể chậm với dữ liệu rất lớn (millions rows) nếu có nhiều bước phức tạp.

5. Phương pháp 4: VBA – Macro tùy biến

VBA (Visual Basic for Applications) cho phép viết macro để loại bỏ ký tự đặc biệt một cách linh hoạt. Dưới đây là một ví dụ macro đơn giản.

5.1. Macro loại bỏ ký tự không mong muốn

Sub RemoveSpecialChars() Dim rng As Range Dim cell As Range Dim ch As String ' Định nghĩa vùng dữ liệu: chỉnh sửa theo nhu cầu Set rng = Range("A2:A1000") ' Dấu gạch ngang và ký tự # cần loại bỏ Dim charsToRemove As Variant charsToRemove = Array("-", "#", "$") For Each cell In rng If Not IsEmpty(cell) Then ch = cell.Value Dim i As Long For i = LBound(charsToRemove) To UBound(charsToRemove) ch = Replace(ch, charsToRemove(i), "") Next i ' Loại bỏ ký tự kiểm soát (Chr 10, 13) ch = Replace(ch, Chr(10), "") ch = Replace(ch, Chr(13), "") cell.Value = ch End If Next cell
End Sub

Lưu ý: Macro này sẽ ghi đè lên dữ liệu gốc. Nếu muốn giữ nguyên dữ liệu gốc, hãy sao chép vào cột mới trước khi chạy macro.

5.2. Macro loại bỏ các ký tự Unicode không chuẩn

Đôi khi dữ liệu chứa ký tự Unicode không đúng chuẩn (ví dụ “). Dưới đây là cách loại bỏ:

Function RemoveInvalidUnicode(str As String) As String Dim i As Long, result As String Dim ch As String For i = 1 To Len(str) ch = Mid(str, i, 1) If战 AscW(ch) > 0 And AscW(ch) < 65536 Then result = result & ch End If Next i RemoveInvalidUnicode = result
End Function

Bạn có thể áp dụng hàm này trong macro tổng thể.

5.3. Định nghĩa hàm VBA để dùng trong công thức

Bạn có thể viết hàm VBA CleanSpecial để bỏ ký tự đặc biệt và sử dụng như công thức trong Excel:

Function CleanSpecial(str As String) As String Dim chars As Variant chars = Array("-", "#", "$", Chr(10), Chr(13), " ") ' thêm khoảng trắng Unicode Dim i As Long For i = LBound(chars) To UBound(chars) str = Replace(str, chars(i), "") Next i CleanSpecial = Trim(str)
End Function

Sau khi nhập hàm vào Editor, bạn chỉ cần nhập =CleanSpecial(A2) trong ô.

5.4. Ưu điểm & hạn chế

Ưu điểm: linh hoạt, có thể thực hiện các thao tác phức tạp, tự động cho toàn bộ dữ liệu.
Hạn chế: cần có kiến thức VBA, macro có thể bị khóa trong môi trường bảo mật.

6. Phương pháp 5: Regex – Regular Expression

Regex (regular expression) cho phép mô tả mẫu ký tự phức tạp. Excel không hỗ trợ regex mặc định, nhưng bạn có thể dùng VBA hoặc công cụ bên ngoài (Google Sheets, Python, Power Query M) để áp dụng.

6.1. Sử dụng regex trong VBA

Function RegExReplace(str As String, pattern As String, replaceWith As String) As String Dim re As Object Set re = CreateObject("VBScript.RegExp") re.Pattern = pattern re.Global = True RegExReplace = re.Replace(str, replaceWith)
End Function

Ví dụ: Loại bỏ tất cả ký tự không phải chữ và số:

=RegExReplace(A2, "^A-Za-z0-9", "")

6.2. Sử dụng regex trong Power Query (M)

Power Query hỗ trợ Text.RegexReplace:

= Table.TransformColumns(#"Previous Step", {{"Column1", each Text.RegexReplace(_, "^A-Za-z0-9", ""), type text}})

6.3. Sử dụng regex trong Google Sheets

Google Sheets có hàm REGEXREPLACE:

=REGEXREPLACE(A2, "^A-Za-z0-9", "")

6.4. Khi nào nên dùng regex?

Khi dữ liệu có nhiều loại ký tự đặc madness (ví dụ: loại bỏ mọi ký tự không phải chữ, số hoặc dấu chấm phẩy).
Khi cần điều chỉnh một mẫu phức tạp (ví dụ: giữ lại dấu gạch ngang giữa các số nhưng loại bỏ chúng ở đầu/đầu cuối).

7. Thực hành: Một ví dụ hoàn chỉnh

Giả sử chúng ta có dữ liệu trong cột A (từ A2 tới A1000) chứa các chuỗi như sau:

1. " ABC-123 "
2. "XYZ#789"
3. "DEF\r\nGHI"
4. "J K L"
5. "PQR-"

Mục tiêu: Loại bỏ dấu gạch ngang -, ký tự #, ký tự kiểm soát Chr(10) và Chr(13), cũng như khoảng trắng thừa.

7.1. Sử dụng công thức

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "#", ""), CHAR(10), "")))

Khi áp dụng cho toàn bộ cột, ta nhận được:

1. "ABC123"
2. "XYZ789"
3. "DEFGHI"
4. "JKL"
5. "PQR"

7.2. Sử dụng Power Query

Chọn cột A → Data → From Table/Range.
Thêm bước Replace Values:
Replace - with "".
Replace # with "".
Replace Chr(10) with "".
Thêm bước Trim → Transform → Format → Trim~/TrimStart.
Tải dữ liệu trở lại Excel.

7.3. Sử dụng VBA

Sub CleanData() Dim rng As Range, cell As Range Set rng = Range("A2:A1000") For Each cell In rng If Not IsEmpty(cell) Then Dim str As String str = cell.Value str = Replace(str, "-", "") str = Replace(str, "#", "") str = Replace(str, Chr(10), "") str = Replace(str, Chr(13), "") cell.Value = Trim(str) End If Next cell
End Sub

8. Kiểm tra dữ liệu sau khi xử lý

Sau khi loại bỏ ký tự, bạn nên kiểm tra lại dữ liệu:

Sử dụng Conditional Formatting: Định dạng màu đỏ cho những ô có ký tự đặc biệt vẫn tồn tại.
Sử dụng công thức COUNTIF: Đếm số ô chứa ký tự không mong muốn.

excel =COUNTIF(A2:A1000, "#") ' Đếm số ô chứa ký tự #

Sử dụng hàm FIND: Kiểm tra vị trí ký tự.

excel =IFERROR(FIND("-", A2), "Không sombras")

Sử dụng Power Query: Thêm bước “Remove Errors” để xác nhận dữ liệu đã sạch.

9. Những lưu ý quan trọng khi làm sạch dữ liệu

Thông tin	Mô tả	Hành động
Dữ liệu gốc	Luôn giữ bản sao dữ liệu gốc để tránh mất dữ liệu quan trọng.	Sử dụng `Copy → Paste Special → Values` hoặc `Save union` trước khi làm sạch.
Số lượng dữ liệu	Khi làm việc với hàng triệu hàng, công thức Excel có thể chậm.	Tải dữ liệu vào Power Query hoặc viết macro VBA để xử lý.
Định dạng dữ liệu	Sau khi loại bỏ ký tự, có thể cần chuyển đổi kiểu dữ liệu (text → number).	Dùng `VALUE` hoặc `Text to Columns` để chuyển đổi.
Định dạng ngày tháng	Ký tự đặc biệt có thể làm mất định dạng ngày.	Kiểm tra bằng `ISDATE` hoặc `DATEVALUE`.
Bảo mật macro	Nếu chạy macro, cần bật `Trust Center Settings → Macro Settings`.	Đảm bảo macro được ký (signed) nếu chạy trong môi trường doanh nghiệp.

10. Kết luận

Loại bỏ ký tự đặc biệt trong Excel là một công việc thiết yếu trong quy trình làm sạch dữ liệu. Dựa vào quy mô, tính chất và nguồn gốc dữ liệu, bạn có thể chọn phương pháp phù hợp:

Công thức: nhanh, đơn giản, phù hợp cho dữ liệu nhỏ.
Find & Replace: tiện lợi cho thao tác nhanh.
Power Query: mạnh mẽ, tự động hoá, phù hợp cho dữ liệu lớn và phức tạp.
VBA: linh hoạt, có thể mở rộng, phù hợp khi cần tự động hoá toàn bộ quy trình.
Regex: tối ưu khi cần xử lý mẫu ký tự phức tạp.

Bằng cách áp dụng đúng công cụ, bạn sẽ tiết kiệm thời gian, tránh lỗi tính toán, và nâng cao chất lượng dữ liệu. Hãy thử áp dụng một vài phương pháp trên vào dữ liệu của bạn và cảm nhận sự khác biệt ngay!

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