Công thức hàm TRIM bình thường như sau:

=TRIM(A2)

Trong đó A2 là ô bạn muốn xoá bỏ các khoảng trắng thừa.

Như hình minh hoạ, hàm TRIM loại bỏ thành công các khoảng trắng ở đầu và cuối đoạn văn bản cũng như khoảng trắng thừa ở giữa ô.

Và giờ, bạn chỉ cần thay thế giá trị trong cột gốc bằng giá trị trong cột TRIM. Cách dễ nhất để làm là dùng Paste Special > Values.

Ngoài ra, bạn có thể dùng hàm TRIM chỉ để loại bỏ khoảng trắng phía trước, giữ lại tất cả khoảng trắng ở giữa đoạn văn bản.

XOÁ NGẮT DÒNG VÀ KÝ TỰ KHÔNG IN ĐƯỢC

Khi bạn nhập dữ liệu từ nguồn bên ngoài, không chỉ có khoảng trắng thừa xuất hiện mà còn thêm nhiều ký tự không in được như về đầu dòng (carriage return), xuống dòng (line feed), tab ngang (horizontal tab), tab dọc (vertical tab).

Hàm TRIM xoá hoàn toàn khoảng trắng, nhưng không thể xoá các ký tự không in được. Về mặt kỹ thuật, hàm TRIM được thiết kể chỉ để xoá ký tự 32 trong hệ thống ASCII 7-bit, tức là khoảng trắng.

Để loại trừ các khoảng trắng và ký tự không in được trong Excel, dùng hàm TRIM kết hợp với CLEAR. Hàm CLEAR được dùng với mục đích xoá sạch dữ liệu, và hàm này có thể xoá tất cả 32 ký tự không in được trong ASCII 7 bit (mã 0 đến 31) bao gồm cả ngắt dòng (line break – mã 10).

Giả sử dữ liệu cần xoá ở ô A2, công thức sẽ như sau:

=TRIM(CLEAN(A2))

Nếu công thức Trim/Clean khiến nội dung các dòng nối với nhau và mất khoảng trắng, bạn có thể sửa lỗi đó bằng 1 trong các cách sau:

• Dùng chức năng Replace All của Excel: trong hộp Find what, nhập ký tự về đầu dòng bằng phím tắt Ctrl + J, và trong hộp Replace with, nhập khoảng trắng. Nhấn nút Place All sẽ xoá mọi dấu ngắt dòng và thay bằng khoảng trắng
• Dùng công thức sau để thay thế ký tự về đầu dòng (mã 13) và xuống dòng (mã 10) bằng khoảng trắng:

=SUBSTITUTE(SUBSTITUTE(A2, CHAR(13),” “), CHAR(10), ” “)

XOÁ BỎ KHOẢNG TRẮNG KHÔNG NGẮT (NON-BREAKING SPACE) TRONG EXCEL

Nếu sau khi dùng công thức TRIM & CLEAN, những khoảng trắng bướng bỉnh vẫn còn, thì có thể do bạn đã sao chép dữ liệu từ nguồn ngoài và có chứa khoảng trắng không ngắt.

Để loại bỏ những khoảng trắng không ngắt (ký tự html  ) thay thế bằng khoảng trắng thường, ta có công thức hàm TRIM như sau:

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

Để hiểu được, chúng ta cần chia nhỏ công thức trên:

• Khoảng trắng không ngắt có mã 160 trong hệ thống ASCII 7-bit, do vậy bạn có thể dùng công thức CHAR(160) để biểu thị.
• Hàm SUBSTITUTE dùng để chuyển khoảng trắng không ngắt thành khoảng trắng thường.
• Và cuối cùng, bạn gộp công thức SUBSTITUTE vào hàm TRIM để xoá những khoảng trắng thường mà khoảng trắng không ngắt chuyển thành.

Nếu trong bảng còn chứa cả các ký tự không in được, dùng hàm CLEAN kết hợp hàm TRIM và SUBSTITUTE nhằm xoá những khoảng trắng và ký tự không mong muốn với 1 công thức duy nhất:

=TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160),” “))))

Hình dưới thể hiện sự khác nhau:

XOÁ 1 KÝ TỰ KHÔNG IN ĐƯỢC CỤ THỂ

