Trong bài viết này, Blog Học Excel Online đã hướng dẫn 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 xóa 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 được in [ký tự không in được]
Vấn đề lớn nhất với khoảng trắng là gì? . 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 một 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 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 vẫn 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ẽ đau đầu khi 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 TRÊN EXCEL, KHOẢNG TRẮNG PHỤA TRƯỚC, SAU, VÀ GIỮA CÁC DẠNG TỪ
Nếu dữ liệu của bạn có khoảng trắng không cần thiết, hàm TRIM sẽ giúp bạn xóa 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 normal as after
=TRIM[A2]
Trong đó A2 là ô bạn muốn xóa khoảng trắng thừa
Như hình minh họa, hàm TRIM loại bỏ thành công các khoảng trắng ở đoạn đầu và đoạn cuối văn bản cũng như khoảng trắng thừa ở giữa các ô
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 để thực hiện là sử dụng Paste Special > Values
Ngoài ra, bạn có thể chỉ sử dụng hàm TRIM để 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 VÀO ĐƯỢ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 có thêm nhiều ký tự không được như về đầu dòng [dòng xuống dòng], xuống dòng [nạp dòng], tab ngang [tab ngang],
Hàm TRIM xóa hoàn toàn khoảng trống, nhưng không thể xóa các ký tự không có trong được. Về mặt kỹ thuật, hàm TRIM được kể chỉ để xóa ký tự 32 trong hệ thống ASCII 7-bit, tức là khoảng trống
Để loại trừ khoảng trắng và các ký tự không có trong Excel, hãy sử dụng hàm TRIM để kết hợp với CLEAR. Hàm CLEAR được sử dụng với mục đích xóa sạch dữ liệu, và hàm này có thể xóa tất cả 32 ký tự không có trong ASCII 7 bit [mã 0 đến 31] bao gồm cả ngắt dòng [ngắt dòng – mã 10]
Giả sử dữ liệu cần xóa ở ô A2, công thức sẽ như sau
=TRIM[SẠCH[A2]]
Nếu công thức Trim/Clean khiến nội dung của 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
- Use function Replace All of Excel. trong hộp Tìm gì, nhập ký tự về đầu dòng bằng phím tắt Ctrl + J, và trong hộp Thay thế bằng, nhập khoảng trắng. Nhấn nút Place All sẽ xóa mọi dấu ngắt dòng và thay thế bằng khoảng trắng
- Sử dụng công thức sau để thay thế các 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ÊN KHÔNG NGẮT [NON-BREAKING SPACE] TRÊN EXCEL
Nếu sau khi sử dụng công thức TRIM & CLEAN, những khoảng trắng cứng đầu vẫn còn, thì có thể bạn đã sao chép dữ liệu từ nguồn bên ngoài và có chứa khoảng trắng không ngắt
Để loại bỏ các khoảng trắng không ngắt [ký tự html ] thay thế bằng khoảng trắng thô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, vậy bạn có thể sử 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 thường
- Và cuối cùng, bạn công thức SUBSTITUTE vào hàm TRIM để xóa những khoảng trắng thông thường mà khoảng trắng không ngắt chuyển đổi thành
Nếu trong bảng còn chứa cả các ký tự không có trong bảng, hãy sử dụng hàm CLEAN kết hợp hàm TRIM và SUBSTITUTE Xóa các khoảng trống và ký tự không mong muốn với 1 công thức duy nhất
=TRIM[SẠCH[[SUBSTITUTE[A2,CHAR[160],” “]]]]
Hình dưới thể hiện sự khác nhau
XOÁ 1 KÝ TỰ KHÔNG VÀO ĐƯỢ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ể xóa khoảng trắng và các ký tự không có trong bảng, thì có nghĩa là trong bảng chứa các ký tự có mã ASCII ngoài khoảng 0 đến 32 [ký
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, sử dụng hàm CODE để xác định mã ký tự, sau đó sử dụng SUBTITUTE thay thế ký tự đó bằng khoảng trắng thông thường và TRIM để xóa 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 các ký tự mã theo cách sử dụng 1 trong các CODE công thức
- Khoảng trắng hoặc ký tự không được đặt ở đầu chuỗi văn bản
=CODE[TRÁI[A2,1]]
- Khoảng trắng hoặc ký tự không được đặt ở cuối chuỗi văn bản
=CODE[RIGHT[A2,1]]
- Khoảng trắng hoặc ký tự không đượ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ó các ký tự không được mã hóa ở giữa chuỗi văn bản, ở vị trí thứ 4, với công thức sau đó chúng ta sẽ tìm ra mã của ký tự đó
=CODE[MID[A2,4,1]]
Hàm CODE trả về là mã 127 [xem bên dưới]
- Nếu trong ô C2, bạn thay thế CHAR[127] bằng khoảng trắng thông thường [“”], và sau đó 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 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 để xóa 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 TRANG TRONG EXCEL
Trong một số trường hợp, bạn muốn xóa 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 tính toán, giúp đọc các số lớn hơn dễ dàng hơn, nhưng ngăn bạn sử dụng công thức để tính toán
Để xóa tất cả khoảng trắng trong 1 lần, hãy sử 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 bằng 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 [“ “], as after
=SUBSTITUTE[A2,” “,””]
Sau đó, công thức thay thế 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
ĐEM KHOANG TRÊN TRÊN EXCEL
Trước khi loại bỏ khoảng trắng trong 1 ô định trước, bạn có thể tò mò muốn biết có bao nhiêu khoảng trắng trong ô đó
To have the white distance in 1 ô, thực hiện các bước sau
- Tính toán chiều dài của chuỗi văn bản bằng hàm LEN. LEN[A2]
- Thay thế mọi khoảng trắng bằng không. SUBSTITUTE[A2,” “,””]
- Độ dài của chuỗi văn bản được tính khi không có khoảng trắng. LEN[SUBSTITUTE[A2,” “,””]]
- Get the length of string of the text string null
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 chuỗi độ dài trừ đi con số vừa tìm được
=LEN[A2]-LEN[TRIM[A2]]
Hình nền minh hoạ cả 2 công thức
Khi bạn biết có nhiều khoảng trắng trong mỗi ô, bạn có thể xóa khoảng trắng thừa bằng cách sử dụng hàm TRIM