Sử dụng hàm CLEAN để loại bỏ một số ký tự máy tính không in được đã được sao chép hoặc nhập vào một trang tính cùng với dữ liệu tốt.
Mã cấp thấp này thường được tìm thấy ở đầu và / hoặc kết thúc tệp dữ liệu.
Một số ví dụ phổ biến của các ký tự không thể in này là các ký tự được trộn lẫn với văn bản trong các ví dụ trong các ô A2 và A6 trong hình trên.
Các ký tự này có thể ảnh hưởng đến việc sử dụng dữ liệu trong các thao tác trang tính như in, sắp xếp và lọc dữ liệu.
Xóa các ký tự ASCII và Unicode không thể in bằng chức năng CLEAN
Mỗi ký tự trên máy tính - có thể in và không thể in được - có một số được gọi là mã hoặc giá trị ký tự Unicode của nó.
Một bộ ký tự khác, cũ hơn và được biết đến nhiều hơn là ASCII, viết tắt của Mã tiêu chuẩn của Mỹ cho trao đổi thông tin, đã được tích hợp vào bộ Unicode.
Kết quả là, 32 ký tự đầu tiên (0 đến 31) của bộ Unicode và ASCII giống hệt nhau và chúng được gọi là các ký tự điều khiển được sử dụng bởi các chương trình để điều khiển các thiết bị ngoại vi như máy in.
Như vậy, chúng không được sử dụng trong một bảng tính và có thể gây ra các loại lỗi được đề cập ở trên khi có mặt.
Hàm CLEAN, được đặt trước bộ ký tự Unicode, được thiết kế để loại bỏ 32 ký tự ASCII không in đầu tiên và loại bỏ các ký tự giống nhau từ bộ mã Unicode.
Cú pháp và đối số của hàm CLEAN
Cú pháp của hàm liên quan đến cách bố trí của hàm và bao gồm tên, khung và đối số của hàm .
Cú pháp cho hàm CLEAN là:
= CLEAN (Văn bản)
Văn bản - (bắt buộc) dữ liệu phải được làm sạch các ký tự không thể in được. Tham chiếu ô đến vị trí của dữ liệu này trong trang tính.
Ví dụ: để xóa dữ liệu trong ô A2 trong hình trên, hãy nhập công thức:
= CLEAN (A2)
vào một ô bảng tính khác.
Làm sạch số
Nếu được sử dụng để làm sạch dữ liệu số, hàm CLEAN, ngoài việc xóa bất kỳ ký tự không in nào, sẽ chuyển đổi tất cả các số thành văn bản - điều này có thể dẫn đến lỗi nếu dữ liệu đó sau đó được sử dụng trong các phép tính.
Ví dụ: Xóa các ký tự không in được
Trong cột A trong hình ảnh, hàm CHAR đã được sử dụng để thêm các ký tự không in vào văn bản từ như được hiển thị trong thanh công thức phía trên trang tính cho ô A3 sau đó được loại bỏ bằng hàm CLEAN.
Trong các cột B và C của hình ảnh ở trên, hàm LEN, tính số lượng ký tự trong một ô, được sử dụng để hiển thị ảnh hưởng của việc sử dụng hàm CLEAN trên dữ liệu trong cột A.
Số ký tự cho ô B2 là 7 - bốn ký tự cho văn bản từ và ba ký tự cho các ký tự không in xung quanh nó.
Số ký tự trong ô C2 là 4 vì hàm CLEAN đã được thêm vào công thức và loại bỏ ba ký tự không in trước khi hàm LEN tính các ký tự.
Xóa ký tự # 129, # 141, # 143, # 144 và # 157
Bộ ký tự Unicode chứa các ký tự không in bổ sung không tìm thấy trong bộ ký tự ASCII - số 129, 141, 143, 144 và 157.
Mặc dù trang web hỗ trợ của Excel nói rằng nó không thể, hàm CLEAN có thể loại bỏ các ký tự Unicode này khỏi dữ liệu như được hiển thị trong hàng ba ở trên.
Trong ví dụ này, hàm CLEAN trong cột C được sử dụng để loại bỏ năm ký tự điều khiển không nhìn thấy này để lại một số ký tự chỉ có bốn chữ cho văn bản từ trong C3.
Đang xóa Ký tự # 127
Có một ký tự không in trong bộ Unicode mà chức năng CLEAN không thể xóa - ký tự có hình hộp # 127 được hiển thị trong ô A4, trong đó bốn ký tự bao quanh văn bản từ.
Số ký tự 8 trong ô C4 cũng giống như trong ô B4 và vì hàm CLEAN trong C4 đang cố gắng tự xóa # 127 một cách không thành công.
Tuy nhiên, như được hiển thị trong các hàng năm và sáu ở trên, có các công thức thay thế bằng cách sử dụng các hàm CHAR và SUBSTITUTE có thể được sử dụng để xóa ký tự này:
- Công thức trong hàng năm sử dụng SUBSTITUTE và CHAR để thay thế ký tự # 127 bằng một ký tự mà hàm CLEAN có thể loại bỏ — trong trường hợp này, ký tự số 7 (dấu chấm đen được thấy trong ô A2);
- Công thức trong hàng sáu sử dụng các hàm SUBSTITUTE và CHAR để thay thế ký tự # 127 không có gì như được hiển thị bằng dấu ngoặc kép ( "" ) ở cuối công thức trong ô D6. Kết quả là, hàm CLEAN không cần thiết trong công thức, vì không có ký tự nào cần loại bỏ.
Loại bỏ các không gian không phá vỡ khỏi một bảng tính
Tương tự như các ký tự không thể in được là không gian không phá vỡ mà cũng có thể gây ra các vấn đề với tính toán và định dạng trong một bảng tính. Giá trị Unicode cho không gian không phá vỡ là # 160.
Các khoảng trống không được sử dụng rộng rãi trong các trang web - mã html cho nó là & nbsp; - vì vậy nếu dữ liệu được sao chép vào Excel từ một trang web, các khoảng trống không phá vỡ có thể được bao gồm.
Một cách để loại bỏ các khoảng trống không bị phá vỡ khỏi một trang tính bằng công thức này kết hợp các hàm SUBSTITUTE, CHAR và TRIM.