Làm cách nào để loại bỏ khoảng trắng thừa trong excel?

Hàm TRIM trong Excel loại bỏ khoảng trắng ở đầu, khoảng trắng thừa và dấu cách ở cuối. Sử dụng hàm SUBSTITUTE để xóa tất cả khoảng trắng hoặc khoảng trắng không ngắt

1. Hàm TRIM bên dưới loại bỏ 2 khoảng trắng ở đầu, 3 khoảng trắng thừa và 2 dấu cách ở cuối

Ghi chú. hàm TRIM không xóa các khoảng trắng giữa các từ

2. Để lấy độ dài của một chuỗi, hãy sử dụng hàm LEN

Giải trình. hàm LEN đếm 2 dấu cách, 2 ký tự, 4 dấu cách, 3 ký tự và 2 dấu cách

3. Bạn có thể sử dụng chức năng SUBSTITUTE để loại bỏ tất cả khoảng trắng

Ghi chú. hàm SUBSTITUTE thay thế khoảng trắng (đối số thứ hai) bằng các chuỗi rỗng (đối số thứ ba)

Văn bản được nhập từ các ứng dụng khác có thể chứa các ký tự ASCII. Hàm CLEAN xóa 32 ký tự ASCII không in được đầu tiên (mã từ 0 đến 31)

4. Hàm CLEAN bên dưới loại bỏ một ký tự không in được, CHAR(7)

5. Chỉ cần kết hợp CLEAN và TRIM để xóa các ký tự và khoảng trắng không in được

6. Chức năng CLEAN loại bỏ ngắt dòng. Để nhập ngắt dòng, hãy nhấn ALT + ENTER

7. Bạn cũng có thể sử dụng hàm SUBSTITUTE để xóa dấu ngắt dòng, CHAR(10) và thay thế bằng một thứ khác. Ví dụ: dấu phẩy và dấu cách

Hàm CLEAN không xóa tất cả các ký tự ASCII. Chỉ cần sử dụng hàm SUBSTITUTE để xóa các ký tự ASCII khác

8. Đầu tiên, tìm ra mã số của ký tự

9. Tiếp theo, sử dụng SUBSTITUTE và CHAR để trả về chuỗi văn bản không có ký tự này

10. Nếu văn bản của bạn chứa khoảng trắng không ngắt, CHAR(160) thì hàm TRIM không hoạt động

11. Bạn có thể sử dụng hàm SUBSTITUTE để thay thế khoảng trắng không ngắt, CHAR(160) bằng khoảng trắng bình thường, CHAR(32). Đừng quên thêm chức năng TRIM để xóa khoảng trắng thừa

Mọi thứ đều ổn và công thức sẽ hoạt động. Nhưng vì một số lý do không xác định, một số ít kết quả bị lỗi

Lần đầu tiên điều này xảy ra với tôi, tôi đã dành nhiều thời gian để cố gắng tìm ra vấn đề chỉ để phát hiện ra một số dữ liệu tra cứu có thêm các ký tự khoảng trắng được đính kèm ở cuối

Điều này xảy ra rất nhiều. Dữ liệu có thể có các ký tự khoảng trắng ở đầu hoặc ở cuối. Nó thậm chí có thể có nhiều khoảng cách giữa các từ không nên có

Bây giờ chúng ta đã biết vấn đề, làm thế nào chúng ta có thể khắc phục nó?

Trong bài đăng này, chúng tôi sẽ xem xét nhiều phương pháp khác nhau để xóa các ký tự khoảng trắng thừa không mong muốn này khỏi dữ liệu của chúng tôi

Dữ liệu mẫu của chúng tôi cho bài viết

Đối với bài đăng này, chúng tôi sẽ xem xét một bộ dữ liệu đơn giản. Nó chỉ là “Xin chào thế giới. ” lặp đi lặp lại nhưng với nhiều khoảng trống khác nhau được thêm vào

1. Không có thêm không gian ở đây. Đây chỉ là một biện pháp kiểm soát, để đảm bảo khi chúng tôi áp dụng bản sửa lỗi của mình, chúng tôi không làm hỏng thứ gì đó đúng
2. Có vẻ ổn, nhưng thực tế có 5 dấu cách được thêm vào cuối
3. Có 5 khoảng trắng được thêm vào đầu. Không gian hàng đầu dễ dàng phát hiện hơn nhiều
4. Ở đây, chúng tôi có cả ký tự khoảng trắng ở đầu và cuối trong dữ liệu
5. Có một vài dấu cách giữa các từ. Trong trường hợp này là hiển nhiên, vì tôi đã thêm 5 dấu cách giữa các từ. Nếu chúng tôi chỉ có 2 khoảng trắng thì có thể khó phát hiện hơn nhiều tùy thuộc vào phông chữ được sử dụng
6. Điều này có tất cả ba khả năng. Dẫn đầu, theo sau và giữa

Hãy xem làm thế nào chúng ta có thể khắc phục tất cả những điều này

Loại bỏ khoảng trắng thừa bằng chức năng TRIM

Phương pháp đầu tiên chúng ta sẽ xem xét là phương pháp đơn giản nhất

Không gian thừa là một vấn đề phổ biến đến nỗi có một chức năng để loại bỏ (hoặc cắt) chúng. Đó là chức năng TRIM

Nó làm chính xác những gì nó có thể làm. Nó cắt bỏ mọi khoảng trống thừa từ dữ liệu. Nó xóa tất cả các khoảng trắng khỏi một chuỗi văn bản ngoại trừ các khoảng trắng đơn lẻ giữa các từ

Hàm TRIM có một đầu vào bắt buộc và đây là văn bản mà chúng tôi muốn cắt bớt khoảng trắng thừa. Đầu vào có thể là tham chiếu ô như B2 hoặc chuỗi được mã hóa cứng như ” Hello World. “

TRIM(B2) sẽ trả lại nội dung của ô B2 với các khoảng trắng thừa đã bị xóa

TRIM(" Hello World! ") sẽ trả về “Xin chào thế giới. “

Loại bỏ khoảng trắng thừa giữa các từ bằng Tìm và Thay thế

Phương pháp tiếp theo sẽ sử dụng tính năng tìm kiếm và thay thế của Excel

Điều này sẽ chỉ hoạt động để xóa khoảng trắng thừa giữa các từ

Trước tiên, chúng ta cần chọn cơn thịnh nộ của các ô mà chúng ta muốn xóa không gian thừa

Sau đó chuyển đến tab Trang chủ ➜ Phần chỉnh sửa ➜ nhấn nút Tìm & Chọn ➜ chọn tùy chọn Thay thế từ menu

Bạn cũng có thể sử dụng phím tắt Ctrl + H

Cả hai sẽ mở menu Tìm & Thay thế và chúng tôi có thể nhấn nút Tùy chọn để có thêm các tùy chọn tìm và thay thế trước

Trong phần Find what, chúng ta cần thêm hai ký tự khoảng trắng. Chúng ta có thể nhấn phím cách hai lần trên bàn phím

Trong phần Replace with, chúng ta cần thêm một ký tự khoảng trắng. Chúng ta có thể nhấn phím Space một lần trên bàn phím

Chúng ta có thể biết các ký tự khoảng trắng nằm trong vùng nhập liệu vì con trỏ sẽ di chuyển sang phải mỗi khi chúng ta nhấn phím cách

Đảm bảo rằng các tùy chọn nâng cao khác được chọn là Trong trang tính và Tìm trong công thức

Nhấn nút Thay thế tất cả. Vì có các chuỗi 5 khoảng trắng trong dữ liệu của chúng tôi, chúng tôi sẽ cần nhấn nút Thay thế tất cả một vài lần cho đến khi không còn tìm thấy bất kỳ ký tự khoảng trắng kép nào trong dữ liệu

Điều này sẽ để lại một khoảng trắng ở đầu hoặc cuối dữ liệu nếu có bất kỳ khoảng trắng nào ở đó

Loại bỏ khoảng trắng thừa bằng Power Query

Power Query là tất cả về chuyển đổi dữ liệu, do đó, nó sẽ có thể xử lý tác vụ này

Chọn dữ liệu và chuyển đến tab Data ➜ nhấn lệnh From Table/Range

Thao tác này sẽ biến dữ liệu của chúng ta thành Bảng Excel, nếu nó chưa được định dạng thành một

Chúng tôi có thể đảm bảo rằng phạm vi dữ liệu đã chọn là chính xác và điều chỉnh nó nếu cần. Dữ liệu của chúng tôi không có bất kỳ tiêu đề cột nào, vì vậy chúng tôi sẽ bỏ chọn hộp Bảng của tôi có tiêu đề

Nhấn nút OK để tạo bảng và trình chỉnh sửa truy vấn nguồn sẽ mở ra

Nhấp chuột phải vào tiêu đề cột ➜ chọn Transform ➜ chọn Trim

Thật không may, điều này không loại bỏ bất kỳ khoảng trắng thừa nào giữa các từ và chúng tôi cần thực hiện một vài bước chuyển đổi Giá trị thay thế để thực hiện điều này

Nhấp chuột phải vào cột và chọn Thay thế giá trị từ menu

Giống như phương pháp tìm và thay thế, chúng ta sẽ cần thêm hai khoảng trắng làm Giá trị cần tìm và một khoảng trắng làm giá trị Thay thế bằng. Sau đó, chúng ta có thể nhấn nút OK để thêm chuyển đổi này

Chúng tôi cần lặp lại bước này cho đến khi tất cả các khoảng trống thừa đã bị xóa khỏi dữ liệu của chúng tôi

Để lấy lại dữ liệu đã làm sạch vào Excel, chúng ta cần đóng và tải dữ liệu

Chúng ta có thể chuyển đến tab Trang chủ trong trình chỉnh sửa truy vấn nguồn ➜ nhấn Đóng và Tải ➜ chọn Đóng và Tải vào ➜ chọn Bảng và chọn vị trí để tải bảng

Xóa khoảng cách thừa giữa các từ bằng VBA

VBA cũng có chức năng cắt được tích hợp sẵn

Sub TrimSpaces()

Dim rng As Range
Dim cell As Range
Dim TempCell As String

Set rng = Selection

For Each cell In rng
    TempCell = cell.Value
    TempCell = Trim(TempCell)
    cell.Value = TempCell
Next cell

End Sub

Đoạn mã trên sẽ lặp qua từng ô trong phạm vi đã chọn và áp dụng hàm VBA Trim cho giá trị bên trong ô

Tương tự như chuyển đổi Power query Trim, nó sẽ chỉ xóa khoảng trắng ở đầu và cuối của bất kỳ văn bản nào

Có cách nào để loại bỏ khoảng trắng thừa trong Excel không?

Công thức để loại bỏ khoảng trắng thừa khỏi một ô trong Excel là gì?