4 Cách Sử Dụng Hàm CLEAN Thông Minh Trong Excel (Cơ Bản Đến Nâng Cao)

Đầu tiên mình sẽ giải thích về hàm Clean cho các bạn dễ hình dung nhé. Hàm CLEAN sẽ xóa tất cả các ký tự không in (print) được khỏi chuỗi văn bản được cung cấp.  Hàm được thiết kế để loại bỏ các ký tự không in được, được biểu thị bằng các số từ 0 đến 31 của mã ASCII 7 bit thường được tìm thấy khi bắt đầu dữ liệu đã được nhập vào Excel từ các ứng dụng khác.

Thế là xong phần giải thích bây giờ mình sẽ bắt đầu ngay với ví dụ đầu tiên.

Bỏ ô có nhiều dòng

Ví dụ 1 (Bỏ ô có nhiều dòng)

Ở đây chúng ta sẽ có các ô nhưng nó ở các hàng khác nhau, ví dụ: ta có câu xin chào các bạn nhưng mỗi chữ sẽ ở một hàng khác nhau, như hình dưới.

Bây giờ mình muốn xóa các khoảng trống của các hàng đi để được một câu hoàn chỉnh là xin chào các bạn. Ta dùng hàm =CLEAN(ô mà bạn cần xóa), ví dụ: bạn gõ hàm =CLEAN(B4) sau đó Enter và kết quả thu được.

Như vậy các ô xuống dòng sẽ bị xóa và sẽ còn một ô duy nhất là xin chào các bạn.

Xóa ký tự đặc biệt

Sang ví dụ 2 mình sẽ xóa các ký tự đặc biệt nhé. Giả sử mình có hai chuỗi là Excel nhưng bên trái và bên phải là các ký tự đặc biệt, như hình dưới.

Để thấy rõ được sự khác biệt khi sử dụng hàm CLEAN mình đã dùng hàm LEN để kiểm tra độ dài. Bây giờ mình muốn xóa các ký tự đặc biệt này thì mình chỉ cần dùng hàm =CLEAN(ô bạn muốn xóa) và Enter, ví dụ: =CLEAN(B8) và Enter khá là đơn giản nhưng kết quả mang lại rất tuyệt vời.

Ps:Cho các bạn chưa biết cách dùng hàm LEN để kiểm tra độ dài chuỗi thì ta sử dụng hàm như sau =LEN(ô bạn muốn kiểm tra) sau đó Enter.

Xóa ký tự ẩn

Đến với ví dụ 3 trường hợp này xuất hiện khi bạn copy một chuỗi từ hệ thống vào file Excel của mình thì bên trong chuỗi đó có thể sẽ chứa các ký tự ẩn. Bề ngoài nhìn nó rất giống một chuỗi thông thường nhưng bên trong chuỗi đó lại chứa các ký tự ẩn cho nên khi các bạn so sánh hai chuỗi với nhau sẽ không thu được kết quả mong muốn, tham khảo ví dụ bên dưới để hiểu rõ nhé.

Ở hình trên có hai chuỗi là excel nhưng một chuỗi có độ dài là 6 chuỗi còn lại có độ dài là 5. Nếu so sánh hai chuỗi này thì chúng sẽ không bằng nhau. Thoạt nhìn ta sẽ không thể nhận ra chúng có sự khác biệt đều này chứng tỏ hàm CLEAN là hữu dụng đến mức nào.

Để giải quyết vấn đề này bạn chỉ cần gõ =CLEAN(ô bạn muốn xóa) và Enter, ví dụ: =CLEAN(B13) kế tiếp Enter, tương tự như ví dụ trên đúng không nè.

Xóa ký tự siêu đặc biệt

Ở ví dụ cuối cùng này chúng ta sẽ dùng hàm CLEAN để xóa các ký tự siêu đặc biệt, nghe có vẻ cao siêu nhưng bản chất vẫn là xóa các ký tự. Vì hàm CLEAN chỉ xóa được ký tự từ 0 đến 31 trong bảng mã ASCII, nên khi xóa các ký tự nằm ngoài khoảng trên thì không thể xóa được. Cùng xem ví dụ xem thực hư thế nào nhé.

Ở hình trên là mình đang sử dụng ký tự 61 trong bảng mã ASCII vì nằm ngoài phạm vi xử lý của hàm CLEAN nên không thể xóa được ký tự.

Vì là để xóa ký tự siêu đặc biệt nên mình sẽ giới thiệu cách xóa đặc biệt nhé.

Đầu tiên bạn cần phải xác định được mã ASCII của ký tự cần xóa, mình sẽ dùng lại ví dụ trên nên mã của nó là 61. Nhưng nếu các bạn không biết mã ASCII của nó là gì thì cũng đừng lo nhé mình sẽ hướng dẫn các bạn ngay sau đây.

Để xác định ký tự ASCII bạn dùng hàm RIGHT cho mình nhé, cách thực hiện vô cùng đơn giản bạn gõ =RIGHT(ô cần xác định) sau đó Enter. Ví dụ luôn nhé =RIGHT(B18)

Tiếp theo là đổi ký tự đó ra mã ASCII nè, để thực hiện bạn dùng hàm CODE cách dùng hàm này thì tương tự hàm RIGHT bạn chỉ cần gõ =CODE(ô mà bạn vừa mới đổi ra ký tự ASCII) sau đó Enter, xem hình để biết kết quả nhé.

Sau khi đã có được mã ASCII của ký tự cần xóa tiếp theo ta sẽ kết hợp ba hàm CLEAN, hàm TRIM, hàm SUBSTITUBE. Vì bài này chỉ giới thiệu về hàm CLEAN nên mình sẽ không giải thích về hai hàm còn lại, nếu các bạn muốn tìm hiểu thì comment để mình làm luôn nè.

Cuối cùng ta sẽ áp dụng công thức sau để xóa ký tự siêu đặc biệt

=CLEAN(TRIM(SUBSTITUTE(ô mà bạn muốn xóa,CHAR(thứ tự đổi được ở hàm CODE),)))

Vì mỗi mã có một thứ tự khác nhau nên các bạn hãy thay đổi nó trước khi xóa nhé.

Video hướng dẫn

Trên đây chính là 4 cách sử dụng hàm CLEAN mà mình hay sử dụng, nó thực sự hữu ích với những người thường xuyên làm việc với Excel. Nếu bạn muốn học về Excel đừng bỏ qua nó nhé.