Giới thiệu cách loại bỏ khoảng trắng và ký tự đặc biệt trong Excel

Bài viết này từ Blog Siêu Marketing sẽ chỉ dẫn bạn phương pháp xóa dấu cách và khoảng trắng thừa trong Excel thông qua việc sử dụng công thức. Bạn sẽ được hướng dẫn cách xóa những khoảng trắng ở đầu, cuối hoặc giữa các từ trong một ô, bên cạnh việc loại bỏ các khoảng trắng không cần thiết, non-printing characters.

Khó khăn lớn nhất khi đối mặt với khoảng trắng là chúng thường không hiển thị rõ ràng. Những người dùng Excel kỹ lưỡng có thể nhận ra những khoảng trắng thừa ở đầu dòng hoặc giữa các từ, tuy nhiên, làm thế nào để phát hiện khoảng trắng nằm ở cuối, vì chúng ta không thấy được chúng khi chúng là phần cuối của ô.

Nếu những khoảng trắng này chỉ đơn giản là không di chuyển thì không sao, tuy nhiên chúng sẽ gây trở ngại cho các công thức của bạn. Nếu như hai ô có nội dung văn bản nhìn tương tự nhau, nhưng một ô lại chứa khoảng trắng còn ô kia thì không, thì chỉ một ký tự của khoảng trắng cũng có thể làm cho giá trị của hai ô này trở nên không giống nhau. Bạn sẽ cảm thấy khó chịu khi không giải thích được tại sao công thức chính xác lại bất lực trước hai dữ liệu có vẻ như giống hệt nhau.

Nắm bắt được vấn đề, giờ chúng ta hãy đi tìm cách giải quyết. Có khá nhiều phương án để loại bỏ khoảng trắng trên Excel, và bài viết dưới đây sẽ hướng dẫn bạn làm điều đó với phương án phù hợp nhất cho công việc và dữ liệu của bạn.

CÁCH LOẠI BỎ KHOẢNG TRẮNG THỪA TRONG EXCEL, BAO GỒM ĐẦU, CUỐI VÀ GIỮA CÁC TỪ:

Nếu trong dữ liệu của bạn xuất hiện nhiều khoảng trắng không cần thiết, bạn có thể dùng hàm TRIM để loại bỏ chúng, từ việc xóa đi những khoảng trắng ở đầu và cuối các từ cho đến việc loại bỏ khoảng trắng giữa chúng (để lại một khoảng trắng duy nhất) chỉ với một công thức đơn giản.

Bạn có thể áp dụng công thức hàm TRIM như sau:

=TRIM(A2)

Ở đây, A2 đại diện cho ô dữ liệu mà bạn muốn xóa khoảng trắng thừa.

Hàm TRIM đã được minh hoạ hiệu quả trong việc loại bỏ khoảng trắng ở cả đầu và cuối đoạn văn cũng như giữa các dữ liệu trong ô.

Sau đó bạn có thể thay thế dữ liệu cũ trong cột gốc bằng dữ liệu đã được xử lý bằng TRIM. Phương pháp dễ nhất để làm việc này chính là sử dụng chức năng Paste Special > Values.

Bạn cũng có khả năng sử dụng hàm TRIM cho mục đích xóa khoảng trắng ở đầu, trong khi vẫn bảo toàn các khoảng trắng giữa các từ.

PHƯƠNG PHÁP XOÁ NGẮT DÒNG VÀ KÝ TỰ KHÔNG IN ĐƯỢC:

Khi nhập dữ liệu từ các nguồn bên ngoài, không chỉ có sự xuất hiện của khoảng trắng thừa mà còn kèm theo nhiều ký tự không thể in được như carriage return, line feed, tab ngang, tab dọc.

Mặc dù hàm TRIM có khả năng xóa sạch khoảng trắng, nhưng lại không thể loại bỏ những ký tự không in được đó. Hàm TRIM về cơ bản chỉ được thiết lập để xóa bỏ ký tự số 32 trong bảng mã ASCII 7-bit, chính là khoảng trắng thông thường.

Để có thể loại bỏ cả khoảng trắng và ký tự không in được trong Excel, bạn nên dùng sự kết hợp của hàm TRIM cùng hàm CLEAN. Hàm CLEAN có khả năng ‘lau chùi’ dữ liệu, loại bỏ hoàn toàn 32 ký tự không in được từ bảng mã ASCII 7-bit (từ mã 0 đến 31), bao gồm cả ngắt dòng (có mã 10).

Giả sử bạn cần xử lý dữ liệu nằm ở ô A2, bạn có thể sử dụng công thức như sau:

