Hướng dẫn how do i remove special characters from an excel spreadsheet? - làm cách nào để xóa các ký tự đặc biệt khỏi bảng tính excel?

Quay trở lại danh sách công thức Excel

Tải xuống ví dụ sổ làm việc

Tải xuống sổ làm việc ví dụ

Hướng dẫn này sẽ trình bày bạn cách loại bỏ tất cả các ký tự đặc biệt khỏi các ô trong Excel và Google Sheets.

Xóa các ký tự đặc biệt

Excel không cung cấp bất kỳ chức năng nào để loại bỏ tất cả các ký tự đặc biệt khỏi chuỗi cùng một lúc. Nếu bạn chỉ muốn xóa một ký tự đặc biệt, bạn có thể sử dụng hàm thay thế [xem thêm trong bài viết này xóa các ký tự không mong muốn].

Nếu bạn muốn sử dụng một công thức duy nhất để xóa các ký tự đặc biệt, bạn sẽ cần sử dụng hàm do người dùng xác định [UDF], như RemoveSpecChar.

=RemoveSpecChar[B3]

Để làm cho UDF của bạn hoạt động, hãy mở Trình soạn thảo Visual Basic bằng cách đẩy Alt+F11. Nhấp chuột phải vào tên sách bài tập của bạn trong cửa sổ dự án ở phía bên trái và chèn một mô-đun mới. Vào mô -đun này sao chép UDF dưới đây:

Function RemoveSpecChar[sInput As String] As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®|.&@# [_+`©~];-+=^$!,'"
For i = 1 To Len[sSpecChar]
sInput = Replace$[sInput, Mid$[sSpecChar, i, 1], ""]
Next
RemoveSpecChar = sInput
End Function

Bây giờ UDF của bạn đã được xác định và sẵn sàng để sử dụng. Đừng quên lưu sổ làm việc của bạn dưới dạng sổ làm việc hỗ trợ macro [*.xlsm].

Để tìm hiểu thêm về UDFS, vui lòng đọc bài viết này: Các chức năng do người dùng xác định.

Nhiều lần 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 tôi có thể dễ dàng thực hiện nhiệm vụ 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 xóa các ký tự đặc biệt trong Excel.

Tải xuống Sổ làm việc thực hành

Để thực hành, bạn có thể tải xuống cuốn sách thực hành từ liên kết dưới đây.

4 Phương pháp về cách xóa các ký tự đặc biệt trong Excel

Chúng tôi sẽ sử dụng bộ 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ị giá trị không thể in cùng với tên của máy khách "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ả các dữ liệu. Chúng ta sẽ thấy 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.Names and Mail Addresses of clients of a company. You can notice Cell B8 contains a formula and it shows a non-printable value along with the name of the client "Rachel". Again, we can see there are some special characters along with all the data. We will see how to remove these special characters in Excel by using the following ways.

1. Loại bỏ 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ư thay thế, phải, trái, sạch, cắt và thay thế. Chúng tôi sẽ xem xét từng người một.SUBSTITUTE, RIGHT, LEFT, CLEAN, TRIM and REPLACE. We will look into each of them one by one.

một. Sử dụng chức năng thay thế

Hãy để chúng tôi bắt đầu với chức năng thay thế. Nó được sử dụng để thay thế một ký tự bằng một nhân vật khác.SUBSTITUTE function. It is used to replace a character with another.

Giả sử bạn muốn xóa các ký tự đặc biệt khỏi ô B5 của bộ dữ liệu đã cho.

Công thức để loại bỏ các ký tự cụ thể bằng cách sử dụng thay thế sẽ là:SUBSTITUTE will be:

=SUBSTITUTE[B5,"!#$$",""]

Ở đây bạn có thể nhận thấy rằng các ký tự cụ thể được đề cập trong ô được loại bỏ. Nó hoạt động tuần tự. Do đó, ký tự ____ 4 & nbsp; vẫn ở đầu."#" remains at the beginning.

Một lần nữa, bạn có thể loại bỏ các ký tự đệ quy bằng các số thể hiện.

Công thức sẽ là:

Quan sát rằng "#" thứ hai tuần tự đã được loại bỏ trong khi cái đầu tiên còn nguyên vẹn."#" has been removed while the first one is intact.

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 nhau thay thế trong chính nó. Công thức sẽ trông giống như:SUBSTITUTE within itself. The formula will look like:

=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:

=SUBSTITUTE[text, old_text, new_text, [instance_num]]

Văn bản = văn bản mà bạn muốn làm việc.=the text which you want to work with.

old_text = văn bản mà bạn muốn xóa.= text which you want to remove.

new_text = văn bản thay thế. [Đối với trường hợp của chúng tôi, chúng tôi thay thế nó bằng trống Trò chơi]. replaced text. [ For our case we replace it with blank “ “].

instance_name = số lượng ký tự đặc biệt trong trường hợp ký tự đệ quy có trong văn bản.= the number of the special character in case of recursive characters present in the text.

Trương hợp đặc biệt:

Các ký tự đặc biệt chứa số mã và chúng tôi có thể nhận số mã của chúng bằng công thức:

hoặc

Hàm phải hoặc bên trái được sử dụng để có được vị trí của ký tự bạn muốn nhận mã.RIGHT or LEFT function is used to get the location of the character whose code you want to get.

Do đó, quá trình này bao gồm hai bước:

• Nhận mã bằng cách sử dụng công thức mã lồng nhau với phải hoặc trái.CODE nested with RIGHT or LEFT.
• Sử dụng công thức thay thế và thay cho Old_Text Viết char [số].SUBSTITUTE formula and in place of old_text write CHAR[number].

Đối với kết quả, theo các hình ảnh dưới đây cùng với các công thức.

=SUBSTITUTE[C5,CHAR[109],""]

=SUBSTITUTE[C5,CHAR[77],""]

Hơn nữa, nếu các ký tự tương tự được tìm thấy quá trình này sẽ loại bỏ cả hai. Quan sát các kết quả dưới đây.

Function RemoveSpecChar[sInput As String] As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®|.&@# [_+`©~];-+=^$!,'"
For i = 1 To Len[sSpecChar]
sInput = Replace$[sInput, Mid$[sSpecChar, i, 1], ""]
Next
RemoveSpecChar = sInput
End Function

Function RemoveSpecChar[sInput As String] As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®|.&@# [_+`©~];-+=^$!,'"
For i = 1 To Len[sSpecChar]
sInput = Replace$[sInput, Mid$[sSpecChar, i, 1], ""]
Next
RemoveSpecChar = sInput
End Function

b. Sử dụng các chức năng phải hoặc trái

Xem xét, bạn đã thấy theo cách ở trên, việc sử dụng các chức năng bên phải và bên trái. Chúng có thể được sử dụng với hàm LEN để loại bỏ các ký tự cụ thể trong Excel. RIGHT and LEFT functions. These can be used with the LEN function to remove specific characters in Excel.

Công thức sẽ là:

Bạn có thể tăng các giá trị lên bất kỳ số nào và trừ nó bằng LEN [văn bản] để loại bỏ một lượng ký tự đặc biệt cụ thể.LEN[text] to remove a specific amount of special characters.

Đối với công thức này là:

Tương tự đối với công thức bên trái,LEFT formula,

Và để gia tăng số phiên bản, công thức đã thay đổi:

Mô tả công thức:

Cú pháp của công thức:

Function RemoveSpecChar[sInput As String] As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®|.&@# [_+`©~];-+=^$!,'"
For i = 1 To Len[sSpecChar]
sInput = Replace$[sInput, Mid$[sSpecChar, i, 1], ""]
Next
RemoveSpecChar = sInput
End Function

Text = văn bản từ nơi bạn muốn xóa các ký tự.= the text from where you want to remove characters.

num_chars = số lượng ký tự sẽ được gỡ bỏ.= number of characters to be removed.

Văn bản = văn bản có độ dài bạn muốn đếm.= the text whose length you want to count.

-1 hoặc -[bất kỳ số nào] là số lượng ký tự bạn muốn trừ vào tổng số ký tự trong một văn bản. or -[any number] is the number of characters you want to subtract from the total number of characters in a text.

c. Sử dụng các chức năng sạch và cắt

Bộ dữ liệu của bạn có thể chứa các ký tự không thể in và thêm không gian. Chức năng sạch và cắt có thể được sử dụng để loại bỏ chúng.CLEAN and TRIM functions can be used to remove them.

Công thức loại bỏ ký tự không thể in là:

Để loại bỏ các ký tự không thể in cùng với các không gian bổ sung, bạn có thể sử dụng công thức:

Tuy nhiên, bạn có thể làm cả bằng cách làm tổ và làm sạch bằng cách thay thế. Công thức sẽ trông giống như:TRIM and CLEAN with SUBSTITUTE. The formula will look like:

Function RemoveSpecChar[sInput As String] As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®|.&@# [_+`©~];-+=^$!,'"
For i = 1 To Len[sSpecChar]
sInput = Replace$[sInput, Mid$[sSpecChar, i, 1], ""]
Next
RemoveSpecChar = sInput
End Function

Theo dõi hình ảnh dưới đây.

Mô tả công thức:

Cú pháp của công thức riêng lẻ:

Ở đây, văn bản = văn bản từ nơi bạn muốn xóa ký tự không thể in. text= the text from where you want to remove the non-printable character.

Văn bản = văn bản từ nơi cần xóa không gian thêm.= the text from where the extra space needs to be removed.

=SUBSTITUTE[text, old_text, new_text, [instance_num]]

Văn bản = văn bản mà bạn muốn làm việc.=the text which you want to work with.

old_text = văn bản mà bạn muốn xóa.= text which you want to remove.

new_text = văn bản thay thế. [Đối với trường hợp của chúng tôi, chúng tôi thay thế nó bằng trống Trò chơi]. replaced text. [ For our case we replace it with blank “ “].

instance_name = số lượng ký tự đặc biệt trong trường hợp ký tự đệ quy có trong văn bản.= the number of the special character in case of recursive characters present in the text.

Trương hợp đặc biệt:

Các ký tự đặc biệt chứa số mã và chúng tôi có thể nhận số mã của chúng bằng công thức: REPLACE function to remove a specific amount of characters after a number of characters.

hoặc

Function RemoveSpecChar[sInput As String] As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®|.&@# [_+`©~];-+=^$!,'"
For i = 1 To Len[sSpecChar]
sInput = Replace$[sInput, Mid$[sSpecChar, i, 1], ""]
Next
RemoveSpecChar = sInput
End Function

Hàm phải hoặc bên trái được sử dụng để có được vị trí của ký tự bạn muốn nhận mã.start_num [ the number from which the characters need to be removed].

Do đó, quá trình này bao gồm hai bước: [ the number of characters to be removed].

Nhận mã bằng cách sử dụng công thức mã lồng nhau với phải hoặc trái.text as an argument which is needed for SUBSTITUTE.

Sử dụng công thức thay thế và thay cho Old_Text Viết char [số].#Sen“.

Đối với kết quả, theo các hình ảnh dưới đây cùng với các công thức. How to Remove Specific Characters in Excel

Hơn nữa, nếu các ký tự tương tự được tìm thấy quá trình này sẽ loại bỏ cả hai. Quan sát các kết quả dưới đây.

b. Sử dụng các chức năng phải hoặc tráiFlash Fill is the easiest way to remove special characters in Excel.

Xem xét, bạn đã thấy theo cách ở trên, việc sử dụng các chức năng bên phải và bên trái. Chúng có thể được sử dụng với hàm LEN để loại bỏ các ký tự cụ thể trong Excel.Flash Fill to remove special characters in Excel.

• Công thức sẽ là: the first text without special characters.
• Bạn có thể tăng các giá trị lên bất kỳ số nào và trừ nó bằng LEN [văn bản] để loại bỏ một lượng ký tự đặc biệt cụ thể.

• Đối với công thức này là: ENTER from the keyboard. This will show the result as below.

Tương tự đối với công thức bên trái, How to Remove Blank Characters in Excel

Và để gia tăng số phiên bản, công thức đã thay đổi:

Text = văn bản từ nơi bạn muốn xóa các ký tự. Find & Replace.

num_chars = số lượng ký tự sẽ được gỡ bỏ.Mailto:” before the address in the column named Mail Address of the dataset.

Văn bản = văn bản có độ dài bạn muốn đếm.Find & Replace.

• -1 hoặc -[bất kỳ số nào] là số lượng ký tự bạn muốn trừ vào tổng số ký tự trong một văn bản.Replace from Find & Replace. Follow the picture below to get Find & Replace from the Editing options of the Home tab.

• c. Sử dụng các chức năng sạch và cắtFind what: box and keep the Replace with: box blank. See the picture below.

