Hầu hết dữ liệu bạn đưa vào Excel thường đến từ nhiều nguồn khác nhau. Nhiều lần chúng được chuyển đổi từ một định dạng khác. Do đó, không có gì lạ khi tìm thấy các ký tự không mong muốn bên trong dữ liệu chuỗi văn bản
Điều khó chịu hơn là đôi khi những ký tự không mong muốn này lại vô hình. Vì vậy, chúng không mang lại kết quả như mong đợi khi các thao tác được thực hiện trên chúng. Một vấn đề phổ biến khác là sự hiện diện của các khoảng trống không nhất quán
Trong hướng dẫn này, chúng tôi sẽ giải quyết tất cả các vấn đề trên bằng cách sử dụng các hàm, công thức và tính năng của Excel. Chúng ta sẽ xem cách sử dụng các chức năng khác nhau của Excel để xóa các ký tự cụ thể khỏi chuỗi trong ô của bạn
Mục lục
Xóa một ký tự cụ thể bằng tính năng tìm và thay thế
Hộp thoại Tìm và Thay thế của Excel là một cách tuyệt vời để tìm các mục trên trang tính của bạn và hoàn thành công việc một cách nhanh chóng
Tất cả những gì bạn cần làm là nhập chuỗi tìm kiếm của bạn để chỉ định những gì bạn muốn thay thế và sau đó chỉ định những gì bạn muốn thay thế bằng
Giả sử bạn có tập dữ liệu bên dưới và bạn muốn xóa tất cả các ký tự '@' khỏi chuỗi văn bản trong mỗi ô
How to Remove the Last Digit in Excel?
Chỉ xóa một Trường hợp cụ thể của một ký tự cụ thể trong chuỗi
Bây giờ, nếu bạn chỉ muốn xóa biểu tượng '@' đầu tiên khỏi mỗi ô, thay vì tất cả các phiên bản của chúng thì sao?
Đây là lúc tham số tùy chọn cuối cùng của hàm SUBSTITUTE có ích
Sử dụng điều này, bạn có thể chỉ định trường hợp nào của biểu tượng bạn muốn xóa. Vì vậy, để xóa phiên bản đầu tiên của ký hiệu, chức năng của bạn phải là
SUBSTITUTE [original_string, old_character, “”,1]
Tương tự, nếu bạn muốn loại bỏ phiên bản thứ hai của ký tự, chức năng sẽ là
SUBSTITUTE [original_string, old_character, “”,2]
Hãy xem các bước để chỉ xóa phiên bản đầu tiên của biểu tượng '@' khỏi tập dữ liệu trên
How to Remove First Character in Excel?
Xóa bất kỳ ký tự đặc biệt nào bằng hàm CLEAN
Hàm Excel CLEAN loại bỏ các ngắt dòng và các ký tự không in được khỏi một chuỗi. Cú pháp chung của hàm này là
________số 8_______Ở đây, original_string là văn bản hoặc tham chiếu đến ô văn bản mà bạn muốn xóa
Kết quả là văn bản đã bị loại bỏ tất cả các ký tự không in được
Chúng ta hãy xem tập hợp các chuỗi sau
Vì danh sách được đưa vào từ một ứng dụng khác nên cuối cùng nó có rất nhiều ký tự không cần thiết, chẳng hạn như ký tự xuống dòng, dấu cách, v.v.
Hãy xem cách sử dụng chức năng CLEAN để làm sạch dữ liệu này
- Chọn ô đầu tiên của cột mà bạn muốn kết quả xuất hiện. Trong ví dụ của chúng tôi, nó sẽ là ô B2
- Nhập công thức.
=CLEAN[A2].
- Nhấn phím quay lại
- Điều này sẽ cung cấp cho bạn văn bản thu được sau khi loại bỏ tất cả các ngắt dòng khỏi chuỗi trong ô A2
- Bấm đúp vào núm điều khiển điền [nằm ở góc dưới cùng bên trái] của ô B2. Điều này sẽ sao chép công thức vào tất cả các ô khác của cột B
Lưu ý rằng các ký tự dòng mới đã bị xóa, nhưng kết quả vẫn không ổn. Điều này là do khi dữ liệu được đưa vào, nó cũng chứa một số ký tự khoảng trắng, bên cạnh các dòng mới
Hàm CLEAN chỉ xóa 32 ký tự đầu tiên [không in được] trong mã ASCII 7 bit [i. e. giá trị 0 đến 31]. Tuy nhiên, có những ký tự không in được khác trong Unicode mà CLEAN không thể loại bỏ
Vì ký tự khoảng trắng có giá trị là 32 nên hàm CLEAN không loại bỏ khoảng trắng. Vì vậy, tốt nhất là áp dụng chức năng TRIM sau khi áp dụng chức năng CLEAN để xóa khoảng trắng
Loại bỏ các ký tự khoảng cách ở đầu hoặc ở cuối bằng hàm TRIM
Rất nhiều công việc dọn dẹp dữ liệu chỉ bao gồm việc loại bỏ các ký tự khoảng trắng ở đầu hoặc ở cuối khỏi chuỗi. Hàm TRIM trong Excel giúp thực hiện điều này dễ dàng chỉ trong một lần
Hàm TRIM xóa ký tự khoảng trắng [“ “] khỏi văn bản. Nếu các khoảng trắng ở đầu hoặc cuối các khoảng trắng, nó sẽ xóa tất cả chúng. Nếu có thêm khoảng cách giữa các từ, thì nó sẽ loại bỏ phần thừa và chỉ để lại một khoảng trắng
Cú pháp chung của hàm này là
=SUBSTITUTE [original_string, old_character, “”]0
Ở đây, original_string là văn bản hoặc tham chiếu đến ô văn bản mà bạn muốn xử lý
Hãy để chúng tôi sử dụng chức năng TRIM để xóa các ký tự khoảng trắng còn sót lại sau khi áp dụng chức năng CLEAN
- Chọn ô đầu tiên của cột mà bạn muốn kết quả xuất hiện. Trong ví dụ của chúng tôi, nó sẽ là ô C2
- Nhập công thức.
=SUBSTITUTE [original_string, old_character, “”]
1 - Nhấn phím quay lại
- Điều này sẽ cung cấp cho bạn văn bản thu được sau khi loại bỏ tất cả các khoảng trắng không cần thiết khỏi chuỗi trong ô B2
- Bấm đúp vào núm điều khiển điền [nằm ở góc dưới cùng bên trái] của ô C2. Điều này sẽ sao chép công thức vào tất cả các ô khác của cột C
- Nếu bạn chỉ muốn giữ lại các phiên bản đã chuyển đổi của văn bản, hãy chọn các ô này [C2. C5], sao chép và dán chúng vào cùng một vị trí với các giá trị
- Sau đó, bạn có thể xóa cột A và B nếu cần
Xóa một ký tự vô hình cụ thể khỏi một chuỗi bằng các hàm SUBSTITUTE, CHAR và CODE
Trong một số trường hợp, cả hàm CLEAN và TRIM đều không thể xóa một số ký tự đặc biệt khó chịu khỏi chuỗi
Điều này có thể là do các ký tự này không phải là khoảng trắng cũng không phải là một trong 32 ký tự mà chức năng CLEAN có thể xóa
Bạn có thể tìm mã của một ký tự bằng chức năng CODE. Ví dụ, trong văn bản mẫu dưới đây, có một ký tự vô hình ở đầu chuỗi
Vì nó không thể bị xóa bằng TRIM hoặc CLEAN, nên rõ ràng đó không phải là một không gian thông thường
Đây là những gì chúng ta có thể làm để loại bỏ tất cả các phiên bản của ký tự vô hình
- Để biết ký tự này là gì, chúng ta có thể sử dụng chức năng CODE. Nhập hàm này vào ô B2. =CODE[TRÁI[A2]]. Vì ký tự nằm ở vị trí đầu tiên trong văn bản nên chúng ta có thể dễ dàng tìm ra mã của ký tự đó bằng hàm LEFT. Trong trường hợp này, chúng tôi nhận được kết quả là “160”. Điều đó có nghĩa là mã của ký tự vô hình là 160
- Hãy để chúng tôi sử dụng giá trị này trong hàm SUBSTITUTE. Nhập hàm này vào ô C2. =SUBSTITUTE[A2,CHAR[B2],””]. Ở đây, chúng tôi đã sử dụng hàm CHAR để chuyển đổi mã ký tự trở lại ký tự tương đương của nó
- Khi bạn nhấn phím quay lại ngay bây giờ, bạn sẽ thấy tất cả các phiên bản của ký tự vô hình đó đã bị xóa khỏi chuỗi
Trong hướng dẫn này, chúng ta đã thấy cách bạn có thể sử dụng các hàm, công thức và tính năng khác nhau của Excel để xóa các ký tự cụ thể khỏi một chuỗi
Nếu bạn biết ký tự mình muốn xóa là gì, bạn có thể sử dụng tính năng Tìm và Thay thế hoặc chức năng SUBSTITUTE
Để loại bỏ các khoảng trống và các ký tự đặc biệt [thường đi kèm với dữ liệu được đưa vào từ các ứng dụng khác], bạn có thể sử dụng các chức năng CLEAN và TRIM tương ứng
Nếu có các ký tự ẩn khác trong chuỗi và bạn không biết các ký tự đó là gì, thì bạn có thể sử dụng công thức kết hợp các hàm CODE, CHAR và SUBSTITUTE với nhau
Chúng tôi đã cố gắng tập hợp tất cả các tình huống có thể xảy ra khi bạn cần xóa một ký tự cụ thể khỏi văn bản trong Excel