=TRIM(CLEAN(A2))

Nếu việc áp dụng công thức TRIM/CLEAN khiến cho nội dung các dòng dính lại với nhau và mất đi những khoảng trắng, bạn hãy sửa lỗi này bằng một số cách sau đây:

  • Sử dụng tính năng Replace All của Excel: trong khung Find what, nhập vào ký tự carriage return
    • Để xóa tất cả các dấu ngắt dòng trong Word và thay thế chúng bằng khoảng trắng, ta bấm tổ hợp phím Ctrl + J, đồng thời nhập một khoảng trắng vào ô Replace with. Khi nhấn vào Replace All, tất cả dấu ngắt dòng sẽ biến mất, được thế chỗ bằng khoảng trắng.
    • Sử dụng công thức dưới đây để chuyển ký tự xuống dòng (mã 10) và đầu dòng mới (mã 13) thành dấu cách:

    =SUBSTITUTE(SUBSTITUTE(A2, CHAR(13), ” “), CHAR(10), ” “)

    LOẠI BỎ KHOẢNG TRẮNG KHÔNG NGẮT (NON-BREAKING SPACE) TRONG EXCEL

    Khi bạn phát hiện ra rằng các khoảng trắng cứng đầu vẫn tồn tại sau khi áp dụng công thức TRIM & CLEAN, có khả năng chúng đến từ dữ liệu sao chép từ nguồn bên ngoài và bao gồm khoảng trắng không ngắt.

    Để xoá khoảng trắng loại này và thay thế bằng khoảng trắng thông thường, bạn có công thức dùng hàm TRIM sau đây:

    =TRIM(SUBSTITUTE(A2, CHAR(160), ” “))

    Cấu tạo của công thức trên gồm:

    • Ký tự khoảng trắng không ngắt được mã hoá là 160 trong bảng mã ASCII 7-bit, nên chúng ta có thể dùng CHAR(160) để biểu thị ký tự đó.
    • Hàm SUBSTITUTE giúp thay khoảng trắng không ngắt sang dạng khoảng trắng thông thường.
    • Sau cùng, sử dụng hàm TRIM nhằm gộp công thức SUBSTITUTE vào để loại bỏ các khoảng trắng thông thường mà khoảng trắng không ngắt đã chuyển đổi sang.

    Để xử lý các ký tự không thể in được trong bảng, hãy kết hợp hàm CLEAN với TRIM và SUBSTITUTE để loại bỏ các khoảng trắng cũng như ký tự không mong đợi với chỉ một công thức:

    =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), ” “)))

    Bạn có thể thấy sự khác biệt qua hình ảnh dưới đây:

    XOÁ BỎ MỘT KÝ TỰ KHÔNG IN ĐƯỢC CỤ THỂ

    Khi ba hàm TRIM, CLEAN và SUBSTITUTE không giải quyết được việc loại bỏ các ký tự không in được và khoảng trắng, điều này nghĩa là table của bạn có chứa các ký tự ASCII nằm ngoài phạm vi từ 0 đến 32, và không phải là ký tự 160 (khoảng trắng không ngắt).

    Bạn có thể dùng hàm CODE để tìm ra mã của ký tự, sau đó áp dụng SUBSTITUTE để thay thế ký tự đó bằng khoảng trắng thông thường và dùng TRIM để loại bỏ khoảng trắng.

    Nếu như ô A2 chứa ký tự khó chịu mà bạn muốn bài trừ, viết công thức như sau:

    1. Dùng công thức CODE trong ô B2 để tìm mã ký tự:
    • Nếu ký tự không in được hoặc khoảng trắng nằm ở đầu chuỗi văn bản:

    =CODE(LEFT(A2, 1))

    • Đối với ký tự hoặc khoảng trắng nằm ở cuối chuỗi:

    =CODE(RIGHT(A2, 1))

    • Ký tự không in được hoặc khoảng trắng tại một vị trí bất kỳ giữa chuỗi, với n là vị trí ký tự đó:

    =CODE(MID(A2, n, 1))

    Với ký tự không in được nằm giữa văn bản ở vị trí số 4, mã của nó được xác định bằng công thức:

    =CODE(MID(A2, 4, 1))

    Kết quả từ hàm CODE cho ta biết mã ký tự là 127 (như hình dưới).

    1. Thay thế ký tự này bằng khoảng trắng thông thường trong ô C2 bằng cách sử dụng CHAR(127), sau đó dùng TRIM để loại bỏ khoảng trắng đó:

    =TRIM(SUBSTITUTE(A2, CHAR(127), ” “))

    Đây là kết quả sau khi thực hiện:

    Nếu dữ liệu của bạn có chứa nhiều ký tự không thể in được khác nhau, hãy dùng hàm SUBSTITUTE với nhiều lớp để loại bỏ chúng cùng lúc.

    =TRIM(SUBSTITUTE(SUBSTITUTE(A2, CHAR(127), ” “), CHAR(160), ” “))

    LOẠI BỎ TẤT CẢ KHOẢNG TRẮNG TRONG EXCEL

    Đôi khi bạn cần phải xóa tất cả các khoảng trắng trong Excel, kể cả những khoảng cách giữa chữ và số. Ví dụ, trong trường hợp nhập số liệu có khoảng trắng phân cách giữa các hàng nghìn để dễ nhìn, nhưng việc này lại làm bạn không thực hiện được việc tính toán bằng công thức.

    Để xoá hết các khoảng trắng chỉ trong một thao tác, bạn có thể áp dụng hàm SUBSTITUTE giống như ví dụ dưới đây, chỉ khác là bạn sẽ thay khoảng trắng được định nghĩa bởi mã CHAR(32) bằng một chuỗi rỗng (“”).

    =SUBSTITUTE(A2, CHAR(32), “”)

    Bạn cũng có thể đặt khoảng trắng vào giữa hai dấu ngoặc kép (” “), như sau:

    =SUBSTITUTE(A2, ” “, “”)

    Kế đến, bạn hãy thay công thức bằng giá trị thực tế và các con số sẽ hiển thị mà không có bất kỳ sự nhầm lẫn nào.

    TÍNH TỔNG SỐ KHOẢNG TRẮNG TRONG EXCEL

    Nếu bạn muốn biết có bao nhiêu khoảng trắng trong một ô nào đó trước khi loại bỏ chúng, hãy thực hiện theo các bước sau:

    • Xác định độ dài của chuỗi bằng cách sử dụng hàm LEN: LEN(A2)
    • Loại bỏ tất cả các khoảng trắng bằng cách thay thế chúng: SUBSTITUTE(A2, ” “, “”)
    • Đo độ dài mới của chuỗi sau khi đã được loại bỏ khoảng trắng: LEN(SUBSTITUTE(A2, ” “, “”))
    • Tính hiệu số giữa độ dài của chuỗi ban đầu và chuỗi đã loại bỏ khoảng trắng.

    Bạn có thể sử dụng công thức đầy đủ dưới đây với giả định rằng chuỗi văn bản gốc nằm ở ô A2:

    =LEN(A2) – LEN(SUBSTITUTE(A2, ” “, “”))

    Để xác định số khoảng trắng thừa, bạn trừ độ dài sau khi loại bỏ chúng khỏi tổng độ dài chuỗi ban đầu:

    =LEN(A2) – LEN(TRIM(A2))

    Dưới đây là hình minh họa cho cả hai phương pháp trên:

    Sau khi xác định được số lượng khoảng trắng trong mỗi ô, bạn có capability loại bỏ những khoảng trắng thừa đó bằng cách dùng hàm TRIM.

    – – – – –

    Thêm vào đó, để áp dụng Excel một cách hiệu quả trong công việc, bạn phải thành thạo các hàm và công cụ khác nhau của Excel.

    Cần phải nắm vững một số hàm cơ bản thường gặp bao gồm:

    • SUMIF, SUMIFS để tính tổng dựa trên một hoặc nhiều điều kiện
    • COUNTIF, COUNTIFS để đếm theo một hoặc nhiều tiêu chí
    • Hàm xử lý chuỗi, ngày tháng và số liệu…
    • Hàm tra cứu INDEX+MATCH, hàm SUMPRODUCT…

    Một vài công cụ thường được sử dụng gồm:

    • Conditional formatting để định dạng theo điều kiện
    • Data Validation để thiết lập điều kiện nhập liệu
    • Cách đặt và sử dụng Name trong công thức
    • Việc xây dựng báo cáo sử dụng Pivot Table…

    Bạn thấy đấy, có rất nhiều điều cần phải học về Excel. Tất cả những kiến thức này đều có thể được học trong khóa học SM90 – Excel từ cơ bản đến chuyên gia do Siêu Marketing cung cấp. Khóa học này giúp bạn tổ chức lại kiến thức một cách toàn diện và chi tiết. Ngoài ra, không có giới hạn thời gian học, nên bạn có thể tự học mọi lúc và dễ dàng tìm lại kiến thức khi cần. Hệ thống hiện đang có một ưu đãi đặc biệt cho những người đăng ký khóa học. Xem chi tiết tại: sieumarketing.com

    Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop