CÁCH BỎ KHOẢNG TRỐNG TRONG EXCEL TỰ ĐỘNG MỚI NHẤT 2022, 4 CÁCH XÓA KHOẢNG TRẮNG TRONG EXCEL CỰC ĐƠN GIẢN
Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn xoá khoảng trắng phía trước, phía sau và khoảng trắng thừa giữa các từ nhanh chóng và dễ dàng,giải thích lý do hàm TRIM bị lỗi và cách sửa.
Bạn đang xem: Cách bỏ khoảng trống trong excel
Bạn đang so sánh 2 cột mà bạn biết là sẽ giống nhau, nhưng công thức lại không cho ra kết quả như vậy? Hay bạn cộng 2 cột số nhưng tổng lại bằng 0? Hoặc khi bạn chắc chắn công thức hàm VLOOKUP của bạn chính xác, thì lại trả về toàn lỗi? Đó là một vài ví dụ mà có lẽ bạn đang băn khoăn. Và tất cả đều do những khoảng trắng thừa ở đầu, cuối hoặc giữa số hoặc đoạn chữ trong ô Excel.
Excel đưa ra những cách khác nhau để xoá khoảng trắng thừa và làm sạch dữ liệu của bạn. Nhưng trong bài này, chúng ta sẽ tìm hiểu hàm TRIM – công cụ nhanh và dễ dàng nhất để xoá khoảng trắng thừa trong Excel.
Xem nhanh
HÀM TRIM, XOÁ KHOẢNG TRẮNG THỪA TRONG EXCEL
Bạn sử dụng hàm TRIM để xoá khoảng trắng trắng thừa trong chuỗi văn bản, bao gồm các khoảng trắng phía trước, sau và giữa chuỗi, chỉ để lại 1 khoảng trắng giữa các từ.Cấu trúc của hàm TRIM rất đơn giản:
TRIM (chuỗi văn bản)
Có thể thay chuỗi văn bản bằng ô chứa chuỗi đó.
Ví dụ để xoá khoảng trắng trong ô A1, bạn dùng công thức:
=TRIM(A1)
Hình dưới là kết quả:
Nếu dữ liệu của bạn còn chứa dấu ngắt dòng và các ký tự không in được ngoài khoảng trắng thừa, bạn dùng hàm TRIM kết hợp với CLEAN để xoá 32 ký tự không in được đầu tiên trong hệ thống ASCII 7-bit.
Ví dụ, để xoá khoảng trắng, dấu ngắt dòng và các ký tự không mong muốn khỏi ô A1, dùng công thức:
=TRIM(CLEAN(A1))
VÍ DỤ CÁCH DÙNG HÀM TRIM TRONG EXCEL
Bạn đã biết các kiến thức cơ bản, giờ chúng ta xem xét dùng hàm TRIM trong những trường hợp cụ thể.
Xoá khoảng trắng trong cả cột dữ liệu
Giả sử bạn có 1 cột tên chứa khoảng trắng ở đầu và cuối chuỗi văn bản, cũng như khoảng trắng giữa các từ. Vậy bạn làm thế nào để xoá mọi khoảng trắng trước, sau và giữa ở tất cả các ô trong 1 lần? Chúng ta sao chép công thức hàm TRIM đến hết cột, sau đó thay thế các giá trị. Các bước chi tiết thực hiện như sau:
Viết công thức hàm TRIM cho ô cao nhất, trong ví dụ của chúng ta là A2=TRIM(A2)
Di chuyển con trỏ đến vị trí góc dưới bên phải ô công thức (trong ví dụ là B2), và khi con trỏ chuyển thành dấu cộng, kích đúp chuột để sao chép công thức xuống hết cột, với ô dữ liệu cuối cùng. Kết quả bạn sẽ có 2 cột – cột tên gốc còn khoảng trắng và cột tên đã được xoá khoảng trắng.
Chọn các ô chứa dữ liệu gốc (A2:A8), và nhấn Ctrl + Alt + V rồi nhấn V. Đó là phím tắt cho Paste Special > ValuesNhấn Enter
XOÁ KHOẢNG TRẮNG PHÍA TRƯỚC TRONG CỘT SỐ
Như bạn vừa thấy, hàm TRIM có chức năng xoá mọi khoảng trắng thừa khỏi cột dữ liệu mà không gặp khó khăn nào. Nhưng nếu dữ liệu của bạn là số, không phải chữ?
Nhìn qua, có vẻ hàm TRIM đã làm tốt nhiệm vụ. Nhưng nếu bạn xem xét kỹ hơn, bạn sẽ thấy các giá trị được chỉnh sửa không còn chức năng như số. Có vài điều bất thường như:
Cả cột gốc có khoảng trắng và cột đã sửa đều căn trái ngay cả khi bạn đặt dạng Số cho ô, nhưng số bình thường được mặc định căn phải.Công thức SUM áp dụng cho ô đã chỉnh sửa ra kết quả 0.
Bởi vì các giá trị qua chỉnh sửa là chuỗi văn bản, trong khi chúng ta cần số. Để sửa lỗi này, bạn nhân giá trị đã qua chỉnh sửa với 1 (nhân các giá trị trong 1 lần, bạn dùng Paste Special > Multiply).
Cách nhẹ nhàng hơn là lồng hàm TRIM trong hàm VALUE:
=VALUE(TRIM(A2))
Công thức trên sẽ xoá bỏ mọi khoảng trắng phía trước và phía sau, và trả giá trị kết quả về dạng số, như hình dưới.
CÁCH XOÁ KHOẢNG TRẮNG PHÍA TRƯỚC (LEFT TRIM)
Trong vài trường hợp, bạn có 2, thậm chí 3 khoảng trắng giữa các chữ để dữ liệu dễ đọc hơn. Tuy nhiên, khi bạn muốn bỏ những khoảng trắng phía trước, như thế này:
Như bạn đã biết, hàm TRIM cũng sẽ xoá khoảng trắng ở giữa các chữ. Để giữ lại những khoảng trắng này, chúng ta sử dụng công thức phức tạp hơn:
=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))
Trong công thức trên, bạn kết hợp hàm FIND, MID và TRIM để tính toán vị trí của ký tự đầu tiên trong chuỗi. Và sau đó, bạn thêm con số đó vào hàm MID khác, hàm sẽ đưa toàn bộ chuỗi văn bản (độ dài chuỗi được tính bằng hàm LEN) về vị trí của ký tự đầu tiên.
Hình dưới minh hoạ khi xoá mọi khoảng trắng phía trước, chỉ để lại khoảng trắng giữa các chữ.
Cuối cùng, thay thế đoạn chữ gốc bằng đoạn chữ đã được chỉnh sửa.
Đếm số khoảng trắng thừa trong 1 ô
Đôi lúc trước khi xoá khoảng trắng trong bảng Excel, bạn muốn biết có bao nhiêu khoảng trắng thừa ra trong đó.
Để có được con số này, tìm độ dài chuỗi văn bản bằng cách dùng hàm LEN, sau đó tính độ dài chuỗi văn bản khi không có khoảng trắng, và trừ đi:
=LEN(A2)-LEN(TRIM(A2))
Hình dưới minh hoạ công thức:
Lưu ý. Công thức trả kết quả là số khoảng trắng thừa, bao gồm khoảng trắng phía trước, phía sau và khoảng trắng thừa giữa các chữ. Nếu bạn muốn biết tổng số khoảng trắng, dùng hàm SUBSTITUTE.
Đừng bỏ lỡ: khóa học excel kế toán
ĐÁNH DẤU Ô CÓ KHOẢNG TRẮNG THỪA
Khi có những thông tin nhạy cảm hoặc quan trọng, bạn có thể chần chừ không biết nên xoá khoảng trắng. Trong trường hợp này, bạn có thể đánh dấu ô đó để kiểm tra thông tin trước khi quyết định xoá.
Xem thêm: Cách tải video từ youtube không cần phần mềm về máy tính và điện thoại
Để đánh dấu, ta dùng công thức điều kiện sau:
=LEN($A2)>LEN(TRIM($A2))
A2 là ô dữ liệu trên cùng mà bạn muốn đánh dấu.
Công thức hướng dẫn Excel đánh dấu ô có độ dài đoạn chữ lớn hơn độ dài đoạn chữ sau khi cắt bỏ khoảng trắng thừa.
Để tạo 1 công thức điều kiện, chọn tất cả các ô bạn muốn đánh dấu, bỏ ô đầu cột, vào Home > Styles > Conditional formatting > New Rule > Use a formula to determine which cells to format.
Ở hình dưới, kết quả khớp với khoảng trắng thừa chúng ta đếm ở ví dụ trước.
HÀM TRIM EXCEL LỖI
Hàm TRIM được thiết kế để xoá ký tự khoảng trắng (mã 32 trong hệ thống ASCII 7-bit). Trong hệ thống Unicode, còn có ký tự gọi là khoảng trắng không ngắt, mã thập phân 160 – mã html mà hàm TRIM không thể xoá.
Nếu dữ liệu của bạn có nhiều hơn 1 khoảng trắng mà hàm TRIM không xoá được, dùng hàm SUBSTITUTE để chuyển khoảng trắng không ngắt thành khoảng trắng thường. Giả sử chuỗi văn bản trong ô A1, công thức như sau:
=TRIM(SUBSTITUTE(A1, CHAR(160), ” “))
Nếu công thức trên cũng không hoạt động, có thể dữ liệu của bạn chứa ký tự không in được có mã ngoài khoảng 0 – 32 và khác 60. Trong trường hợp này, dùng công thức để tìm mã ký tự đó:
Khoảng trắng phía trước: =CODE(LEFT(A1,1))
Khoảng trắng phía sau: =CODE(RIGHT(A1,1))
A1 là ô có chứa chuỗi văn bản.
Khoảng trắng ở giữa (n là vị trí của ký tự lỗi trong chuỗi văn bản):
=CODE(MID(A1, n, 1)))
Sau đó đưa mã của ký tự vào công thức TRIM(SUBSTITUTE()).
Ví dụ nếu công thức CODE trả mã là 9, ký tự thanh ngang (Horizontal Tab), bạn dùng công thức sau để xoá:
=TRIM(SUBSTITUTE(A1, CHAR(9), ” “))
Cảm ơn các bạn đã đọc bài viết. Chúc các bạn thực hiện các thao tác với hàm TRIM thành công!
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Xóa khoảng trắng trong Excel – Việc có khá nhiều khoảng trắng trong các ô chứa dữ liệu sẽ gây ra nhiều vướng mắc khi mà bạn thực hiện các hàm hay những công thức. Vậy làm thế nào để có khả năng xóa những khoảng trắng đấy, hãy theo dõi bài content này để biết 4 cách xóa các khoảng trắng trong Excel dễ dàng, nhanh chóng cho mọi phiên bản nhé!
Bạn đang xem bài viết: 4 cách xóa khoảng trắng trong Excel cực đơn giản
Các nguyên nhân tạo nên nhiều khoảng trắng trong Excel
Do vô tình nhấn nhiều lần dấu cách khi nhập dữ liệu mới.Do copy hoặc tải 1 file Excel từ trên mạng hoặc từ máy khác sang.Do sự không tương thích giữa 2 phiên bản Excel.Cách xóa khoảng trắng trong Excel
Dùng hàm Trim để bỏ khoảng trắng trong Excel
Các Bạn có thể dùng hàm Trim trong Excel để xóa các khoảng trắng thừa khỏi văn bản.
Cú pháp của hàm TRIM: =TRIM(ô chứa dữ liệu cần xóa khoảng trắng thừa). Sau đó ấn Enter.
Sử dụng công cụ tìm và thay thế để bỏ khoảng trắng trong Excel
dùng cách này đơn giản hơn sử dụng Áp dụng hàm TRIM. tuy nhiên, cách này chỉ cho phép xóa khoảng trắng thừa giữa các từ. khoảng trống ở đầu và cuối, nếu chứa nhiều khoảng trắng thì cũng sẽ bị cắt thành 1 khoảng trắng chứ không bị xóa.
Các bước làm như sau:
Bước 1: Chọn các ô có chứa dữ liệu mà bạn mong muốn bỏ khoảng trắng giữa các từ.Bước 2: Nhấn phím Ctrl + H để mở hộp thoại Tìm và thay thế.Bước 3: Tại ô Find what, các bạn nhấn phím Space 2 lần. Tại ô Replace with, các bạn nhấn phím Space 1 lần.Bước 4: Nhấp vào nút Replace all, sau đấy nhấn OK để đóng hộp thoại công nhậnBước 5: Lặp lại bước 4 cho đến khi bạn thấy thông cáo không thể tìm thấy ký tự để thay thế như hình dưới là hoàn thành.
Xóa toàn bộ khoảng trắng giữa các số sử dụng công cụ tìm và thay thế trong Excel
Giả sử bạn có một bảng dữ liệu với các số, trong số đó các chữ số (hàng ngàn, hàng triệu, hàng tỷ) được phân tách bằng dấu cách. Vì Excel xem các số như Vậy coi như là văn bản và không cho phép các phép toán được làm trên đó, nên chúng ta phải xóa toàn bộ khoảng trắng giữa các số đấy.
Cách dễ nhất để xóa khoảng trắng thừa là sử dụng công cụ Tìm và thay thế trong Excel. Các bước thực hiện như sau:
Bước 1: Chọn các ô chứa số mà bạn mong muốn xóa khoảng trắng.Bước 2: Nhấn phím Ctrl + H để mở hộp thoại Tìm và thay thế.Bước 3: Nhấn Space trong ô Find what và đảm bảo rằng ô Replace with không chứ bất kỳ chỗ trống nào.Bước 4: Nhấp vào nút Replace all, sau đó nhấn OK. khi đó, tất cả các khoảng trắng đều bị xóa.Dùng công thức để xóa tất cả khoảng trắng trong Excel
Để có thể bỏ toàn bộ khoảng trắng trong Excel dùng công thức, Bạn có thể tạo một cột tạm mới và nhập bí quyết sau: =SUBSTITUE(B19,” “,””)
trong đó, giả sử B19 là ô đầu tiên của cột có số hoặc chữ mà bạn mong muốn xóa tất cả khoảng trắng.
Tổng kết
Trên đây chính là hướng dẫn xóa khoảng trắng trong excel và các cách khắc phục mà Thế giới táo khuyết muốn gửi đến bạn. Hy vọng qua bài viết này, bạn đọcsẽ có thêm nhiều thông tin và kiến thức hữu ích.
