Xoá khoảng trắng và các ký tự đặc biệt trong Excel
Vấn đề lớn nhất với những khoảng chừng trắng là gì ? Chúng ta không nhìn thấy chúng. Người dùng cẩn trọng hoàn toàn có thể tìm ra khoảng chừng trắng ẩn ở trước đoạn văn bản hoặc vài khoảng chừng trắng thừa ở giữa những từ. Nhưng không có cách nào tìm ra khoảng chừng trắng phía sau, vì tất cả chúng ta không hề thấy khi khoảng chừng trắng ở cuối những ô .
Sẽ không có yếu tố gì nếu những khoảng chừng trắng thừa nằm yên, nhưng chúng sẽ tác động ảnh hưởng đến những 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 chừng trắng còn ô kia không có, thì khoảng chừ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 nguyên do tại sao công thức đúng lại không hiệu suất cao với 2 tài liệu nhìn giống nhau .
Bạn đang đọc: Xoá khoảng trắng và các ký tự đặc biệt trong Excel
Bạn đã hiểu yếu tố, giờ là lúc tìm ra giải pháp. Có một số ít cách loại bỏ khoảng chừng trắng trong Excel, và bài viết này sẽ giúp bạn chọn chiêu thức tương thích nhất với việc làm và tài liệu của bạn .
Mục lục bài viết
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 tài liệu của bạn có những khoảng chừng trắng không thiết yếu, hàm TRIM sẽ giúp bạn xoá bỏ toàn bộ – khoảng chừng trắng phía trước, sau và giữa những từ ( chỉ để lại 1 khoảng chừng trắng ) với chỉ 1 công thức duy nhất .
Công thức hàm TRIM thông thường như sau :
=TRIM(A2)
Trong đó A2 là ô bạn muốn xoá bỏ những khoảng chừng trắng thừa .
Như hình minh hoạ, hàm TRIM loại bỏ thành công xuất sắc những khoảng chừng trắng ở đầu và cuối đoạn văn bản cũng như khoảng chừ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 hoàn toàn có thể dùng hàm TRIM chỉ để loại bỏ khoảng chừng trắng phía trước, giữ lại tổng thể khoảng chừ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 tài liệu từ nguồn bên ngoài, không riêng gì có khoảng chừng trắng thừa Open 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á trọn vẹn khoảng chừng trắng, nhưng không hề xoá những 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 mạng lưới hệ thống ASCII 7 – bit, tức là khoảng chừng trắng .
Để loại trừ những khoảng chừng trắng và ký tự không in được trong Excel, dùng hàm TRIM phối hợp với CLEAR. Hàm CLEAR được dùng với mục tiêu xoá sạch tài liệu, và hàm này hoàn toàn có thể xoá tổng thể 32 ký tự không in được trong ASCII 7 bit ( mã 0 đến 31 ) gồm có cả ngắt dòng ( line break – mã 10 ) .
Giả sử tài 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 những dòng nối với nhau và mất khoảng chừng trắng, bạn hoàn toàn có thể sửa lỗi đó bằng 1 trong những 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 và CLEAN, những khoảng chừng trắng bướng bỉnh vẫn còn, thì hoàn toàn có thể do bạn đã sao chép tài liệu từ nguồn ngoài và có chứa khoảng chừng trắng không ngắt .
Để loại bỏ những khoảng chừng trắng không ngắt ( ký tự html ) sửa chữa thay thế bằng khoảng chừng trắng thường, ta có công thức hàm TRIM như sau :
=TRIM(SUBSTITUTE(A2, CHAR(160), ” “))
Để hiểu được, tất 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ả những ký tự không in được, dùng hàm CLEAN tích hợp hàm TRIM và SUBSTITUTE nhằm mục đích xoá những khoảng chừng trắng và ký tự không mong ước với 1 công thức duy nhất :
=TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160),” “))))
Hình dưới bộc lộ sự khác nhau :
XOÁ 1 KÝ TỰ KHÔNG IN ĐƯỢC CỤ THỂ
Nếu sự phối hợp của 3 hàm trong ví dụ trên ( TRIM, CLEAN và SUBSTITUTE ) không hề xoá khoảng chừ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 chừng 0 đến 32 ( ký tự không in được ) và khác 160 ( khoảng chừ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 lập mã ký tự, sau đó dùng SUBTITUTE thay thế sửa chữa ký tự đó bằng khoảng chừng trắng thường và TRIM để xoá khoảng chừng trắng .
Giả sử khoảng chừng trắng hoặc những ký tự không dễ 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, tất cả 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ả hiệu 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 :
Nếu tài liệu của bạn có một vài ký tự không in được khác nhau như khoảng chừng trắng không ngắt, bạn hoàn toàn có thể lồng 2 hoặc nhiều hàm SUBSTITUTE để xoá những ký tự không mong ước trong 1 lần .
=TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(127), ” “), CHAR(160), ” “)))
XOÁ MỌI KHOẢNG TRẮNG TRONG EXCEL
Trong 1 số ít trường hợp, bạn muốn xoá mọi khoảng chừng trắng trong 1 ô, gồm có cả khoảng chừng trắng giữa những chữ và số. Ví dụ như khi bạn nhập 1 cột số, khoảng chừng trắng để cách hàng nghìn, giúp đọc những số lớn dễ hơn, nhưng ngăn bạn dùng công thức để thống kê giám sát .
Để xoá tổng thể khoảng chừ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 sửa chữa thay thế khoảng chừng trắng với mã CHAR ( 32 ) bằng không ( “ ” ) .
=SUBSTITUTE(A2, CHAR(32), “”)
Hoặc bạn để khoảng chừng trắng trong dấu ngoặc kép ( “ “ ), như sau :
=SUBSTITUTE(A2,” “,””)
Sau đó, thay thế sửa chữa công thức với những giá trị và những số lượng của bạn sẽ được đo lường và thống kê đúng chuẩn .
ĐẾM KHOẢNG TRẮNG TRONG EXCEL
Trước khi loại bỏ những khoảng chừng trắng trong 1 ô nhất định, bạn hoàn toàn có thể tò mò muốn biết có bao nhiêu khoảng chừng trắng trong ô đó .
Để có được số khoảng chừng trắng trong 1 ô, làm những 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 hảo như sau :
=LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))
Để tìm ra có bao nhiêu khoảng chừng trắng thừa trong ô, lấy độ dài chuỗi văn bản khi không có khoảng chừng trắng thừa, sau đó lấy tổng độ dài chuỗi trừ đi số lượng 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 chừng trắng trong mỗi ô, bạn hoàn toàn có thể xoá khoảng chừ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 và kỹ năng phải không nào ? Toàn bộ những kiến thức và kỹ năng này những bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên viên của Học Excel Online. Đây là khóa học giúp bạn mạng lưới hệ thống kiến thức và kỹ năng một cách rất đầy đủ, chi tiết cụ thể. Hơn nữa không hề có số lượng giới hạn về thời hạn học tập nên bạn hoàn toàn có thể tự do học bất kể khi nào, thuận tiện tra cứu lại kiến thức và kỹ năng khi cần. Hiện nay mạng lưới hệ thống đang có khuyến mại rất lớn cho bạn khi ĐK tham gia khóa học. Chi tiết xem tại : HocExcel. Online
Source: https://mix166.vn
Category: Thủ Thuật
