Excel Remove Character From String: Hướng Dẫn Chi Tiết Và Các Phương Pháp Thông Dụng

Trong môi trường làm việc với dữ liệu, Microsoft Excel luôn là một công cụ không thể thiếu. Một trong những nhiệm vụ phổ biến mà người dùng thường gặp phải là loại bỏ ký tự không mong muốn trong chuỗi văn bản. Dù là loại bỏ dấu cách thừa, ký tự đặc biệt, hoặc các ký tự không cần thiết khác, việc thực hiện đúng cách sẽ giúp dữ liệu sạch sẽ, chuẩn xác và dễ dàng phân tích hơn. Bài viết này sẽ cung cấp cho bạn một hướng dẫn chi tiết, đầy đủ và sâu sắc về cách xóa ký tự khỏi chuỗi trong Excel, bao gồm:

1. Giới thiệu tổng quan về vấn đề và tại sao cần loại bỏ ký tự.
2. Các hàm cơ bản như SUBSTITUTE, REPLACE, LEFT, RIGHT, MID, TRIM, CLEAN.
3. Sử dụng hàm mảng và công thức phức tạp để loại bỏ nhiều ký tự đồng thời.
4. Sử dụng Power Query để xử lý dữ liệu quy mô lớn.
5. Sử dụng VBA (Macro) cho các yêu cầu tự động hoá phức tạp.
6. Các ví dụ thực tế từ các trường hợp kinh doanh, tài chính, marketing.
7. Mẹo tối ưu và cảnh báo thường gặp khi làm việc với chuỗi trong Excel.
8. Kết luận và các tài nguyên học tập bổ sung.

Có thể bạn quan tâm: Quản Lý Chi Tiêu Bằng Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Giới Thiệu Tổng Quan

1.1 Tại sao cần loại bỏ ký tự trong Excel?

Trong thực tế, dữ liệu thường được nhập từ nhiều nguồn khác nhau: hệ thống ERP, website, file CSV, email, hoặc thậm chí là nhập tay. Những nguồn này thường mang theo:

• Dấu cách thừa (trailing spaces, leading spaces) làm sai lệch kết quả so sánh.
• Ký tự đặc biệt như #, @, !, %, không cần thiết.
• Dòng mới (CHAR(10), CHAR(13)) gây rối khi dùng hàm VLOOKUP hoặc khi xuất dữ liệu.
• Các ký tự Unicode không hiển thị đúng trong một số phiên bản Excel.

Nếu không xử lý, những ký tự này sẽ:

