Hướng dẫn how to remove extra spaces between numbers in excel - cách xóa khoảng cách thừa giữa các số trong excel

Hướng dẫn giải thích cách loại bỏ các không gian trống trong Excel bằng các công thức và công cụ công cụ văn bản. Bạn sẽ học cách xóa các không gian hàng đầu và theo dõi trong một ô, loại bỏ các không gian bổ sung giữa các từ, loại bỏ không gian trắng không phá vỡ và các ký tự không in.

Vấn đề lớn nhất với không gian là gì? Chúng thường vô hình trước mắt người. Một người dùng chu đáo thỉnh thoảng có thể bắt được một không gian hàng đầu ẩn trước văn bản hoặc một vài khoảng trống giữa các từ. Nhưng không có cách nào để phát hiện không gian dấu vết, những người tránh xa tầm nhìn ở cuối tế bào.

Sẽ không có vấn đề gì nếu thêm không gian chỉ nằm xung quanh, nhưng chúng làm rối tung các công thức của bạn. Vấn đề là hai ô chứa cùng một văn bản có và không có khoảng trắng, ngay cả khi nó ít như một ký tự không gian, được coi là các giá trị khác nhau. Vì vậy, bạn có thể đang cố gắng tìm ra lý do tại sao một công thức rõ ràng đúng không thể phù hợp với hai mục dường như giống hệt nhau.

Bây giờ bạn hoàn toàn nhận thức được vấn đề, đã đến lúc phải giải quyết một giải pháp. Có một số cách để loại bỏ khoảng trống khỏi chuỗi và hướng dẫn này sẽ giúp bạn chọn kỹ thuật phù hợp nhất cho nhiệm vụ cụ thể của bạn và loại dữ liệu bạn đang làm việc.

Cách loại bỏ các khoảng trống trong Excel - Dẫn đầu, Trailing, giữa các từ

Nếu bộ dữ liệu của bạn chứa các không gian thừa, hàm trang trí Excel có thể giúp bạn xóa tất cả chúng trong một lần đi, dẫn đầu và nhiều không gian ở giữa, ngoại trừ một ký tự không gian giữa các từ.

Một công thức trang trí thông thường đơn giản như thế này:

=TRIM(A2)

Trong đó A2 là ô bạn muốn xóa không gian.

Như được hiển thị trong ảnh chụp màn hình sau, công thức trang trí Excel đã loại bỏ thành công tất cả các không gian trước và sau khi văn bản cũng như không gian liên tiếp ở giữa một chuỗi.

Và bây giờ, bạn chỉ cần thay thế các giá trị trong cột gốc bằng các giá trị được cắt. Cách dễ nhất để làm điều này là sử dụng Dán đặc biệt> Các giá trị, các hướng dẫn chi tiết có thể được tìm thấy ở đây: Cách sao chép các giá trị trong Excel.

Ngoài ra, bạn có thể sử dụng chức năng trang trí Excel để chỉ loại bỏ các không gian hàng đầu, giữ cho tất cả các không gian ở giữa một chuỗi văn bản nguyên vẹn. Ví dụ về công thức là ở đây: Cách cắt các không gian hàng đầu trong Excel (trang trí bên trái).leading spaces only, keeping all spaces in the middle of a text string intact. The formula example is here: How to trim leading spaces in Excel (Left Trim).

Cách xóa các lỗi dòng và các ký tự không in

Khi bạn nhập dữ liệu từ các nguồn bên ngoài, không chỉ các không gian bổ sung xuất hiện, mà còn các ký tự không in khác nhau như trả lại vận chuyển, nguồn cấp dữ liệu, tab dọc hoặc ngang, v.v.

Hàm trang trí có thể thoát khỏi không gian trắng, nhưng nó không thể loại bỏ các ký tự không in. Về mặt kỹ thuật, trang trí Excel được thiết kế để chỉ xóa giá trị 32 trong hệ thống ASCII 7 bit, là ký tự không gian.

Để loại bỏ khoảng trắng và các ký tự không in trong một chuỗi, hãy sử dụng trang trí kết hợp với hàm sạch. Như tên của nó cho thấy, Clean được mục đích để làm sạch dữ liệu và nó có thể xóa bất kỳ và tất cả 32 ký tự không in đầu tiên trong bộ ASCII 7 bit (giá trị 0 đến 31) bao gồm ngắt dòng (giá trị 10).line break (value 10).

Giả sử dữ liệu được làm sạch có trong ô A2, công thức như sau:

=TRIM(CLEAN(A2))

Nếu công thức trang trí/sạch sẽ nối các nội dung của nhiều dòng không có khoảng trống, bạn có thể sửa nó bằng cách sử dụng một trong các kỹ thuật sau:

• Sử dụng tính năng "thay thế tất cả" của Excel: trong hộp "Tìm cái gì", nhập trở lại vận chuyển bằng cách nhấn phím tắt Ctrl+J; và trong hộp "Thay thế bằng", nhập không gian. Nhấp vào nút Thay thế tất cả sẽ hoán đổi tất cả các ngắt dòng trong phạm vi đã chọn cho khoảng trắng.
• Sử dụng công thức sau để thay thế trả về vận chuyển (giá trị 13) và các ký tự nguồn cấp dữ liệu (giá trị 10) với khoảng trắng:

  =SUBSTITUTE(SUBSTITUTE(A2, CHAR(13)," "), CHAR(10), " ")

Để biết thêm thông tin, vui lòng xem cách loại bỏ trả lại vận chuyển (ngắt dòng) trong Excel.

Cách loại bỏ không gian không phá vỡ trong Excel

Nếu sau khi sử dụng Công thức Trim & Clean, một số không gian cứng đầu vẫn còn đó, rất có thể bạn sao chép/dán dữ liệu từ đâu đó và một vài không gian không phá vỡ đã lẻn vào.

Để loại bỏ các không gian không phá vỡ (ký tự HTML & nbsp;), thay thế chúng bằng không gian thông thường và sau đó có chức năng trang trí loại bỏ chúng:

=TRIM(SUBSTITUTE(A2, CHAR(160), " "))

Để hiểu rõ hơn về logic, hãy phá vỡ công thức:

• Một ký tự không phá vỡ có giá trị 160 trong hệ thống ASCII 7 bit, do đó bạn có thể xác định nó bằng cách sử dụng công thức char (160).
• Hàm thay thế được sử dụng để biến không gian không phá vỡ thành không gian thông thường.
• Và cuối cùng, bạn nhúng câu lệnh thay thế vào hàm trang trí để loại bỏ các không gian được chuyển đổi.

Nếu bảng tính của bạn cũng chứa các ký tự không in, hãy sử dụng chức năng sạch cùng với trang trí và thay thế để loại bỏ không gian và các biểu tượng không mong muốn trong một cú đánh rơi:

=TRIM(CLEAN((SUBSTITUTE(A2,CHAR(160)," "))))

Ảnh chụp màn hình sau đây cho thấy sự khác biệt:

Cách xóa một ký tự không in cụ thể

Nếu liên lạc của 3 chức năng được thảo luận trong ví dụ trên (Trim, Clean và thay thế) không thể loại bỏ khoảng trắng hoặc các ký tự không in trong trang của bạn, điều đó có nghĩa là các ký tự đó có các giá trị ASCII khác với 0 đến 32 (ký tự không in ) hoặc 160 (không gian không phá vỡ).

Trong trường hợp này, sử dụng hàm mã để xác định giá trị ký tự, sau đó sử dụng thay thế để thay thế nó bằng một không gian thông thường và cắt để loại bỏ không gian.

Giả sử các không gian hoặc các ký tự không mong muốn khác mà bạn muốn loại bỏ cư trú trong ô A2, bạn viết 2 công thức:

1. Trong ô B2, phát hiện giá trị ký tự có vấn đề bằng cách sử dụng một trong các hàm mã sau:
   • Không gian hàng đầu hoặc ký tự không in ở đầu chuỗi:

     =CODE(LEFT(A2,1))

   • Không gian kéo dài hoặc ký tự không in ở cuối chuỗi:

     =CODE(RIGHT(A2,1))

   • Không gian hoặc ký tự không in ở giữa chuỗi, trong đó n là vị trí của ký tự có vấn đề:

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

   Trong ví dụ này, chúng tôi có một số ký tự không in không rõ ở giữa văn bản, ở vị trí thứ 4 và chúng tôi tìm ra giá trị của nó với công thức này:

   ________số 8

   Hàm mã trả về giá trị 127 (vui lòng xem ảnh chụp màn hình bên dưới).

2. Trong ô C2, bạn thay thế char (127) bằng một không gian thông thường (""), sau đó cắt không gian đó:

   =TRIM(SUBSTITUTE(A2, CHAR(127), " "))

Kết quả sẽ trông giống như thế này:

Nếu dữ liệu của bạn chứa một vài ký tự không in khác nhau cũng như các không gian không phá vỡ, bạn có thể làm tổ hai hoặc nhiều chức năng thay thế để xóa tất cả các mã ký tự không mong muốn tại một thời điểm:

=TRIM(CLEAN(A2))0

Cách loại bỏ tất cả các không gian trong Excel

