3
Sử dụng chức năng Thay thế để thay đổi khoảng trắng không ngắt thành khoảng trắng thông thường. Ví dụ: "=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))" không có dấu ngoặc kép sẽ thay thế khoảng trắng không ngắt (ký tự ASCII 160) trong ô "A1" bằng khoảng trắng tiêu chuẩn (ký tự ASCII 32) và
dòng dưới cùng. Tìm hiểu 5 kỹ thuật khác nhau để tìm và xóa khoảng trắng trong Excel. Khoảng cách có thể gây ra lỗi công thức và gây khó chịu cho việc chuẩn bị và phân tích dữ liệu
Cấp độ kỹ năng. Trung cấp
Xem hướng dẫn
Xem trên YouTube và Đăng ký Kênh của chúng tôiTải xuống tệp Excel
Bạn có thể tải xuống workbook mà tôi sử dụng trong video, bao gồm cả macro tôi đã viết để loại bỏ các khoảng trống
Xóa khoảng trống TRƯỚC. xlsmXóa khoảng trắng SAU. xlsmsăn không gian
Khoảng trống có thể làm đau đầu các nhà phân tích dữ liệu và người viết công thức. Đó là bởi vì chúng không dễ phát hiện, nhưng có thể gây ra lỗi tính toán khó chịu
Sau nhiều năm làm việc với Excel, tôi đã trở thành Thợ săn không gian. Điều đó nghe giống như một tiêu đề phim khoa học viễn tưởng, nhưng đó chỉ là một người có thể nhanh chóng xác định vị trí và xóa các khoảng trống thừa trong các giá trị ô
Dựa trên kinh nghiệm săn tìm không gian của tôi, đây là năm kỹ thuật tôi thường sử dụng để dọn sạch dữ liệu
1. Tìm thay thế
Phương pháp đầu tiên để săn không gian là sử dụng tính năng Tìm & Thay thế
Sau khi đánh dấu các ô mà bạn muốn tìm kiếm, hãy chuyển đến tab Trang chủ. Sau đó mở menu thả xuống Tìm & Chọn. Chọn tùy chọn Thay thế. Phím tắt cho việc này là Ctrl + H
Thao tác đó sẽ hiển thị cửa sổ Tìm và Thay thế. Trên tab Thay thế, đặt một khoảng trống trong trường Tìm gì. Đảm bảo không có gì trong trường Thay thế bằng. Nhấn Replace All (phím tắt. Alt+A) sẽ xóa mọi phiên bản của khoảng trắng trong tập dữ liệu mà bạn đã chọn
Mặc dù phương pháp này thực sự nhanh chóng và dễ dàng, nhưng nó chỉ hữu ích cho dữ liệu mà bạn muốn loại bỏ TẤT CẢ khoảng trắng. Tuy nhiên, có những lúc bạn muốn giữ khoảng cách giữa các từ. Ví dụ: nếu các ô của bạn chứa cả họ và tên, có thể bạn sẽ muốn giữ khoảng trắng ở giữa các tên đó
Kịch bản đó dẫn chúng ta đến phương pháp thứ hai để săn tìm không gian
2. Chức năng TRIM
Hàm TRIM loại bỏ tất cả các khoảng trắng trong một chuỗi văn bản, ngoại trừ các khoảng trắng đơn lẻ giữa các từ
Dưới đây là một ví dụ về một báo cáo trong đó một số ô trông giống như được thụt vào nhưng thực ra phần thụt vào chỉ là khoảng trắng thừa. Giả sử chúng ta muốn xóa những khoảng trắng đó vì mục đích đồng nhất. Chúng ta có thể sử dụng chức năng TRIM để làm điều đó
Trong một cột trống, hãy bắt đầu bằng cách nhập dấu bằng (=) và từ TRIM, sau đó tab vào hàm TRIM
Điều duy nhất bạn cần xác định để chức năng TRIM hoạt động là văn bản cần cắt. Vì vậy, hãy nhập tên của ô hoặc nhấp vào ô mà bạn muốn cắt và nhấn Enter. Điều này sẽ trả về kết quả bạn đang tìm kiếm. văn bản không có dấu cách trước hoặc sau nó
Copy công thức xuống cả cột thì được kết quả như này
Như bạn có thể thấy, khoảng cách giữa các từ vẫn được giữ nguyên, nhưng bất kỳ khoảng cách nào trước hoặc sau văn bản đều bị xóa
Với phương pháp này, chúng tôi phải tạo một cột hoàn toàn mới, vì vậy bạn sẽ phải thêm bước bổ sung là thay thế cột ban đầu bằng cột mới bằng cách sử dụng Sao chép và Dán Giá trị. Sau đó, bạn có thể xóa cột bằng các công thức
3. Truy vấn nguồn
Giống như các hàm và công thức, các truy vấn cũng có thể bị vấp khi bao gồm một khoảng trống đi lạc
Nếu bạn không chắc liệu khoảng trống có phải là nguyên nhân gây ra sự cố hay không, bạn có thể nhấp vào phần xem trước của Trình chỉnh sửa để xem liệu có khoảng trống hay không
Để xóa khoảng trắng, Power Query có tính năng Cắt trong menu chuột phải. Với cột mà bạn muốn sửa đã chọn, chỉ cần nhấp chuột phải và chọn Chuyển đổi và Cắt
Thao tác này cắt bỏ tất cả khoảng trống trước/sau chuỗi văn bản để truy vấn sẽ trả về giá trị chính xác
Nếu bạn còn khá mới đối với Power Query hoặc có thể sử dụng tài liệu ôn tập, tôi khuyên bạn nên xem qua hội thảo trên web miễn phí của mình. Bản thiết kế Excel hiện đại
4. Macro và VBA
Hãy xem ví dụ tương tự mà chúng ta đã sử dụng cho hàm TRIM, nhưng lần này chúng ta sẽ sử dụng macro để cắt các khoảng trắng thừa khỏi văn bản
Bắt đầu bằng cách mở VB Editor. Chuyển đến tab Nhà phát triển và chọn Nút Visual Basic. Phím tắt là Alt + F11
Tôi đã viết một macro đơn giản lặp qua từng ô và thay thế giá trị của ô bằng giá trị đã cắt. Nếu bạn muốn sao chép macro này, bạn có thể tìm thấy nó trong tệp tải xuống ở đầu bài đăng này
Một trong những lợi ích của việc sử dụng macro là bạn có thể gán nó cho một nút. Ở đây tôi đã gán nó cho một nút trên trang tính thực tế
Và ở đây, tôi đã thêm nó vào dải băng bằng cách đưa macro này vào Sổ làm việc Macro Cá nhân của tôi và gán một nút cho nó
Bằng cách đó, tôi có thể sử dụng macro này trên bất kỳ sổ làm việc nào
Một nhược điểm của việc sử dụng phương pháp macro để xóa khoảng trắng là bạn không thể hoàn tác hành động sau khi macro đã được chạy. Trong trường hợp đó, bạn nên lưu tệp trước khi chạy macro, nếu bạn cho rằng mình có thể cần bỏ cắt bớt dữ liệu vì lý do nào đó
Cũng cần lưu ý rằng chức năng VBA Trim KHÔNG cắt các khoảng trống thừa giữa các từ. Hàm TRIM của Excel sẽ loại bỏ các khoảng trắng bổ sung giữa các từ. Vì vậy, nếu có hai khoảng trắng giữa họ và tên, TRIM sẽ xóa khoảng trắng thừa. Trim của VBA sẽ không làm điều này. Nó chỉ xóa khoảng trắng ở đầu hoặc cuối chuỗi. Cảm ơn Marvin (Thành viên cộng đồng Excel Campus) đã thông báo cho chúng tôi về vấn đề này
Dưới đây là các hướng dẫn về cách tạo Sổ làm việc Macro Cá nhân cũng như cách tạo các nút Macro, cho dù trên trang tính hay trong dải băng
Cách tạo Sổ làm việc Macro cá nhân (Chuỗi video)
Cách tạo các nút macro trong bảng tính Excel
5. Cắt tỉa các ký tự khác
Đôi khi sau khi bạn đã cắt bớt khoảng trắng khỏi văn bản của mình, bạn vẫn có thể gặp lỗi tính toán. Một lý do cho điều này có thể là một nhân vật khó phát hiện khác có thể đang chơi. Một ký tự như vậy là không gian không phá vỡ. Điều này thường được sử dụng trong mã hóa HTML ( ) và tương tự như một khoảng trắng thông thường, nhưng sẽ không bị hàm TRIM hoặc macro của chúng tôi xóa
Để xác định ký tự nào có thể đang ăn cắp tác phẩm, bạn có thể sử dụng chức năng CODE. Bằng cách sao chép và dán ký tự vô hình vào một ô trống, sau đó xác định văn bản của ô đó trong hàm CODE (“văn bản” là đối số duy nhất cần được xác định cho hàm CODE), bạn sẽ trả về một mã số cho ký tự đó. Tìm kiếm nhanh trên Google về số mã đó sẽ tiết lộ loại ký tự bạn đang xử lý
Trong trường hợp này, ký tự khoảng cách không ngắt là mã 160
Một cách để xóa ký tự đó khỏi văn bản của bạn là sử dụng Tìm & Thay thế. Bạn chỉ cần sao chép ký tự và dán vào trường Tìm gì
Bạn cũng có thể sử dụng chức năng REPLACE để làm điều tương tự
Một tùy chọn khác, vì chúng tôi biết rằng chúng tôi muốn xóa ký tự cuối cùng trong văn bản, nên sử dụng các hàm LEFT và LEN để tạo một tính năng cắt tùy chỉnh về cơ bản
TRÁI trả về các ký tự ngoài cùng bên trái trong một ô, lên đến một số lượng được chỉ định. LEN (viết tắt của Length) trả về số ký tự trong một ô
Vì vậy, kết hợp hai hàm đó và trừ 1 khỏi LEN để xóa ký tự không mong muốn đó sẽ chỉ để lại văn bản mà không có ký tự bổ sung
Sự kết luận
Tôi hy vọng điều này đã được hữu ích cho bạn. Tôi cũng giải thích cách dấu cách có thể gây ra lỗi trong công thức trong video của tôi về Giới thiệu về VLOOKUP
Trên thực tế, có nhiều hơn năm cách để thực hiện nhiệm vụ này và nếu bạn muốn thêm vào danh sách, vui lòng để lại. Như mọi khi, bạn cũng có thể
Cảm ơn vì đã theo dõi. Cho đến lần sau
