Đánh giá lequocthai.com:
Dựa trên bộ ký tự ASCII 7‑bit, các mã từ 0 tới 31 được xem là các ký tự điều khiển không in được như tab ngang, tab dọc, quay về dòng (carriage return) v.v.
Hàm CLEAN thường được áp dụng khi làm việc với một lượng lớn dữ liệu xuất khẩu chứa các ký tự không hiển thị như ngắt dòng do khác biệt mã hoá giữa các hệ thống và thiết bị.
Cú pháp
Cú pháp của hàm CLEAN như sau:
=CLEAN(text)Đối số
`text` – Đối số văn bản có thể được nhập trong dấu ngoặc kép hoặc dưới dạng tham chiếu ô.
Các đặc điểm quan trọng của hàm CLEAN
Một số tính năng đáng chú ý của hàm CLEAN:
- Hàm CLEAN loại bỏ các ký tự không hiển thị (mã 0‑31) trong bộ ký tự ASCII 7‑bit.
- Các ký tự không hiển thị từ bộ ký tự Unicode (giá trị 127, 129, 141, 143, 144, 157 và 160) không thể bị loại bỏ bằng hàm này.
- Nếu đối số `text` ở dạng số, giá trị trả về sẽ là văn bản.
- Hàm CLEAN chỉ hoạt động trong Excel 2003 trở lên.
Ví dụ
Khi dùng hàm CLEAN một cách đơn giản, nó sẽ loại bỏ mọi ký tự không hiển thị ASCII khỏi dữ liệu cho trước. Trong ví dụ này, ô B2 chứa văn bản sao chép từ một trang web, vì vậy có một số ký tự không hiển thị.
Sử dụng công thức như sau:
=CLEAN(B2)
Bây giờ chúng ta có văn bản đã được làm sạch ở ô C2.
Ví dụ 1 – Làm sạch dữ liệu số trong Excel
Giả sử chúng ta đã thu thập một vài mẫu điện thoại và nhận được giá của chúng trong một bảng tính. Khi tải xuống, chúng ta phát hiện có một số ký tự không hiển thị trong dữ liệu, gây ra vấn đề khi tính tổng.
Thay vì tự tay làm sạch dữ liệu ở cột C (giá), chúng ta có thể dùng hàm CLEAN để nhanh chóng loại bỏ tất cả các ký tự không hiển thị:
=CLEAN(C3)
Vì hàm CLEAN trả về dạng văn bản, nên các giá trị đã được làm sạch hiện đang ở dạng văn bản và tổng ở ô D10 hiện là 0. Để chuyển chúng sang dạng số, dùng hàm VALUE:
=VALUE(D3)
Kết hợp cả hai hàm trong một công thức ngắn gọn hơn:
=VALUE(CLEAN(C3))
Ví dụ 2 – Loại bỏ ngắt dòng trong Excel
Trong ví dụ này, chúng ta có một tập dữ liệu gồm một số tên và địa chỉ. Các địa chỉ được viết có ngắt dòng để dễ đọc hơn, nhưng khi đưa vào cơ sở dữ liệu cần loại bỏ ngắt dòng.
Áp dụng hàm CLEAN trực tiếp lên cột C sẽ cho ra kết quả mong muốn:
=CLEAN(C2)
Lưu ý:
Có một cách khác để đạt được mục tiêu trên. Vì ngắt dòng tương ứng với giá trị ASCII 10, nên có thể dùng hàm SUBSTITUTE để thay `CHAR(10)` bằng chuỗi rỗng:
=SUBSTITUTE(C2,CHAR(10),"")Ví dụ 3 – Xác định số ký tự không hiển thị trong dữ liệu
Giả sử bạn đang làm việc ở bộ phận Marketing và xuất một số dữ liệu từ công cụ trực tuyến để xử lý. Bây giờ cần kiểm tra dữ liệu xuất ra có chứa ký tự không hiển thị nào không.
Ta so sánh độ dài chuỗi gốc và chuỗi đã được làm sạch; nếu có sự chênh lệch thì có ký tự không hiển thị.
Công thức tính chênh lệch:
=LEN(B3)-LEN(CLEAN(B3))Kết hợp với hàm IF, công thức cuối cùng sẽ là:
=IF((LEN(B3)-LEN(CLEAN(B3)))>0,(LEN(B3)-LEN(CLEAN(B3)))&" Non‑printable Characters Found.","Clean Text")
Như vậy, chúng ta có thể xác định số ký tự không hiển thị trong dữ liệu và xử lý chúng tùy ý.
Nếu dữ liệu còn chứa dấu cách thừa (extra spaces) cùng với các ký tự không hiển thị thì sao?
Ví dụ 4 – Làm sạch dấu cách thừa
Trong bộ dữ liệu, chúng ta tải một số câu trích dẫn từ một trang web. Dữ liệu chứa dấu cách thừa và ký tự không hiển thị, vì vậy không thể dùng trực tiếp.
Khi dùng hàm CLEAN, chỉ ngắt dòng và ký tự không hiển thị được xóa:
=CLEAN(C2)
Hàm CLEAN xóa các ký tự có mã ASCII từ 0‑31; còn ký tự dấu cách có mã ASCII 32. Đối với dấu cách thừa, chúng ta có thể dùng hàm TRIM. Hàm TRIM loại bỏ các dấu cách dư thừa ở đầu và cuối chuỗi, và chỉ để lại một dấu cách duy nhất giữa các từ.
Công thức dùng TRIM:
=TRIM(C2)
Kết hợp cả hai hàm, chúng ta có:
=TRIM(CLEAN(C2))
Trong khi hàm CLEAN rất tốt để loại bỏ các ký tự ASCII không hiển thị, đôi khi chúng ta muốn xóa một số ký tự không hiển thị nằm ngoài phạm vi ASCII, chẳng hạn như dấu cách không ngắt (non‑breaking space) có mã 160, thường xuất hiện trong HTML.
Ví dụ 5 – Loại bỏ ký tự Unicode không hiển thị
Trong ví dụ này, chúng ta có hai câu trông giống nhau ở ô B2 và B3. Cả hai đều có dấu cách thừa ở đầu và một ký tự không hiển thị.
Áp dụng giải pháp kết hợp TRIM và CLEAN:
=TRIM(CLEAN(B2))
Ở ví dụ 2, chuỗi đã sạch và độ dài chuỗi sạch là 34 sau khi loại bỏ dấu cách thừa và ký tự không hiển thị. Tuy nhiên, ở ví dụ 1, ký tự không hiển thị đã bị xóa nhưng vẫn còn một dấu cách ở đầu.
Ký tự dấu cách này được gọi là non‑breaking space (nbsp), thường xuất hiện khi chúng ta sao chép dữ liệu từ chương trình khác hoặc từ một trang web. Ký tự này được biểu thị bằng `CHAR(160)`.
Để xóa nó, cần kết hợp hàm SUBSTITUTE với TRIM và CLEAN:
=TRIM(CLEAN(SUBSTITUTE(B2,CHAR(160)," ")))
Bây giờ, chúng ta có thể làm sạch dữ liệu ngay cả khi có dấu cách ẩn. Vì Unicode là một bộ ký tự rất lớn, còn có các ký tự không hiển thị khác (129, 141, 143, 144, 157…) có thể gây nhiễu; chúng ta có thể loại bỏ chúng bằng cùng một phương pháp.
CLEAN vs. TRIM
Như các ví dụ trên đã minh họa, hàm TRIM xử lý các dấu cách thừa, trong khi hàm CLEAN loại bỏ các ký tự không hiển thị thuộc mã ASCII. Dấu cách tương ứng với `CHAR(32)`, còn CLEAN chỉ xóa các ký tự có mã 0‑31.
—
Như vậy là chúng ta đã khám phá toàn bộ tính năng của hàm CLEAN. Khi đã nắm vững, hãy thực hành thêm để trở thành bậc thầy Excel. Chúng tôi sẽ sớm mang đến một bài hướng dẫn mới với những công thức hữu ích giúp bạn trở thành nhà vô địch Excel bất khả chiến bại.