Nếu sự kết hợp của 3 hàm trong ví dụ trên (TRIM, CLEAN và SUBSTITUTE) không thể xoá khoảng trắng và ký tự không in được trong bảng, thì có nghĩa trong bảng chứa ký tự có mã ASCII ngoài khoảng 0 đến 32 (ký tự không in được) và khác 160 (khoảng trắng không ngắt).

Trong trường hợp này, dùng hàm CODE để xác định mã ký tự, sau đó dùng SUBTITUTE thay thế ký tự đó bằng khoảng trắng thường và TRIM để xoá khoảng trắng.

Giả sử khoảng trắng hoặc những ký tự khó chịu mà bạn muốn loại trừ ở trong ô A2, bạn viết 2 công thức:

1. Trông ô B2, phát hiện mã ký tự bằng cách sử dụng 1 trong những công thức CODE:

• Khoảng trắng hoặc ký tự không in được ở đầu chuỗi văn bản:

=CODE(LEFT(A2,1))

• Khoảng trắng hoặc ký tự không in được ở cuối chuỗi văn bản:

=CODE(RIGHT(A2,1))

• Khoảng trắng hoặc ký tự không in được ở giữa chuỗi văn bản, n là vị trí của ký tự:

=CODE(MID(A2, n, 1)))

Trong ví dụ này, chúng ta có ký tự không in được chưa biết mã ở giữa chuỗi văn bản, ở vị trí thứ 4, với công thức sau ta sẽ tìm ra mã của ký tự đó:

=CODE(MID(A2,4,1))

Hàm CODE trả kết quả là mã 127 (xem hình dưới)

2. Nếu trong ô C2, bạn thay thế CHAR(127) bằng khoảng trắng thường (“”), và rồi loại bỏ khoảng trắng đó:

=TRIM(SUBSTITUTE(A2, CHAR(127), ” “))

Kết quả sẽ giống như sau:

Nếu dữ liệu của bạn có một vài ký tự không in được khác nhau như khoảng trắng không ngắt, bạn có thể lồng 2 hoặc nhiều hàm SUBSTITUTE để xoá các ký tự không mong muốn trong 1 lần.

=TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(127), ” “), CHAR(160), ” “)))

XOÁ MỌI KHOẢNG TRẮNG TRONG EXCEL

Trong một số trường hợp, bạn muốn xoá mọi khoảng trắng trong 1 ô, bao gồm cả khoảng trắng giữa các chữ và số. Ví dụ như khi bạn nhập 1 cột số, khoảng trắng để cách hàng nghìn, giúp đọc các số lớn dễ hơn, nhưng ngăn bạn dùng công thức để tính toán.

Để xoá tất cả khoảng trắng trong 1 lần, dùng SUBSTITUTE như trong ví dụ trên, điểm khác duy nhất là bạn thay thế khoảng trắng với mã CHAR(32) bằng không (“”).

=SUBSTITUTE(A2, CHAR(32), “”)

Hoặc bạn để khoảng trắng trong dấu ngoặc kép (“ “), như sau:

=SUBSTITUTE(A2,” “,””)

Sau đó, thay thế công thức với các giá trị và các con số của bạn sẽ được tính toán chính xác.

ĐẾM KHOẢNG TRẮNG TRONG EXCEL

Trước khi loại bỏ các khoảng trắng trong 1 ô nhất định, bạn có thể tò mò muốn biết có bao nhiêu khoảng trắng trong ô đó.

Để có được số khoảng trắng trong 1 ô, làm các bước sau:

• Tính toán độ dài của chuỗi văn bản dùng hàm LEN: LEN(A2)
• Thay thế mọi khoảng trắng bằng không: SUBSTITUTE(A2,” “,””)
• Tính độ dài của chuỗi văn bản khi không có khoảng trắng: LEN(SUBSTITUTE(A2,” “,””))
• Lấy tổng độ dài chuỗi văn bản trừ đi độ dài của chuỗi không có khoảng trắng.

Giả sử chuỗi văn bản gốc ở trong ô A2, công thức hoàn chỉnh như sau:

=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))

Để tìm ra có bao nhiêu khoảng trắng thừa trong ô, lấy độ dài chuỗi văn bản khi không có khoảng trắng thừa, sau đó lấy tổng độ dài chuỗi trừ đi con số vừa tìm được:

=LEN(A2)-LEN(TRIM(A2))

Hình dưới minh hoạ cả 2 công thức:

Khi bạn biết có bao nhiêu khoảng trắng trong mỗi ô, bạn có thể xoá khoảng trắng thừa bằng cách dùng hàm TRIM.