Đôi khi, chúng tôi không thể sử dụng dữ liệu của mình theo cách chúng tôi muốn. Và một trong những lý do lớn nhất khiến dữ liệu của chúng tôi không thể sử dụng được là…
Vì vậy nếu muốn sử dụng chúng ta cần gỡ bỏ tất cả. Khi bạn nghĩ về điều này, hầu hết thời gian khi bạn lấy dữ liệu từ hệ thống ERP hoặc bạn có một số công thức được viết không chính xác, bạn sẽ nhận được những khoảng trắng không mong muốn đó
Vì vậy, mỗi khi bạn làm việc với dữ liệu của mình, trước tiên bạn cần xác minh dữ liệu đó
Và trong bài viết hôm nay, tôi sẽ chỉ cho bạn 4 phương pháp chính xác để loại bỏ tất cả những khoảng trắng thừa và không mong muốn đó.
Đảm bảo TẢI XUỐNG TỆP MẪU này từ đây để theo dõi. Bây giờ, hãy bắt đâù
1. Loại bỏ các khoảng trắng thừa bằng chức năng TRIM [Đang theo dõi và dẫn đầu]
Đôi khi, bạn có khoảng trắng ở cuối và ở đầu trong dữ liệu của mình và nó trông rất khó chịu
Nhưng chúng tôi có một phương pháp rất đơn giản và dễ dàng để loại bỏ đống rác này và bạn chỉ cần một chức năng để xử lý nó.
Đó là TRIM
Nó loại bỏ tất cả các khoảng trắng không mong muốn khỏi một chuỗi văn bản ngoại trừ khoảng cách giữa hai từ, hãy xem ví dụ bên dưới
Mục lục
Chúng ta đang giải quyết vấn đề gì?
- Khoảng trắng thừa – Khoảng trắng thừa là khoảng trống mà chúng tôi không yêu cầu trong dữ liệu của mình. Chúng có thể có nhiều số lượng và cũng có các loại [đã đề cập ở trên]
- Khoảng trắng hàng đầu – là khoảng trắng ở đầu văn bản trong một ô
- Trailing Spaces – là khoảng trắng ở cuối văn bản trong một ô
- Khoảng trắng ở giữa Văn bản – là khoảng trắng thừa trong khoảng cách giữa các từ. Bạn có thể chỉ cần một khoảng trắng hoặc không cần khoảng trống nào cả
- Ngắt dòng – là các dòng mới [được nhập thủ công bằng phím Alt + Enter]
- Nonprinting Characters – là các ký tự được hiển thị cho thiết kế nội dung nhưng không được hiển thị trong bản in
Tại sao lại có những vấn đề này?
Trong khi khai thác hoặc phân tích dữ liệu, thật khó để tìm ra văn bản vì chúng tôi đang tìm kiếm "A" nhưng không thể tìm thấy nó vì văn bản trong ô là "ký tự khoảng trắng". Đây là một trong nhiều vấn đề. Vì vậy, hãy lưu lại tính thẩm mỹ và phân tích của trang tính và tìm hiểu cách loại bỏ các ký tự không mong muốn
Sử dụng hàm TRIM để xóa khoảng trắng thừa
Hàm TRIM xóa tất cả khoảng trắng khỏi chuỗi văn bản ngoại trừ khoảng trắng đơn lẻ giữa các từ. Hàm TRIM sẽ loại bỏ tất cả các khoảng trắng ở đầu và cuối và khoảng cách giữa các từ thừa. Điều này ngụ ý rằng nếu vô tình có thêm khoảng trắng làm hỏng một từ, hàm sẽ loại bỏ khoảng trắng thừa nhưng sẽ xử lý một khoảng trắng theo yêu cầu. Nếu bạn muốn xóa tất cả các ký tự khoảng trắng khỏi một ô, hãy chuyển đến một vài phân đoạn cuối cùng của hướng dẫn này
Bây giờ hãy xem hàm TRIM loại bỏ khoảng trắng ở đầu và cuối và khoảng cách giữa các từ thừa. Đây là công thức
=TRIM[B3]
Hàm chỉ cần một đối số, tham chiếu đến ô có văn bản, để thực hiện công việc của nó. Dưới đây, chúng tôi sẽ sử dụng hàm TRIM để loại bỏ tất cả các ký tự khoảng trắng không cần thiết khỏi danh sách tên
Hàm TRIM đã dọn sạch tất cả các khoảng trống không cần thiết khỏi dữ liệu ở trên. Nếu có bất kỳ dấu cách ngẫu nhiên nào chia tách tên e. g. "John" được chia thành "Jo hn" với 3 ký tự khoảng trắng ở giữa, hàm TRIM sẽ rút ngắn thành "Jo hn" với một ký tự khoảng trắng. Hàm xóa TẤT CẢ các khoảng trắng ở đầu và cuối và xóa tất cả các khoảng trắng thừa giữa văn bản & tiết kiệm một khoảng trắng
Sử dụng CLEAN và TRIM để loại bỏ khoảng trắng thừa và ngắt dòng [và các ký tự không in được khác]
Như đã thấy ở trên, hàm TRIM sẽ loại bỏ tất cả các khoảng trắng thừa trong một ô. Hàm CLEAN xóa tất cả các ký tự không in được khỏi văn bản. Cùng nhau, chúng ta sẽ sử dụng các hàm CLEAN và TRIM để xóa các khoảng trắng thừa, ngắt dòng và các ký tự không in được khác
Các chức năng kết hợp sẽ trông như thế này
=TRIM[CLEAN[B3]]
Các hàm TRIM và CLEAN đã được ghép nối với nhau để hoạt động trên một đối số; . Hàm CLEAN xóa B3 của tất cả các dấu ngắt dòng và các ký tự không in được, để lại tất cả các khoảng trắng thừa được chuyển vào hàm TRIM để xóa. Kết quả là một ô văn bản được sắp xếp gọn gàng với khoảng trắng phù hợp
Bạn có thể nghĩ rằng công thức sẽ vẫn hoạt động với các chức năng được chuyển đổi theo cách khác nhưng chúng tôi khuyên bạn nên tuân theo thứ tự này. Trước tiên, sử dụng hàm TRIM sẽ để lại các dấu ngắt dòng và các ký tự không in được. Việc sử dụng hàm CLEAN sau đó sẽ loại bỏ cả hai ô này nhưng đôi khi có thể để lại một khoảng trống; . Do đó, chúng tôi đề xuất công thức trên để có kết quả nhất quán
Sử dụng các hàm CLEAN, TRIM, & SUBSTITUTE để xóa tất cả các khoảng trắng khỏi một ô [ngoại trừ khoảng trắng từ đơn]
Tóm tắt nhanh; . Hàm SUBSTITUTE thay thế văn bản hiện có bằng văn bản mới trong chuỗi văn bản. Hàm SUBSTITUTE ở đây được sử dụng cho hai mục đích;
Chúng tôi sẽ chỉ cho bạn một công thức hoàn toàn độc quyền bên dưới để loại bỏ các ký tự sau
- không gian thêm,
- ngắt dòng,
- ký tự không in
Bạn có thể chọn ra các hàm mục tiêu của mình từ công thức và áp dụng chúng vào kịch bản của mình. Đây là công thức chúng tôi đã ghép lại với nhau
=TRIM[CLEAN[SUBSTITUTE[B3,CHAR[160]," "]]]
Trước khi chúng ta nắm bắt được công thức này, CHAR[160] là gì? . Nếu điều này vẫn không loại bỏ được các khoảng trắng cứng đầu, bạn có thể phải thử nghiệm với mã CHAR để xem chính xác ký tự nào bạn đang xử lý và cần loại bỏ
Khi hàm SUBSTITUTE đã xóa tất cả các khoảng trắng CHAR[160] khỏi B3, hàm CLEAN sẽ xóa tất cả các dấu ngắt dòng và các ký tự không in được. Được chuyển vào hàm TRIM, TRIM loại bỏ tất cả các ký tự khoảng trắng thừa, để lại một khoảng trắng giữa các tên
Bây giờ công thức này dùng để làm việc với văn bản mà bạn sẽ cần một khoảng trắng giữa các từ [e. g. tên, địa chỉ]. Đây là công việc chính của hàm TRIM, vì vậy nếu bạn muốn loại bỏ tất cả các khoảng trắng, công thức sẽ thay đổi một chút. Tất nhiên, điều đầu tiên cần thực hiện sẽ là chức năng TRIM. Phần còn lại của các chi tiết dưới đây
Sử dụng các hàm CLEAN và SUBSTITUTE để xóa tất cả các khoảng trắng khỏi một ô
Đôi khi có thể cần phải loại bỏ tất cả các khoảng trắng và ký tự đặc biệt không mong muốn để truy xuất văn bản trong một khối vững chắc [e. g. số điện thoại]. Chúng ta sẽ sử dụng một công thức tương tự như trên nhưng không có hàm TRIM. Khi hàm TRIM không còn nữa, chúng ta cần thay thế nó bằng một hàm sẽ xử lý tất cả các ký tự khoảng trắng và đó là lúc hàm SUBSTITUTE mở rộng sự trợ giúp của nó. Công thức chúng tôi đã đặt ra để loại bỏ tất cả các ký tự khoảng trắng, ngắt dòng và các ký tự không in được là
=CLEAN[SUBSTITUTE[SUBSTITUTE[C3," ",""],CHAR[160],""]]
Hàm SUBSTITUTE đã xuất hiện hai lần ở đây; . CHAR[160] là một không gian không phá vỡ. Nếu bạn vẫn gặp khoảng trắng liên tục sau khi sử dụng công thức này, bạn có thể cần tìm mã CHAR nào đang gây ra sự cố và điều chỉnh nó trong công thức
Hàm SUBSTITUTE khác sẽ thay thế tất cả các ký tự khoảng trắng bằng văn bản trống. công thức này
=SUBSTITUTE[C3," ",""]
có thể được sử dụng riêng để xóa tất cả các ký tự khoảng trắng thông thường khỏi một ô
Sau đó, hàm CLEAN sẽ xóa tất cả các ký tự không in được khỏi kết quả của hàm SUBSTITUTE, cung cấp một chuỗi số đã xóa tất cả các khoảng trắng và ký tự
Sử dụng tùy chọn Tìm và Thay thế để Xóa Tất cả Khoảng trắng
Tính năng Tìm và Thay thế có thể được sử dụng để xóa tất cả các khoảng trắng khỏi một ô. Chúng tôi cũng có thể sử dụng Tìm và Thay thế để xóa ngắt dòng nhưng mỗi lần thay thế phải được thực hiện riêng. Đây là cách xóa tất cả khoảng trắng và ngắt dòng khỏi một ô
- Chọn các ô từ nơi bạn muốn xóa khoảng trắng
- Trong nhóm Chỉnh sửa của tab Trang chủ, nhấp vào nút Tìm & Chọn và chọn Thay thế… từ danh sách. Thao tác này sẽ mở hộp thoại Tìm và Thay thế. Bạn cũng có thể sử dụng phím tắt Ctrl + H cho hành động này
- Trong trường Find what, nhập ký tự bạn muốn thay thế [e. g. ký tự khoảng trắng, gạch nối, dấu phẩy]. Chúng tôi đã nhập một ký tự khoảng trắng. Để trống trường Thay thế bằng để ký tự khoảng trắng có thể được thay thế bằng văn bản trống
- Nhấp vào Thay thế tất cả và sau đó nhấp vào OK trong hộp thoại xác nhận sẽ bật lên. Bây giờ tất cả các khoảng trắng đã được thay thế, chúng tôi cũng sẽ xóa các ngắt dòng
- Xóa trường Tìm gì trong hộp thoại Tìm và Thay thế [vẫn mở sau khi thực hiện lần thay thế đầu tiên] và nhấn Ctrl + J. Bạn sẽ chỉ thấy một chấm nhỏ nhấp nháy nhưng điều này sẽ thay thế ngắt dòng
- Để trống trường Thay thế bằng một lần nữa
- Nhấp vào Thay thế tất cả, nhấn OK trong cửa sổ bật lên xác nhận, đóng hộp thoại Tìm và Thay thế nếu hoàn tất
Nếu dữ liệu của bạn chưa được ngắt dòng, bạn có thể cần điều chỉnh độ cao hàng còn lại sau khi xóa ngắt dòng
Đã có dữ liệu được làm sạch sẵn sàng để sử dụng
Hết giờ rồi. Đó là tất cả về cách xóa khoảng trắng thừa trong Excel. Chúng tôi hy vọng đã giải quyết được một số vấn đề về dung lượng cứng đầu cho bạn để việc quét dữ liệu của bạn không còn là cơn ác mộng thủ công. Chúng tôi sẵn sàng giải quyết những cơn ác mộng thủ công hơn để mơ mộng về Excel mượt mà. Hẹn gặp bạn với các giải pháp