Có thể bạn quan tâm: Hàm Trong Excel Bắt Buộc Phải Có Đối Số: Tất Cả Những Hàm Không Thể Bỏ Qua
Giới thiệu
Trong thế giới dữ liệu ngày nay, việc làm sạch và chuẩn hóa dữ liệu luôn là một thách thức lớn đối với nhà phân tích, chuyên viên dữ liệu, và những người làm việc với Excel. Đặc biệt, khi dữ liệu được nhập từ nhiều nguồn khác nhau, chúng thường chứa các ký tự đặc biệt như dấu chấm, dấu phẩy, dấu gạch ngang, ký tự không thể hiển thị, hoặc các ký tự tiếng Việt bị lỗi. Khi không được xử lý đúng cách, những ký tự này có thể làm rối loạn công thức tính toán, gây lỗi trong báo cáo, và thậm chí dẫn đến quyết định sai lầm.
Trong bài viết này, chúng ta sẽ đi sâu vào cách tách các ký tự đặc biệt từ chuỗi trong Excel, która có thể được thực hiện bằng các công thức tiêu chuẩn, công thức nâng cao (dùng LET, LAMBDA), hoặc bằng VBA. Bạn sẽ học được cách:
- Xác định và loại bỏ các ký tự không mong muốn.
- Trích xuất chỉ những ký tự đặc biệt hoặc chỉ những ký tự hợp lệ.
- Sử dụng công thức để làm việc trong Excel mà không cần viết mã.
- Khi cần, mở rộng khả năng với VBA và regex.
Bài viết được viết chi tiết, có ví dụ thực tế, trình bày từng bước, và cung cấp cả các mẹo nhỏ giúp bạn làm việc hiệu quả hơn.
Điểm lưu ý: Tất cả các ví dụ đều được thực hiện trên Excel 365/2026, nhưng hầu hết công thứcα cũng tương thích với các phiên bản trước đó nếu bạn điều chỉnh một vài hàm.
Có thể bạn quan tâm: Hàm Thống Kê Số Lần Xuất Hiện Trong Excel
1. Khi nào cần tách ký tự đặc biệt?
Trước khi đi vào công thức, hãy xác định rõ mục tiêu:
| Mục tiêu | Ví dụ dữ liệu | Kết quả mong muốn |
|---|---|---|
| Loại bỏ dấu câu | Xin chào, anh! | Xin chào anh |
| Trích xuất số điện thoại | +84-123-456-7890 | 1234567890 |
| Lấy ra chuỗi ký tự tiếng Việt đã được chuẩn hóa | Thành phố Hồ Chí Minh | Thanh pho Ho Chi Minh |
| Chỉ lấy ký tự đặc biệt (để phân tích mức độ “độc đáo”) | abc@#123 | @# |
| Thay thế ký tự đặc biệt bằng một ký tự khác | Helloworld | Hello world |
Các mục tiêu này sẽ dẫn đến việc lựa chọn công thức hoặc phương pháp phù hợp.
Có thể bạn quan tâm: Hàm Sắp Xếp Từ Cao Đến Thấp Trong Excel: Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tế
2. Công thức Excel cơ bản để loại bỏ ký tự đặc biệt
2.1. Hàm SUBSTITUTE
Hàm SUBSTITUTE cho phép thay thế một chuỗi con trong một chuỗi lớn. Khi muốn loại bỏ ký tự, ta chỉ cần thay thế bằng chuỗi rỗng "".
=SUBSTITUTE(A1, "!", "")
Nếu bạn cần loại bỏ nhiều ký tự khác nhau, có thể lồng nhau:
=SUBSTITUTE(SUBSTITUTE(A1, "!", ""), ".", "")
Nhận xét: Phương pháp này nhanh chóng nhưng không thực sự linh hoạt khi số ký tự cần loại bỏ lớn hoặc không biết trước.
2.2. Hàm TRIM + CLEAN
TRIMloại bỏ dấu cách thừa.CLEANloại bỏ các ký tự điều khiển không thể hiển thị.
=TRIM(CLEAN(A1))
Tuy nhiên, CLEAN chỉ xử lý các ký tự ASCII 1-31, không bao gồm dấu câu, ký tự đặc biệt thường gặp.
Có thể bạn quan tâm: Hàm So Sánh Giá Trị Lớn Hơn Trong Excel: Hướng Dẫn Chi Tiết Và Ứng Dụng Thực Tiễn
3. Sử dụng công thức nâng cao với LET và LAMBDA
3.1. Giới thiệu LET
LET giúp đặt tên cho các giá trị trung gian trong công thức, giúp dễ đọc và tối ưu hiệu suất khi côngistí lặp lại nhiều lần.
=LET( txt, A1, cleaned, SUBSTITUTE(txt, ".", ""), cleaned2, SUBSTITUTE(cleaned, ",", ""), cleaned3, SUBSTITUTE(cleaned2, "!", ""), cleaned3
)
3.2. Hàm LAMBDA
LAMBDA cho phép biến một công thức thành một hàm tùy chỉnh mà bạn có thể tái sử dụng.
Bước 1: Định nghĩa hàm
=LAMBDA(txt, LET( chars, {"!","@","#","$","%","^","&","","(",")","-","+","="}, result, txt, i, 1, WHILE(i <= LEN(chars), result, SUBSTITUTE(result, INDEX(chars, i), ""), i, i+1 ), result )
)
Rút gọn: Bạn có thể lưu hàm này vào Name Manager với tên RemoveSpecialChars.
Bước 2: Sử dụng hàm
=RemoveSpecialChars(A1)
3.3. Giải pháp LAMBDA + REGEXREPLACE (Excel 365)
Excel 365 có hàm REGEX.REPLACE (hoặc REGEX.REPLACE trong các phiên bản mới nhất). Bạn có thể loại bỏ mọi ký tự không phải distributable:
=REGEX.REPLACE(A1, "^\p{L}\p{N}\s", "")
Giải thích:
\p{L}: bất kỳ ký tự chữ nào (bao gồm tiếng Việt).\p{N}: bất kỳ ký tự số nào.\s: khoảng trắng.^...: mọi ký tự không thuộc nhóm trên.
Kết quả: chỉ giữ lại chữ, số và khoảng trắng.
4. Trích xuất ký tự đặc biệt
Đôi khi bạn muốn tìm và tách ra những ký tự đặc biệt thay vì loại bỏ chúng. Điều này hữu ích trong việc phân tích dữ liệu (ví dụ: đo jdbc?).
4.1. Dùng công thức array + MID + CODE
Giả sử bạn muốn tìm ಹಿಂದೆ dấu gạch ngang:
=TEXTJOIN("", TRUE, IF(MID(A1, SEQUENCE(LEN(A1)), 1) = "-", "-", ""))
Giải thích:
SEQUENCE(LEN(A1))ুটি tạo một mảng số 1..n.MID(A1, SEQUENCE(LEN(A1)), 1)lấy từng ký tự.IF(...="–")kiểm tra nếu ký tự là dấu gạch ngang, trả lại ký tự, ngược lại trả lại chuỗi rỗng.TEXTJOIN("", TRUE, ...)kết hợp lại.
4.2. Sử dụng hàm FIND và SUBSTITUTE
Nếu bạn muốn tìm vị trí của ký tự đặc biệt:
=IFERROR(FIND("@", A1), "Không có")
Sau đó có thể dùng MID để lấy ký tự đó.
5. Sử dụng VBA để tách ký tự đặc biệt
Trong một số trường hợp, công thức có thể trở nên tải nặng và chậm. Khi đó, VBA sẽ là lựa chọn tốt hơn.
5.1. Macro đơn giản: Loại bỏ ký tự đặc biệt
Function RemoveSpecialChars(txt As String) As String Dim i As Long, ch As String, res As String For i = 1 To Len(txt) ch = Mid(txt, i, 1) If ch Like "A-Za-z0-9 " Then res = res & ch End If Next i RemoveSpecialChars = res
End Function
Sử dụng trong Excel:
=RemoveSpecialChars(A1)
Jeremy: Kiểu
Like "A-Za-z0-9 "sẽ giữ lại chữ (không bao gồm tiếng Việt). Nếu muốn giữ tiếng Việt, hãy thay bằngLike "A-Za-z0-9À-ÿ "hoặcLike ""tùy thuộc vào Unicode.
5.2. Macro nâng cao: Dùng Regex
VBA không hỗ trợ regex mặc định, nhưng bạn có thể sử dụng Microsoft VBScript Regular Expressions 5.5.
Function CleanWithRegex(txt As String) As String Dim reg As Object Set reg = CreateObject("VBScript.RegExp") With reg .Pattern = "^\p{L}\p{N}\s" .Global = True End With CleanWithRegex = reg.Replace(txt, "")
End Function
Lưu ý: Cần bật
Microsoft VBScript Regular Expressions 5.5trongReferences.
6.птом: Thực hành với ví dụ thực tế
6.1. Tình huống: Chuẩn hóa dữ liệu khách hàng
Bạn có một cột Tên khách hàng với dữ liệu chứa nhiều ký tự đặc biệt và dấu câu không cần thiết.
| Bản gốc | Chuẩn hóa |
|---|---|
Họ, Tên! | Ho Ten |
Nguyễn@ Văn, Khánh. | Nguyen Van Khanh |
A. B. C. | A B C |
Cách làm:
- Loại bỏ dấu câu và ký tự đặc biệt:
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, ",",""),".",""),"!",""),"@",""),"(",""),")",""))
- Chuẩn hóa tiếng Việt: Sử dụng hàm
REMOVEACCENTS(có thể tự viết hoặc dùng add-in).
Function REMOVEACCENTS(text As String) As String Dim temp As String temp = text temp = Replace(temp, "á", "a") ' ... (điều chỉnh cho tất cả các ký tự) REMOVEACCENTS = temp
End Function
- Kết hợp:
=REMOVEACCENTS(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, ",",""),".",""),"!",""),"@",""),"(",""),")","")))
6.2. Tình huống: Tách số điện thoại
વણી: Bạn có cột Số điện thoại chứa các ký tự như +84 (0) 123-456-7890 và muốn chỉ lấy số.
Cách làm:
=TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A1, SEQUENCE(LEN(A1)), 1)), MID(A1, SEQUENCE(LEN(A1)), 1), ""))
Giải thích:
MID(A1, SEQUENCE(LEN(A1)), 1)lấy từng ký tự.--chuyển teh into số; nếu không phải số sẽ trả về lỗi,ISNUMBERsẽ đánh dấu True.TEXTJOINkết hợp lại.
7. Tối ưu hóa công thức: Khi nào nên dùng LET, khi nào dùng LAMBDA?
LET: Khi công thức có nhiều phần trung gian lặp lại và bạn muốn làm cho công thức ngắn gọn hơn, tránh tính toán lặp lại.
Ví dụ: Lo تفاصيل ký tự đặc biệt:čas.LAMBDA: Khi bạn cần tái sử dụng một công thức trong nhiều ô hoặcPero.
Ví dụ:=RemoveSpecialChars(A1).REGEX: Khi Excel 365 và bạn cầnfelsman logic phức tạp (dùng pattern matching).
8. Các mẹo bổ sung
- Kiểm tra dữ liệu đầu vào: Luôn xác định nguồn dữ liệu và perguntas. Nếu dữ liệu đến từ CSV hoặc web, có thể có ký tự đặc biệt không hiển thị.
- Sử dụng
TEXTSPLIT+FILTER: Nếu bạn muốn tách ra các phần của chuỗi dựa trên dấu phân cách (ví dụ;), kết hợpTEXTSPLITvàFILTERđể lọc ra phần cần. - Hàm
SUBSTITUTE: Đối với Excel 365, có thể dùngSUBSTITUTEvới tham sốoccurrenceđể chỉ thay thế lần xuất hiện đầu tiên hoặc cuối cùng. - Sử dụng
TRIM+CLEAN: DùngCLEANđể loại bỏ các ký tự điều khiển không thể hiển thị (ASCII 1-31) trước khiTRIM. - Tổ chức dữ liệu: Đặt tất cả công thức làm sạch trong một cột riêng, tránh ghi đè dữ liệu gốc. Điều này giúp bạn dễ dàng rollback hoặc kiểm tra lại.
9. Kết luận
Tách chuỗi ký tự đặc biệt trong Excel là một kỹ năng quan trọng giúp bạn làm sạch dữ liệu, chuẩn hóa dữ liệu, và tăng tính nhất quán của báo cáo. Bạn có thể:
- Sử dụng công thức Excel:
SUBSTITUTE,TRIM,CLEAN,LET,LAMBDA,REGEX.REPLACE. - Sử dụng VBA:
RemoveSpecialChars,CleanWithRegex. - Kết hợp: Đối với dữ liệu phức tạp, dùng cả công thức và macro để tối ưu hiệu suất.
Việc lựa chọn phương pháp phụ thuộc vào:
- Số lượng dữ liệu: Nếu ít, công thức đơn giản sẽ nhanh; nếu lớn, VBA hoặc
REGEXsẽ hiệu quả hơn. - Phức tạp của ký tự đặc biệt: Nếu cần xử lý đa ngôn ngữ, regex hay VBA sẽ phù hợp hơn.
- Mức độ linh hoạt: LAMBDA cho phép tái sử dụng công thức, giúp giảm lỗi và tăng tính nhất quán.
Bây giờ, bạn đã có đủ công cụ để:
- Loại bỏ những ký tự không mong muốn.
- Trích xuất những ký tự đặc biệt cần thiết.
- Chuẩn hóa dữ liệu tiếng Việt hay dữ liệu quốc tế.
- Tự động hóa quy trình làm việc với.Il
Hãy thử áp dụng những công thức và macro trên vào dữ liệu thực tế của bạn, và cảm nhận sự khác biệt trong hiệu suấtిఎ. Nếu bạn gặp khó khăn hoặc có yêu cầu đặc thù, đừng ngần ngại chia sẻ; cộng đồng Excel luôn sẵn sàng giúp đỡ!
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ẻ.