• Bộ dữ liệu của bạn có thể chứa các ký tự không thể in và thêm không gian. Chức năng sạch và cắt có thể được sử dụng để loại bỏ chúng.Replace All and a new box will open. It will show the number of replacements done.
• Bấm OK.OK.

Bạn sẽ thấy kết quả như sau.

Đọc thêm: Cách loại bỏ không gian trong Excel: Với Công thức, VBA & Truy vấn sức mạnh How to Remove Spaces in Excel: With Formula, VBA & Power Query

4. Loại bỏ các ký tự đặc biệt bằng công cụ truy vấn Power

Chắc chắn, nếu bạn đang sử dụng Microsoft Excel 2016 hoặc Excel 365 thì bạn có thể sử dụng Truy vấn Power để xóa các ký tự đặc biệt trong Excel.Microsoft Excel 2016 or Excel 365 then you can use Power Query to remove special characters in 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 Microsoft.Microsoft Excel 2010 or 2013, you can install it from the Microsoft website.

Bạn có thể làm theo các bước để sử dụng truy vấn nguồn để xóa các ký tự đặc biệt khỏi bộ dữ liệu của bạn.Power Query to remove special characters from your dataset.

• 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.From Table/Range from the Data tab.

• Bạn sẽ tìm thấy một hộp nhỏ. Kiểm tra phạm vi dữ liệu đã chọn của bạn và đánh dấu vào bảng của tôi có tùy chọn tiêu đề.My table has headers option.
• Sau đó, bấm OK. OK.

Một cửa sổ mới có tên là cửa sổ Truy vấn Power sẽ mở.Power Query Window will open.

• Chọn Cột tùy chỉnh từ tab Thêm cột trong cửa sổ Truy vấn nguồn.Custom Column from the Add Column tab in the Power Query window.

• Nó sẽ mở hộp cột tùy chỉnh.Custom Column box.
• Viết về 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.Without Special Characters” in the New column name option. You can write any name you want.
• Sau đó, viết công thức bên dưới trong tùy chọn Công thức cột tùy chỉnh.Custom column formula option.

Formula:

Function RemoveSpecChar[sInput As String] As String
Dim sSpecChar As String
Dim i As Long
sSpecChar = "\/:*?™""®|.&@# [_+`©~];-+=^$!,'"
For i = 1 To Len[sSpecChar]
sInput = Replace$[sInput, Mid$[sSpecChar, i, 1], ""]
Next
RemoveSpecChar = sInput
End Function

• Sau đó, bấm OK.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ị trong 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ổ.Close & Load from the File tab of the window.

Bạn sẽ tìm thấy một bảng tính mới trong sổ làm việc của bạn, 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 loại bỏ các ký tự của^^^khỏi Cell D7. Đó là bởi vì Excel xem xét nhân vật trong danh mục của. . ” tính cách.^^” characters from cell D7. It is because Excel considers the character within the category of “. .” character.

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 năm 2010, bạn có thể không cài đặt Power truy vấn. Bạn chỉ có thể sử dụng tính năng này với phiên bản 2010 để mới nhất.2010, you might not be able to install Power Query. You can use this feature only with versions 2010 to the latest.

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.

Sự kết luận

Bài báo đã 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. Nói tóm lại, các công thức bao gồm các chức năng như thay thế, sạch, phải, mã, v.v. Mặt khác, các công cụ được sử dụng là flash điền, tìm và thay thế và truy vấn năng lượng. Tôi hy vọng bài viết này là hữu ích cho bạn. Đối với bất kỳ truy vấn nào nữa, hãy viết trong phần bình luận. SUBSTITUTE, CLEAN, RIGHT, CODE, and so on. On the other hand, the tools used are Flash Fill, Find & Replace and Power Query. I hope this article was helpful to you. For any further queries, write in the comment section.

Những bài viết liên quan

• Cách xóa các ký tự trong Excel [6 Phương pháp]
• Xóa ký tự đầu tiên khỏi chuỗi trong Excel [6 cách nhanh]
• Cách xóa ký tự cuối cùng trong Excel [6 cách dễ nhất]
• Cách xóa các ký tự từ bên trái trong Excel [6 cách]
• Excel loại bỏ các ký tự từ bên phải [5 cách]
• Cách loại bỏ các ký tự không phải là số từ các ô trong Excel
• Cách xóa không gian trong Excel trước số [3 cách]