Hàm SUBSTITUTE có thể tìm và thay thế văn bản trong một ô, bất kể văn bản đó xuất hiện ở đâu. Trong trường hợp này, chúng tôi đang sử dụng SUBSTITUTE để tìm một ký tự có mã số 202 và thay thế nó bằng một chuỗi rỗng [""] để loại bỏ ký tự hoàn toàn một cách hiệu quả
Làm cách nào bạn có thể tìm ra [những] ký tự nào cần phải xóa khi chúng ẩn?
=CODE[LEFT[B4]]
Ở đây, hàm LEFT, không có đối số thứ hai tùy chọn, sẽ trả về ký tự đầu tiên bên trái. Điều này đi vào hàm CODE, báo cáo giá trị mã ký tự, là 202 trong ví dụ minh họa
Để làm sạch tổng quát hơn, hãy xem chức năng TRIM và chức năng CLEAN
Tất cả trong một công thức
Trong trường hợp này, vì chúng tôi đang loại bỏ các ký tự đầu, nên chúng tôi có thể kết hợp cả hai công thức thành một, như vậy
=SUBSTITUTE[B4,CHAR[CODE[LEFT[B4]]],""]
Ở đây, thay vì cung cấp ký tự 202 một cách rõ ràng cho SUBSTITUTE, chúng tôi đang sử dụng CODE và CHAR để cung cấp mã động, sử dụng ký tự đầu tiên trong ô
Nhiều khi cơ sở dữ liệu chứa một số ký tự đặc biệt mà chúng tôi không cần trong cơ sở dữ liệu và chúng tôi muốn xóa chúng. Chúng ta có thể dễ dàng thực hiện công việc này với sự trợ giúp của các công cụ và công thức Excel. Bài viết sẽ giải thích 4 cách khác nhau sẽ chỉ ra cách loại bỏ các ký tự đặc biệt trong Excel
Mục lục
Tải sách bài tập thực hành
Để thực hành, bạn có thể tải về cuốn sách thực hành từ liên kết dưới đây
Xóa ký tự đặc biệt. xlsx
4 phương pháp để loại bỏ các ký tự đặc biệt trong Excel
Chúng tôi sẽ sử dụng tập dữ liệu sau để giải thích các cách
Bộ dữ liệu chứa Tên và Địa chỉ Thư của khách hàng của một công ty. Bạn có thể nhận thấy Ô B8 chứa một công thức và nó hiển thị một giá trị không in được cùng với tên của khách hàng "Rachel". Một lần nữa, chúng ta có thể thấy có một số ký tự đặc biệt cùng với tất cả dữ liệu. Chúng ta sẽ xem cách loại bỏ các ký tự đặc biệt này trong Excel bằng cách sử dụng các cách sau
1. Xóa các ký tự đặc biệt trong Excel bằng công thức Excel
Excel có các công thức hữu ích mà bạn có thể sử dụng để loại bỏ các ký tự đặc biệt trong Excel. Chúng được hình thành bằng cách sử dụng các chức năng như SUBSTITUTE, RIGHT, LEFT, CLEAN, TRIM và REPLACE. Chúng ta sẽ xem xét từng cái một
a. Sử dụng hàm SUBSTITUTE
Hãy bắt đầu với hàm SUBSTITUTE. Nó được sử dụng để thay thế một ký tự bằng một ký tự khác
Giả sử bạn muốn xóa các ký tự đặc biệt khỏi ô B5 của tập dữ liệu đã cho
Công thức để loại bỏ các ký tự cụ thể bằng SUBSTITUTE sẽ là
=SUBSTITUTE[B5,"!#$$",""]
Tại đây, bạn có thể nhận thấy rằng các ký tự cụ thể được đề cập trong ô đã bị xóa. Nó hoạt động tuần tự. Do đó, ký tự "#" vẫn ở đầu
Một lần nữa, bạn có thể loại bỏ các ký tự đệ quy bằng cách sử dụng các số mẫu
Công thức sẽ là
=SUBSTITUTE[B5,"#","",2]
Quan sát rằng "#" thứ hai liên tiếp đã bị xóa trong khi cái đầu tiên vẫn còn nguyên vẹn
Tuy nhiên, bạn có thể muốn xóa tất cả các ký tự chỉ giữ tên
Lần này công thức sẽ được lồng SUBSTITUTE trong chính nó. Công thức sẽ giống như
=SUBSTITUTE[SUBSTITUTE[SUBSTITUTE[B5,"#",""],"!",""],"$",""]
Điều này cho thấy kết quả hoàn hảo cho trường hợp này
Mô tả công thức
Cú pháp của công thức
________số 8
text=văn bản mà bạn muốn làm việc với
old_text= văn bản mà bạn muốn xóa
new_text= văn bản được thay thế. [Đối với trường hợp của chúng tôi, chúng tôi thay thế nó bằng khoảng trống “ “]
instance_name= số ký tự đặc biệt trong trường hợp có ký tự đệ quy trong văn bản
Trương hợp đặc biệt
Các ký tự đặc biệt chứa mã số và chúng ta có thể lấy mã số của chúng bằng công thức
=CODE[RIGHT[text]]
hoặc
=SUBSTITUTE[B4,CHAR[CODE[LEFT[B4]]],""]
Hàm RIGHT hoặc LEFT dùng để lấy vị trí của nhân vật có mã mà bạn muốn lấy
Do đó quá trình này bao gồm hai bước
- Nhận Code bằng công thức CODE lồng với RIGHT hoặc LEFT
- Sử dụng công thức SUBSTITUTE và thay cho old_text hãy viết CHAR[number]
Để biết kết quả, hãy làm theo các hình ảnh bên dưới cùng với các công thức
=SUBSTITUTE[B4,CHAR[CODE[LEFT[B4]]],""]
=SUBSTITUTE[B5,"!#$$",""]0
=SUBSTITUTE[B5,"!#$$",""]1
=SUBSTITUTE[B5,"!#$$",""]2
Hơn nữa, nếu tìm thấy các ký tự tương tự, quy trình này sẽ xóa cả hai. Quan sát kết quả bên dưới
=SUBSTITUTE[B5,"!#$$",""]3
=SUBSTITUTE[B5,"!#$$",""]4
=SUBSTITUTE[B5,"!#$$",""]5
=SUBSTITUTE[B5,"!#$$",""]6
b. Sử dụng hàm RIGHT hoặc LEFT
Xem xét, bạn đã thấy ở trên cách sử dụng các hàm RIGHT và LEFT. Chúng có thể được sử dụng với hàm LEN để xóa các ký tự cụ thể trong Excel
Công thức sẽ là
=SUBSTITUTE[B5,"!#$$",""]7
Bạn có thể tăng các giá trị lên bất kỳ số nào và trừ nó bằng LEN[text] để xóa một lượng ký tự đặc biệt cụ thể
Đối với điều này, công thức là
=SUBSTITUTE[B5,"!#$$",""]8
Tương tự cho công thức LEFT,
=SUBSTITUTE[B5,"!#$$",""]9
Và để tăng số phiên bản, công thức đã thay đổi
"#"0
Mô tả công thức
Cú pháp của công thức
"#"1
text= văn bản từ nơi bạn muốn xóa ký tự
num_chars= số ký tự cần xóa
"#"2
text= văn bản có độ dài bạn muốn đếm
-1 hoặc -[bất kỳ số nào] là số ký tự bạn muốn trừ khỏi tổng số ký tự trong văn bản
c. Sử dụng chức năng CLEAN và TRIM
Tập dữ liệu của bạn cũng có thể chứa các ký tự không in được và không gian thừa. Chức năng CLEAN và TRIM có thể được sử dụng để loại bỏ chúng
Công thức để loại bỏ ký tự không in được là
"#"3
Để xóa các ký tự không in được cùng với khoảng trắng thừa, bạn có thể sử dụng công thức
"#"4
Tuy nhiên, bạn có thể làm cả hai bằng cách lồng TRIM và CLEAN với SUBSTITUTE. Công thức sẽ giống như
"#"5
Thực hiện theo các hình ảnh dưới đây
Mô tả công thức
Cú pháp của công thức riêng lẻ
"#"6
Ở đây, văn bản = văn bản từ nơi bạn muốn xóa ký tự không in được
"#"7
văn bản = văn bản từ nơi cần xóa thêm khoảng trắng
________số 8
text=văn bản mà bạn muốn làm việc với
old_text= văn bản mà bạn muốn xóa
new_text= văn bản được thay thế. [Đối với trường hợp của chúng tôi, chúng tôi thay thế nó bằng khoảng trống “ “]
instance_name= số ký tự đặc biệt trong trường hợp có ký tự đệ quy trong văn bản
d. Sử dụng hàm REPLACE
Hơn nữa, có một công thức khác sử dụng hàm REPLACE để xóa một lượng ký tự cụ thể sau một số ký tự
công thức là
"#"9
Ở đây công thức khá giống với SUBSTITUTE. Phải mất thêm 2 đối số có tên start_num [ số mà các ký tự cần được loại bỏ]
num_chars [số ký tự cần xóa]
Và nó không lấy văn bản làm đối số cần thiết cho SUBSTITUTE
Công thức cho tập dữ liệu đã cho là xóa các ký tự đặc biệt sau “#Sen“
=SUBSTITUTE[B5,"#","",2]0
Đọc thêm. Cách xóa các ký tự cụ thể trong Excel
2. Sử dụng Flash Fill để xóa các ký tự đặc biệt trong Excel
Tiếp tục với các công cụ Excel. Flash Fill là cách đơn giản nhất để loại bỏ các ký tự đặc biệt trong Excel
Giả sử chúng ta có tên và địa chỉ thư của khách hàng trong cùng một cột và chúng được phân tách bằng dấu phẩy. Chúng tôi muốn xóa các văn bản sau dấu phẩy bao gồm cả dấu phẩy. Thực hiện theo các bước để biết cách dùng Flash Fill xóa ký tự đặc biệt trong Excel
- Viết văn bản đầu tiên không có ký tự đặc biệt
- Bắt đầu viết văn bản thứ hai và bạn sẽ thấy Excel đang hiển thị các văn bản được đề xuất. Quan sát hình bên dưới
- Nhấn ENTER từ bàn phím. Điều này sẽ hiển thị kết quả như dưới đây
Đọc thêm. Cách xóa ký tự trống trong Excel
3. Sử dụng Lệnh Tìm & Thay thế để Xóa các Ký tự Đặc biệt
Một công cụ hữu ích khác của Excel là Find & Replace
Giả sử chúng tôi muốn xóa “Mailto. ” trước địa chỉ trong cột có tên Mail Address của tập dữ liệu
Thực hiện theo các bước bên dưới để xóa các ký tự đặc biệt bằng Tìm & Thay thế
- Chọn Thay thế từ Tìm & Thay thế. Làm theo hình bên dưới để lấy Find & Replace từ Editing options của tab Home
- Một hộp thoại sẽ mở ra. Viết ký tự bạn muốn xóa trong Find what. hộp và giữ Thay thế bằng. hộp trống. Xem hình bên dưới
- Nhấp vào Thay thế tất cả và một hộp mới sẽ mở ra. Nó sẽ hiển thị số lần thay thế được thực hiện
- Nhấp vào OK
Bạn sẽ thấy kết quả như sau
Đọc thêm. Cách xóa dấu cách trong Excel. Với Công thức, VBA & Power Query
4. Xóa các ký tự đặc biệt bằng Power Query Tool
Chắc chắn nếu bạn đang sử dụng Microsoft Excel 2016 hay Excel 365 thì có thể sử dụng Power Query để xóa ký tự đặc biệt trong Excel
Trong trường hợp bạn đang sử dụng Microsoft Excel 2010 hoặc 2013, bạn có thể cài đặt nó từ trang web của Microsoft
Bạn có thể làm theo các bước để sử dụng Power Query để xóa các ký tự đặc biệt khỏi tập dữ liệu của mình
- Chọn phạm vi dữ liệu của bạn cùng với tiêu đề
- Sau đó chọn Từ bảng/Phạm vi từ tab Dữ liệu
- Bạn sẽ tìm thấy một hộp nhỏ. Kiểm tra phạm vi dữ liệu bạn đã chọn và đánh dấu tùy chọn Bảng của tôi có tiêu đề
- Sau đó, bấm OK
Một cửa sổ mới có tên Cửa sổ Power Query sẽ mở ra
- Chọn Cột tùy chỉnh từ tab Thêm cột trong cửa sổ Power Query
- Nó sẽ mở hộp Cột tùy chỉnh
- Viết “Không có ký tự đặc biệt” trong tùy chọn Tên cột mới. Bạn có thể viết bất kỳ tên nào bạn muốn
- Sau đó, viết công thức bên dưới vào tùy chọn Công thức cột tùy chỉnh
Công thức
=SUBSTITUTE[B5,"#","",2]1
- Sau đó, bấm OK
Một cột mới sẽ được tạo và công thức mới của bạn sẽ được hiển thị trên thanh công thức của cửa sổ
- Chọn Đóng và Tải từ tab Tệp của cửa sổ
Bạn sẽ tìm thấy một trang tính mới trong sổ làm việc của mình, nơi bạn sẽ thấy kết quả cuối cùng như được hiển thị ở đây
Bạn có thể nhận thấy quá trình này không xóa các ký tự “^^” khỏi ô D7. Đó là bởi vì Excel xem xét ký tự trong danh mục “. . " nhân vật
Những điều cần ghi nhớ
Thật không may, nếu bạn đang sử dụng các phiên bản Microsoft Excel cũ hơn 2010, bạn có thể không cài đặt được Power Query. Bạn chỉ có thể sử dụng tính năng này với các phiên bản từ 2010 đến mới nhất
Dù sao, tất cả các phương pháp đều có ưu và nhược điểm, vì vậy hãy sử dụng chúng một cách khôn ngoan theo yêu cầu của bạn
Phần kết luận
Bài viết mô tả 4 cách khác nhau để loại bỏ các ký tự đặc biệt trong Excel. Chúng tôi đã sử dụng các công thức và công cụ Excel để giải thích 4 cách khác nhau để loại bỏ các ký tự đặc biệt trong Excel. để làm điều này. Tóm lại, các công thức bao gồm các hàm như SUBSTITUTE, CLEAN, RIGHT, CODE, v.v. Mặt khác, các công cụ được sử dụng là Flash Fill, Find & Replace và Power Query. Tôi hy vọng bài viết này hữu ích cho bạn. Đối với bất kỳ truy vấn thêm, viết trong phần bình luận