Trong bài viết này, Blog Học Excel Online hướng dẫn cho bạn cách loại bỏ dấu cách/ khoảng trắng trong Excel sử dụng các công thức. Bạn sẽ học cách xoá khoảng trắng phía trước, sau trong 1 ô, loại bỏ khoảng trắng thừa giữa các từ, khoảng trắng không ngắt và các ký tự không in được (non-printing character).

Vấn đề lớn nhất với các khoảng trắng là gì? Chúng ta không nhìn thấy chúng. Người dùng cẩn thận có thể tìm ra khoảng trắng ẩn ở trước đoạn văn bản hoặc vài khoảng trắng thừa ở giữa các từ. Nhưng không có cách nào tìm ra khoảng trắng phía sau, vì chúng ta không thể thấy khi khoảng trắng ở cuối các ô.

Sẽ không có vấn đề gì nếu những khoảng trắng thừa nằm yên, nhưng chúng sẽ ảnh hưởng đến các công thức của bạn. Khi 2 ô chứa đoạn văn bản giống nhau, 1 ô có khoảng trắng còn ô kia không có, thì khoảng trắng dù nhỏ đến đâu, dù chỉ là 1 ký tự cũng khiến 2 ô có giá trị khác nhau. Khi đó, bạn sẽ phải đau đầu tìm ra lý do tại sao công thức đúng lại không hiệu quả với 2 dữ liệu nhìn giống nhau.

Bạn đã hiểu vấn đề, giờ là lúc tìm ra giải pháp. Có một số cách loại bỏ khoảng trắng trong Excel, và bài viết này sẽ giúp bạn chọn phương pháp phù hợp nhất với công việc và dữ liệu của bạn.

LOẠI BỎ KHOẢNG TRẮNG THỪA TRONG EXCEL, KHOẢNG TRẮNG PHÍA TRƯỚC, SAU, VÀ GIỮA CÁC TỪ:

Nếu dữ liệu của bạn có các khoảng trắng không cần thiết, hàm TRIM sẽ giúp bạn xoá bỏ tất cả – khoảng trắng phía trước, sau và giữa các từ (chỉ để lại 1 khoảng trắng) với chỉ 1 công thức duy nhất.

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).

Xem thêm: Tài liệu excel cơ bản hay nhất mọi thời đại

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.

– – – – –

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.

Một số hàm cơ bản thường gặp như:

• SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
• COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
• Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
• Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

• Định dạng theo điều kiện với Conditional formatting
• Thiết lập điều kiện nhập dữ liệu với Data Validation
• Cách đặt Name và sử dụng Name trong công thức
• Lập báo cáo với Pivot Table…

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online