Khi bạn dán dữ liệu từ nguồn bên ngoài vào trang trang tính Excel như các bảng report, số liệu web, bạn sẽ thường gặp phải trường hợp có quá nhiều kí tự trắng thừa khiến công việc trở nên khó khăn hơn. Trong bài viết này, Học Excel Online sẽ cùng các bạn tìm hiểu về công thức bỏ khoảng trống thừa trong Excel.

Hàm SUBSTITUTE thay thế kí tự trắng

Hàm SUBSTITUTE sẽ thay thế một đoạn kí tự trong một chuỗi bằng đoạn kí tự khác.

Cú pháp của hàm SUBSTITUTE như sau:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Trong đó:

text là chuỗi kí tự đưa vào

old_text là đoạn kí tự nằm trong chuỗi muốn thay thế

new_text là đoạn kí tự mới thay thế cho old_text

instance_num (không bắt buộc) là vị trí thay. VD có 3 đoạn old_text, nếu để instance_num bằng 2, đoạn old_text 1 và 3 sẽ không bị ảnh hưởng. Nếu không nhập, hàm SUBSTITUTE mặc định sẽ thay thế hết.

Ứng dụng hàm SUBSTITUTE

Ta sử dụng hàm SUBSTITUTE để làm liền khoảng cách của các số trong một số điện thoại như sau:

=SUBSTITUTE(A2,” “,””)

Để chuyển số sau khi xử lí khoảng trống về định dạng number, ta nhập:

=–SUBSTITUTE(A2,” “,””) hoặc =VALUE(SUBSTITUTE(A2,” “,””))

Hàm TRIM đưa số lượng khoảng trống giữa các từ về bằng 1

Hàm TRIM sẽ xóa tất cả khoảng trắng trong chuỗi kí tự ngoại trừ dấu cách nằm giữa 2 từ.

Cú pháp:

=TRIM(text)

Trong đó text là chuỗi kí tự bạn muốn xóa khoảng trắng thừa

Ta sẽ sử dụng hàm TRIM để xóa các kí tự thừa trong ví dụ trên. Cụ thể như sau:

Và kết quả…

Các bạn có thấy  điều gì bất thường ở đây không? Đó là ô B3, mặc dù hàm TRIM tưởng rằng đã xử lí được chuỗi, nhưng tại sao khoảng cách giữa Đình và Nam vẫn còn lớn vậy?

Hãy đọc lời giải đáp ở phần dưới.

Sử dụng kết hợp các hàm xử lí kí tự. Hàm TRIM và hàm SUBSTITUTE, hàm CHAR, hàm CODE

Trong thực tế, có những “kí tự đặc biệt” giống với dấu cách, cũng là khoảng trắng, nhưng lại không phải dấu cách. Cụ thể là những khoảng trống giữa Đình và Nam trong hình.

Để có thể hiểu một cách tường tận hơn, Học Excel Online khuyên các bạn nên tìm hiểu về bộ kí tự ANSI và Macintosh. Vì sao, chúng ta hãy đến với cú pháp của 2 hàm: Hàm CHAR và hàm CODE

Hàm CHAR trả về kí tự được xác định bằng số mã. Với mỗi môi trường khác nhau hàm trả về bộ kí tự khác nhau:

• Nếu môi trường hoạt động là Macintosh -> hàm trả về bộ ký tự Macintosh.
• Nếu môi trường hoạt động là Windows -> hàm trả về bộ ký tự ANSI.

Hàm CHAR có cú pháp:

=CHAR(number)

Trong đó number là số từ 1-255 đại diện cho các kí tự quy định trong bảng mã.

Ngược lại với hàm CHAR, hàm CODE sẽ trả về giá trị số từ 1-255 tương ứng trong bảng mã cho kí tự đầu tiên của chuỗi.

Cú pháp của hàm CODE:

=CODE(Text)

Trong đó Text là chuỗi kí tự bạn muốn tìm kiếm code. Code sẽ hiển thị cho chữ cái đầu tiên trong chuỗi.

Quay trở lại bài toán. Việc đầu tiên, ta sẽ tìm xem khoảng trắng của Đình và Nam là kí tự nào qua các bước:

1.Sử dụng hàm SUBSTITUTE lọc khoảng trắng để chắc chắn không bị lẫn các kí tự trong chuỗi trống

2.Ta sẽ thực hiện Copy và Paste value.

3.Bôi đen vùng kí tự trắng và sau đó sử dụng hàm CODE

Kết quả thu được là 160

Nhưng đừng vội, tất cả những bước diễn giải trên đây chỉ để hiểu rõ hơn về kí tự trắng. Bây giờ, hãy Undo và ta sẽ tiến hành thay thế bằng cách kết hợp những hàm ở trên.

=TRIM(SUBSTITUTE(A3,CHAR(160),” “))

Trong trường hợp không muốn sử dụng trực tiếp khoảng cách ” “, bạn có thể thay bằng CHAR(32) với 32 là con số đại diện.

Xem thêm: Xóa bỏ khoảng trắng, làm sạch dữ liệu trong Excel

Sau khi đã được diễn giải tường tận, việc xóa khoảng trống bằng hàm đã không còn quá khó hiểu nữa phải không nào? Tuy nhiên, 160 chỉ là một trong những trường hợp, bởi vậy đừng quên luyện tập, tìm tòi để phát hiện ra những vấn đề mới và giải quyết chúng nhé.

Chúc các bạn học tốt!