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ắn g 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.
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.
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:
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:
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:
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)
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:
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:
ĐẾ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:
– – – – –
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…