• Gây lỗi trong công thức (#VALUE!, #N/A).
• Làm sai lệch thống kê (tổng, trung bình, đếm).
• Gây khó khăn khi ghép dữ liệu (merge) hoặc tạo báo cáo.

1.2 Mục tiêu của bài viết

• Cung cấp công cụ và kỹ thuật để loại bỏ ký tự một cách nhanh chóng.
• Giải thích nguyên lý hoạt động của từng hàm, giúp bạn hiểu và tùy biến.
• Đưa ra ví dụ thực tiễn, giúp bạn áp dụng ngay vào công việc.

Có thể bạn quan tâm: File Quản Lý Khách Sạn Bằng Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

2. Các Hàm Cơ Bản Để Xóa Ký Tự

2.1 Hàm SUBSTITUTE

SUBSTITUTE(text, old_text, new_text, instance_num)

• Mục đích: Thay thế một chuỗi ký tự (old_text) bằng một chuỗi khác (new_text). Khi new_text để trống (""), ta thực tế đang xóa ký tự đó.
• Tham số:
• text: Chuỗi gốc.
• old_text: Ký tự hoặc chuỗi cần xóa.
• new_text: Chuỗi thay thế, thường là "".
• instance_num (tùy chọn): Thứ tự xuất hiện muốn thay thế. Nếu bỏ qua, thay thế tất cả.

Ví dụ 1: Xóa dấu gạch ngang - trong cột A.

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

Ví dụ 2: Xóa ký tự thứ 2 là @ trong một chuỗi có nhiều @.

=SUBSTITUTE(A2, "@", "", 2)

2.2 Hàm REPLACE và REPLACEB

REPLACE(old_text, start_num, num_chars, new_text)

• Mục đích: Thay thế một phần của chuỗi dựa trên vị trí.
• start_num: Vị trí bắt đầu (đánh số từ 1).
• num_chars: Số ký tự cần thay thế.
• Khi new_text = "", ta xóa num_chars ký tự.

Ví dụ: Xóa 3 ký tự bắt đầu từ vị trí 5 trong ô B2.

=REPLACE(B2, 5, 3, "")

2.3 Hàm TRIM

TRIM(text)

• Mục đích: Loại bỏ tất cả các dấu cách thừa, chỉ giữ lại một dấu cách duy nhất giữa các từ. Đồng thời, xóa dấu cách đầu và cuối chuỗi.
• Lưu ý: TRIM chỉ xử lý ký tự ASCII (dấu cách CHAR(32)). Đối với các ký tự không‑gian (non‑breaking space) CHAR(160), cần dùng SUBSTITUTE.

Ví dụ: Dọn dẹp dữ liệu nhập tay.

=TRIM(C2)

2.4 Hàm CLEAN

CLEAN(text)

• Mục đích: Xóa các ký tự không in được (ASCII 0–31), bao gồm CHAR(10) (line feed) và CHAR(13) (carriage return).
• Thường dùng kết hợp với TRIM để làm sạch dữ liệu nhập từ web.

Ví dụ:

=TRIM(CLEAN(D2))

2.5 Hàm LEFT, RIGHT, MID

Khi cần cắt một phần chuỗi dựa trên vị trí, các hàm này rất hữu ích.

• LEFT(text, num_chars): Lấy num_chars ký tự từ bên trái.
• RIGHT(text, num_chars): Lấy num_chars ký tự từ bên phải.
• MID(text, start_num, num_chars): Lấy num_chars ký tự bắt đầu từ start_num.

Ví dụ: Loại bỏ ký tự cuối cùng (thường là dấu chấm .) trong ô E2.

=LEFT(E2, LEN(E2)-1)

Có thể bạn quan tâm: File Excel Quản Lý Nhân Viên: Hướng Dẫn Chi Tiết Từ A Đến Z

3. Sử Dụng Công Thức Mảng Để Xóa Nhiều Ký Tự Đồng Thời

Khi muốn xóa nhiều ký tự khác nhau trong một công thức duy nhất, chúng ta có thể kết hợp SUBSTITUTE trong một công thức mảng (array formula) hoặc sử dụng hàm TEXTJOIN (Excel 2026+). Dưới đây là hai cách phổ biến.

3.1 Phương Pháp 1: Lồng Nhiều SUBSTITUTE

Nếu danh sách ký tự cần xóa không quá dài, cách nhanh nhất là lồng các hàm SUBSTITUTE.

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

Giải thích: Đầu tiên xóa dấu -, sau đó kết quả của hàm đầu tiên được đưa vào hàm thứ hai để xóa /, cuối cùng xóa .

3.2 Phương Pháp 2: Công Thức Mảng Với TEXTJOIN và MID

Giả sử chúng ta có danh sách ký tự cần xóa trong một dải ô, ví dụ G1:G10. Ta có thể dùng công thức mảng:

=LET( txt, A2, chars, G1:G10, result, REDUCE(txt, chars, LAMBDA(acc, ch, SUBSTITUTE(acc, ch, ""))), result
)

Giải thích:

• LET giúp đặt tên cho các biến, tăng hiệu suất.
• REDUCE (Excel 365) lặp qua từng ký tự trong chars, áp dụng SUBSTITUTE để xóa.
• Kết quả trả về là chuỗi đã được dọn dẹp.

Lưu ý: REDUCE và LAMBDA chỉ có trong Excel 365/2026 trở lên. Đối với các phiên bản cũ, bạn có thể dùng CTRL+SHIFT+ENTER để tạo công thức mảng truyền thống, hoặc viết VBA.

3.3 Phương Pháp 3: Dùng FILTERXML (thủ thuật)

Kỹ thuật này dùng để tách chuỗi thành từng ký tự và lọc ra những ký tự không cần.

=TEXTJOIN("", TRUE, FILTERXML( "<t><s>" & SUBSTITUTE(A2, "", "</s><s>") & "</s></t>", "//snot(. = '-' or . = '/' or . = '')" )
)

• SUBSTITUTE(A2, "", "</s><s>") chèn thẻ XML giữa mỗi ký tự.
• FILTERXML lọc ra các ký tự không phải -, /, .
• TEXTJOIN ghép lại thành chuỗi mới.

Cách này khá “hack” nhưng hiệu quả khi không muốn viết VBA.

4. Xử Lý Dữ Liệu Lớn Bằng Power Query

Khi dữ liệu lên tới hàng ngàn, hàng triệu dòng, việc dùng công thức trên từng ô sẽ làm file nặng và chậm. Power Query (hay Get & Transform) là giải pháp tối ưu.

4.1 Bước 1: Nhập Dữ Liệu Vào Power Query

1. Chọn bảng dữ liệu → Data → From Table/Range.
2. Power Query Editor sẽ mở ra.

4.2 Bước 2: Thêm Cột Tùy Chỉnh Để Xóa Ký Tự

Trong Add Column → Custom Column, nhập công thức M:

= Text.Remove(ColumnName, {"-", "/", "", " "})

• Text.Remove(text as nullable text, removeChars as list) as nullable text
• removeChars là danh sách ký tự cần xóa.

Nếu muốn loại bỏ ký tự không phải chữ và số:

= Text.Select(ColumnName, each Char.IsLetterOrDigit(_))

4.3 Bước 3: Áp Dụng Và Đóng

Có thể bạn quan tâm: Cách Xóa Tất Cả Các Hàng Trống Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

• Nhấn Close & Load để đưa dữ liệu đã xử lý về Excel.

Ưu điểm:

• Xử lý nhanh, không làm tăng kích thước file.
• Dễ dàng cập nhật khi dữ liệu nguồn thay đổi (Refresh).

4.4 Ví Dụ Thực Tế: Làm Sạch Mã Sản Phẩm

Giả sử cột Mã SP chứa ký tự - và dấu cách thừa. Trong Power Query:

= Table.TransformColumns(Source, {{"Mã SP", each Text.Remove(_, {"-", " "}), type text}})

Kết quả: AB123 thay vì A B-123.

5. Tự Động Hóa Với VBA (Macro)

Khi yêu cầu phức tạp, lặp lại trên nhiều sheet, hoặc cần một nút bấm để thực hiện, VBA là công cụ mạnh mẽ.

5.1 Macro Đơn Giản Để Xóa Ký Tự

Sub RemoveChars() Dim rng As Range, cell As Range Dim charsToRemove As String, i As Long ' Đặt phạm vi cần xử lý (thay A2:A1000 bằng phạm vi thực tế) Set rng = ThisWorkbook.Sheets("Sheet1").Range("A2:A1000") ' Các ký tự cần xóa, viết liền nhau charsToRemove = "-/ " For Each cell In rng If Not IsEmpty(cell) Then For i = 1 To Len(charsToRemove) cell.Value = Replace(cell.Value, Mid(charsToRemove, i, 1), "") Next i End If Next cell
End Sub

• Replace trong VBA tương tự SUBSTITUTE.
• Vòng lặp For i duyệt từng ký tự trong charsToRemove.

5.2 Macro Nâng Cao: Dùng Regular Expressions

Nếu cần xóa mọi ký tự không phải chữ và số, Regular Expressions (RegExp) rất tiện.

Sub RemoveNonAlphaNumeric() Dim rng As Range, cell As Range Dim regEx As Object Set regEx = CreateObject("VBScript.RegExp") regEx.Pattern = "^A-Za-z0-9" regEx.Global = True Set rng = ThisWorkbook.Sheets("Sheet1").UsedRange For Each cell In rng If Not IsEmpty(cell) Then cell.Value = regEx.Replace(cell.Value, "") End If Next cell
End Sub

• ^A-Za-z0-9 nghĩa là “bất kỳ ký tự nào không phải A‑Z, a‑z, 0‑9”.
• Global = True để thay thế toàn bộ.

5.3 Gán Macro Vào Nút

1. Insert → Shapes → Chọn hình.
2. Click chuột phải → Assign Macro → Chọn RemoveChars hoặc RemoveNonAlphaNumeric.
3. Lưu file dưới dạng .xlsm.

6. Các Ví Dụ Thực Tế

6.1 Tài Chính: Làm Sạch Mã Tài Khoản

• Dữ liệu: 001-23-4567 (có dấu - và dấu cách cuối).
• Công thức: =SUBSTITUTE(TRIM(A2), "-", "") → 001234567.

6.2 Marketing: Chuẩn Hóa Email

• Nhiều email có dấu . thừa hoặc ký tự + trong phần username.
• Công thức: =LOWER(SUBSTITUTE(SUBSTITUTE(A2, "+", ""), ".", "")).

6.3 Bán Lẻ: Chuẩn Hóa Mã SKU

• SKU: SKU-001/ABC.
• Power Query: Text.Remove(SKU, {"-","/",""}) → SKU001ABC.

6.4 Nhân Sự: Xử Lý Tên Nhân Viên

• Dữ liệu: Nguyễn Văn A (nhiều dấu cách).
• Công thức: =TRIM(CLEAN(A2)) → Nguyễn Văn A.

7. Mẹo Tối Ưu và Cảnh Báo Thường Gặp

7.1 Khi Dùng SUBSTITUTE với Ký Tự Đặc Biệt

• Ký tự và ? là wildcards trong một số hàm (như COUNTIF). Khi dùng trong SUBSTITUTE, chúng không gây vấn đề, nhưng nếu dùng trong SEARCH hoặc FIND, cần escape bằng ~ (ví dụ ~).

7.2 Tránh Dùng TRIM Khi Dữ Liệu Có CHAR(160)

• TRIM không loại bỏ ký tự non‑breaking space (CHAR(160)). Dùng SUBSTITUTE(A2, CHAR(160), "") trước khi TRIM.

7.3 Hiệu Suất Khi Dùng Công Thức Mảng

• Công thức mảng trên hàng nghìn ô sẽ làm Excel chậm. Nên:
• Sử dụng Helper Columns (cột phụ) để chia nhỏ công việc.
• Hoặc chuyển sang Power Query/VBA.

7.4 Đảm Bảo Không Gây Mất Dữ Liệu

• Khi xóa ký tự, luôn sao lưu dữ liệu gốc (có thể tạo cột sao chép). Tránh trường hợp xóa nhầm ký tự quan trọng (ví dụ dấu - trong số điện thoại).

7.5 Kiểm Tra Kết Quả Bằng LEN

• Sau khi xử lý, dùng =LEN(original) - LEN(cleaned) để kiểm tra số ký tự đã bị xóa, giúp phát hiện lỗi.

8. Kết Luận

Việc loại bỏ ký tự không mong muốn trong Excel không chỉ giúp dữ liệu sạch sẽ, mà còn nâng cao độ chính xác của các phân tích, báo cáo và tự động hoá. Từ các hàm cơ bản như SUBSTITUTE, TRIM, CLEAN đến các giải pháp mạnh mẽ hơn như Power Query và VBA, mỗi công cụ đều có vị trí và ưu điểm riêng:

Bạn có thể bắt đầu bằng cách áp dụng các hàm cơ bản, sau đó mở rộng sang Power Query hoặc VBA khi nhu cầu tăng lên. Hãy luôn kiểm tra kết quả và sao lưu dữ liệu trước khi thực hiện thay đổi hàng loạt.

Tài Nguyên Tham Khảo Thêm

• Microsoft Support: “SUBSTITUTE function” – https://support.microsoft.com/substitute
• Power Query M Language: https://learn.microsoft.com/powerquery-m/
• Excel VBA Reference: https://learn.microsoft.com/office/vba/api/overview/excel
• ExcelJet: Các ví dụ nhanh về TRIM, CLEAN, SUBSTITUTE.

Với kiến thức và công cụ đã được trình bày, bạn hoàn toàn có thể đối phó với mọi tình huống cần xóa ký tự trong Excel một cách hiệu quả, nhanh chóng và an toàn. 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ẻ.