Hàm CLEAN() trong Excel: Cách tốt nhất để xóa ký tự không thể in

Bạn đã bao giờ nhập dữ liệu vào Excel và thấy những ký tự lạ, ẩn làm rối công việc của mình chưa? Đây là các ký tự không thể in, gây lỗi định dạng và thậm chí phá hỏng tính toán.

Bạn có thể loại bỏ các ký tự không mong muốn như vậy bằng hàm CLEAN(). Trong bài viết này, tôi sẽ hướng dẫn bạn về hàm CLEAN(), khi nào nên dùng và cách nó giúp bạn làm việc hiệu quả hơn.

Câu trả lời nhanh: Hàm CLEAN() trong Excel

Cách dùng hàm CLEAN như sau

• Gõ =CLEAN(.

• Nhập văn bản bạn muốn làm sạch hoặc tham chiếu ô.

• Đóng hàm bằng ).

• Nhấn Enter.

=CLEAN(text)Được hỗ trợ bởi

text có thể là bất kỳ chuỗi nào bạn muốn làm sạch.

=CLEAN(A1)Được hỗ trợ bởi

A1 có thể được thay bằng một tham chiếu ô khác.

Ví dụ về cách dùng hàm CLEAN()

Hãy tìm hiểu chi tiết hơn cách dùng hàm CLEAN(). Lần này, tôi sẽ kèm một số ảnh chụp màn hình.

Ở đây, tôi có dữ liệu trong ô A2 với một số ký tự không mong muốn. Vì vậy, tôi nhập công thức sau vào ô B2 để làm sạch văn bản:

=CLEAN(A2)Được hỗ trợ bởi

Dùng hàm CLEAN() trong một ô. Ảnh: Tác giả.

Ngoài việc làm sạch một ô, chúng ta còn có thể dùng hàm CLEAN() để làm sạch nhiều ô cùng lúc. Tất cả những gì bạn cần làm là, sau khi viết công thức ở ô thích hợp, kéo ô vuông màu xanh lá ở góc ô xuống đến hàng cuối. Bằng cách này, công thức sẽ được áp dụng cho toàn bộ tập dữ liệu và bạn sẽ có dữ liệu chuẩn chỉ trong vài giây.

Làm sạch nhiều ô với hàm CLEAN(). Ảnh: Tác giả.

Tôi thấy hàm CLEAN() đặc biệt hữu ích sau khi nhập dữ liệu từ nguồn bên ngoài như cơ sở dữ liệu hoặc trang web. Bởi vì khi nhập dữ liệu, các ký tự không thể in có mã ASCII 0–31 có thể đi kèm. Dù ta không nhìn thấy, chúng có thể làm hỏng phép tính hoặc khiến dữ liệu trông kỳ lạ.

Hàm CLEAN() của Excel loại bỏ những ký tự không mong muốn này. Nó còn dọn sạch các xuống dòng thừa và ký tự ẩn khác có thể gây lỗi định dạng. Vì vậy đôi khi dùng CLEAN() vẫn hữu ích ngay cả khi bạn không thấy vấn đề rõ ràng nào.

So sánh Excel CLEAN() và TRIM()

Nếu bạn đã học một trong các khóa của chúng tôi như Excel Fundamentals, bạn có thể đã quen với một hàm Excel phổ biến khác, hàm TRIM().

Mặc dù cả CLEAN() và TRIM() đều dọn dẹp văn bản, chúng hoạt động khác nhau. CLEAN() loại bỏ ký tự không thể in (ASCII 0-31), còn TRIM() loại bỏ khoảng trắng thừa (ASCII 32) ở trước, sau hoặc giữa các từ (trừ một khoảng trắng giữa các từ).

Nói cách khác, chúng ta dùng CLEAN() để xử lý dữ liệu có lỗi định dạng ẩn; còn nếu dữ liệu có khoảng trắng không nhất quán, ta có thể sửa bằng hàm TRIM().

Phần sau, tôi sẽ chỉ bạn cách kết hợp CLEAN() với TRIM() để vừa xóa ký tự không thể in vừa loại bỏ khoảng trắng thừa.

Kết hợp CLEAN() với các hàm Excel khác

Giờ bạn đã biết hàm CLEAN() là gì, hãy xem các tình huống thực tế có thể dùng hàm này:

Kết hợp CLEAN() với TRIM()

Nếu bạn để ý ở ví dụ trên, CLEAN() không loại bỏ được khoảng cách kỳ lạ giữa văn bản. Để khắc phục, tôi bọc nó trong hàm TRIM() và bạn có thể thấy chúng ta đã có dữ liệu được định dạng đúng trong cột B:

=TRIM(CLEAN(A2))Được hỗ trợ bởi

Kết hợp TRIM() và CLEAN(). Ảnh: Tác giả.

Dùng CLEAN() với SUBSTITUTE()

Có một vài ký tự unicode không thể in khác ngoài 32 ký tự ASCII đầu tiên, và chỉ CLEAN() thì không xử lý được. Nhưng nếu kết hợp hàm SUBSTITUTE() với CLEAN(), chúng ta có thể loại bỏ chúng dễ dàng. Hãy hiểu điều này qua một ví dụ:

Tôi có một đoạn văn bản ngẫu nhiên trong ô A2 bị lỗi khoảng trắng ở bên trái văn bản. Nhưng khi tôi thử xóa bằng TRIM() thì không được. Điều này có nghĩa là nó trông giống khoảng trắng nhưng thực ra không phải — mà là một ký tự đặc biệt. Trong các trường hợp như vậy, trước hết hãy xác định mã ký tự bằng công thức sau:

=CODE(LEFT(A2,1))Được hỗ trợ bởi

Điều này trả về mã ký tự là 160. Giờ bạn có thể loại bỏ nó bằng công thức sau:

=SUBSTITUTE(CLEAN(A2),CHAR(160),"")Được hỗ trợ bởi

Để văn bản sạch hơn, hãy bọc công thức trên trong TRIM().

=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160),""))Được hỗ trợ bởi

Làm sạch ô bằng CLEAN() và SUBSTITUTE(). Ảnh: Tác giả.

Dùng CLEAN() với REPLACE()

Nếu chúng ta muốn thay thế bất kỳ dấu phân tách nào khác có kèm ký tự không thể in, có thể kết hợp hàm CLEAN() với hàm REPLACE(). Hàm REPLACE() trong Excel thay thế một phần văn bản bằng văn bản mới dựa trên vị trí bắt đầu và độ dài xác định.

=REPLACE(old_text, start_num, num_chars, new_text)Được hỗ trợ bởi

Ví dụ, nếu tôi muốn thay dấu - thành khoảng trắng và xóa tất cả ký tự không thể in, tôi sẽ dùng công thức sau:

=REPLACE(CLEAN(A2), 4, 1, " ")Được hỗ trợ bởi

Công thức này trước tiên làm sạch văn bản cũ và thay thế dấu - ở vị trí 4 bằng một khoảng trắng.

Làm sạch văn bản với REPLACE() và CLEAN(). Ảnh: Tác giả.

Dùng CLEAN() với REPT()

Chúng ta cũng có thể kết hợp hàm REPT() với CLEAN() để làm sạch văn bản. Hàm REPT() lặp lại một văn bản số lần xác định.

=REPT(text, number_of_times)Được hỗ trợ bởi

Ví dụ, tôi áp dụng công thức sau cho tập dữ liệu của mình:

=TRIM(CLEAN(SUBSTITUTE(A2, " ", REPT(" ", 2))))Được hỗ trợ bởi

Ở đây, SUBSTITUTE() thay mỗi khoảng trắng đơn bằng hai khoảng trắng bằng REPT(" ", 2), giúp khoảng cách đồng đều hơn. Sau đó, CLEAN() loại bỏ ký tự ẩn không thể in, và TRIM() xóa khoảng trắng thừa, chỉ để lại một khoảng trắng giữa các từ.

Làm sạch văn bản bằng REPT() và CLEAN(). Ảnh: Tác giả.

Như bạn thấy, CLEAN() xuất hiện trong nhiều tình huống và phối hợp tốt với nhiều hàm khác.

Một vài điều cần lưu ý

Mặc dù CLEAN() và SUBSTITUTE() rất hữu ích để loại bỏ ký tự không mong muốn, đôi khi chúng ta có thể vô tình xóa mất thứ quan trọng nếu không chú ý. Ví dụ:

• Mất dữ liệu ngoài ý muốn: Tránh dùng các công thức quá rộng. Nếu chúng ta thay tất cả khoảng trắng bằng rỗng, các từ có thể bị dính vào nhau. Và nếu xóa mọi ký tự đặc biệt, công thức có thể xóa dấu gạch nối trong số điện thoại hoặc dấu thập phân trong giá. Vì vậy, trước khi áp dụng bất kỳ hàm nào, hãy kiểm tra xem công thức có hoạt động đúng không.
• Thử nghiệm trên một phần dữ liệu: Để an toàn, tôi luôn thử công thức trên một tập dữ liệu nhỏ trước. Thay vì áp dụng cho hàng nghìn dòng ngay lập tức, tôi thử trên vài dòng và kiểm tra xem có đúng như kỳ vọng không. Nếu mọi thứ ổn, khi đó tôi mới áp dụng cho toàn bộ dữ liệu.

Kết luận

Hàm CLEAN() nhanh chóng làm sạch dữ liệu văn bản bằng cách xóa các ký tự không thể in. Khi dùng riêng, nó đảm bảo dữ liệu sạch và có cấu trúc. Khi kết hợp với hàm TRIM(), nó cải thiện khả năng đọc bằng cách loại bỏ cả khoảng trắng thừa.

Nếu bạn muốn học thêm các mẹo và thủ thuật Excel, chúng tôi có những khóa học tuyệt vời để hỗ trợ bạn. Bắt đầu với lộ trình kỹ năng Excel Fundamentals để nắm vững nền tảng, sau đó học tiếp khóa Data Analysis in Excel và Data Preparation in Excel để trau dồi kỹ năng hơn nữa.