Trong một số tình huống, bạn có thể muốn loại bỏ hoàn toàn tất cả các không gian trắng trong một ô, bao gồm các không gian đơn giữa các từ hoặc số. Ví dụ: khi bạn đã nhập một cột số trong đó các khoảng trắng được sử dụng làm dấu phân cách hàng ngàn, giúp dễ đọc các số lớn hơn, nhưng ngăn các công thức của bạn tính toán.

Để xóa tất cả các không gian trong một lần, hãy sử dụng thay thế như được giải thích trong ví dụ trước, với sự khác biệt duy nhất là bạn thay thế ký tự không gian được trả về bởi char (32) không có gì (""):

=TRIM(CLEAN(A2))1

Hoặc, bạn có thể chỉ cần nhập không gian ("") vào công thức, như thế này:

=TRIM(CLEAN(A2))2

Sau đó, thay thế các công thức bằng các giá trị và số của bạn sẽ tính toán đúng.

Cách đếm không gian trong Excel

Trước khi loại bỏ không gian khỏi một ô nhất định, bạn có thể tò mò muốn biết có bao nhiêu trong số chúng thực sự ở đó.

Để có được tổng số lượng không gian trong một ô, hãy làm như sau:

• Tính toàn bộ độ dài chuỗi bằng hàm LEN: LEN (A2)
• Thay thế tất cả các không gian không có gì: thay thế (A2, "", "" ")
• Tính độ dài của chuỗi không có khoảng trắng: Len (thay thế (A2, "", "")))
• Trừ chuỗi chiều dài "không có không gian" từ tổng chiều dài.

Giả sử chuỗi văn bản gốc nằm trong ô A2, công thức hoàn chỉnh diễn ra như sau:

=TRIM(CLEAN(A2))3

Để tìm hiểu xem có bao nhiêu khoảng trống trong ô, hãy lấy chiều dài văn bản mà không cần thêm không gian, và sau đó trừ nó khỏi tổng chiều dài chuỗi:extra spaces are in the cell, get the text length without extra spaces, and then subtract it from the total string length:

=TRIM(CLEAN(A2))4

Ảnh chụp màn hình sau đây cho thấy cả hai công thức trong hành động:

Bây giờ bạn đã biết có bao nhiêu khoảng trống mà mỗi ô chứa, bạn có thể xóa các không gian bổ sung một cách an toàn bằng cách sử dụng công thức trang trí.

Cách không có công thức để xóa không gian và dữ liệu sạch

Như bạn đã biết, nhiều không gian bổ sung và các ký tự không mong muốn khác có thể ẩn nấp không được chú ý trong bảng của bạn, đặc biệt nếu bạn nhập dữ liệu của mình từ các nguồn bên ngoài. Bạn cũng biết cách xóa không gian trong Excel với một công thức. Tất nhiên, học một số ít các công thức là một bài tập tốt để làm sắc nét các kỹ năng của bạn, nhưng nó có thể tốn thời gian.

Người dùng Excel đánh giá cao thời gian của họ và đánh giá cao sự tiện lợi có thể tận dụng các công cụ văn bản được bao gồm trong bộ cuối cùng của chúng tôi cho Excel. Một trong những công cụ tiện dụng này cho phép loại bỏ các khoảng trắng và các ký tự không in trong một nút bấm.

Sau khi được cài đặt, Ultimate Suite sẽ thêm một số nút hữu ích vào ruy băng Excel của bạn như trang trí, xóa ký tự, chuyển đổi văn bản, định dạng rõ ràng và một vài nút nữa.

Bất cứ khi nào bạn muốn tháo các khoảng trống trong các tấm excel của mình, hãy thực hiện 4 bước nhanh sau:

1. Chọn các ô (phạm vi, toàn bộ cột hoặc hàng) nơi bạn muốn xóa các không gian bổ sung.
2. Nhấp vào nút Trim Spaces trên tab Dữ liệu ABLEBITS.
3. Chọn một hoặc một số tùy chọn:
   • Xóa không gian hàng đầu và dấu vếtleading and trailing spaces
   • Cắt tỉa các phần tử giữa các từ thành mộtextra spaces between words to one
   • Xóa không gian không phá vỡ (& NBSP;)non-breaking spaces ( )
4. Nhấp vào nút cắt.

Xong! Tất cả các không gian bổ sung bị xóa trong một lần nhấp.

Đây là cách bạn có thể nhanh chóng loại bỏ không gian trong các ô Excel. Nếu bạn tò mò khám phá các khả năng khác, bạn có thể tải xuống một phiên bản đánh giá của Ultimate Suite. Tôi cảm ơn bạn đã đọc và mong được gặp bạn trên blog của chúng tôi vào tuần tới!

Bạn cũng có thể quan tâm

Công thức để loại bỏ các không gian bổ sung từ một ô trong Excel là gì?

Công thức để loại bỏ các không gian bổ sung là gì?