Hướng dẫn how to remove unwanted characters in excel - cách xóa ký tự không mong muốn trong excel

Trong bài viết này, bạn sẽ học cách xóa các ký tự cụ thể khỏi chuỗi văn bản và xóa các ký tự không mong muốn khỏi nhiều ô cùng một lúc.

Khi nhập dữ liệu để xuất sắc từ một nơi khác, rất nhiều nhân vật đặc biệt có thể đi đến bảng tính của bạn. Điều thậm chí còn bực bội hơn là một số nhân vật là vô hình, tạo ra thêm không gian trắng trước, sau hoặc bên trong các chuỗi văn bản. Hướng dẫn này cung cấp các giải pháp cho tất cả các vấn đề này, khiến bạn gặp rắc rối khi phải trải qua các nhân vật không mong muốn của tế bào dữ liệu và thanh trừng bằng tay.

Loại bỏ ký tự đặc biệt khỏi ô excel

Để xóa một ký tự cụ thể khỏi một ô, hãy thay thế nó bằng một chuỗi trống bằng cách sử dụng hàm thay thế ở dạng đơn giản nhất của nó:

Thay thế (ô, char, "")

Ví dụ, để xóa một dấu hỏi từ A2, công thức trong B2 là:

=SUBSTITUTE(A2, "?", "")

Để xóa một ký tự không có trên bàn phím của bạn, bạn có thể sao chép/dán nó vào công thức từ ô ban đầu.

Chẳng hạn, đây là cách bạn có thể thoát khỏi một dấu hỏi đảo ngược:

=SUBSTITUTE(A2, "¿", "")

Nhưng nếu một nhân vật không mong muốn là vô hình hoặc không sao chép chính xác, làm thế nào để bạn đặt nó vào công thức? Đơn giản, tìm số mã của nó bằng cách sử dụng chức năng mã.invisible or does not copy correctly, how do you put it in the formula? Simply, find its code number by using the CODE function.

Trong trường hợp của chúng tôi, ký tự không mong muốn ("") xuất hiện cuối cùng trong ô A2, vì vậy chúng tôi đang sử dụng kết hợp mã và các hàm đúng để lấy lại giá trị mã duy nhất của nó, đó là 191:

=CODE(RIGHT(A2))

Khi bạn nhận được mã của ký tự, hãy phục vụ hàm char tương ứng cho công thức chung ở trên. Đối với bộ dữ liệu của chúng tôi, công thức diễn ra như sau:

=SUBSTITUTE(A2, CHAR(191),"")

Ghi chú. Hàm thay thế là nhạy cảm trường hợp, có nghĩa là nó coi các chữ cái chữ thường và chữ hoa là các ký tự khác nhau. Hãy ghi nhớ điều đó nếu nhân vật không mong muốn của bạn là một lá thư. The SUBSTITUTE function is case-sensitive, meaning it treats lowercase and uppercase letters as different characters. Please keep that in mind if your unwanted character is a letter.

Xóa nhiều ký tự khỏi chuỗi

Trong một trong những bài viết trước, chúng tôi đã xem xét cách loại bỏ các ký tự cụ thể khỏi các chuỗi trong Excel bằng cách làm tổ một số chức năng thay thế sang một chức năng khác. Cách tiếp cận tương tự có thể được sử dụng để loại bỏ hai hoặc nhiều ký tự không mong muốn trong một lần:

Thay thế (thay thế (thay thế (ô, char1, ""), char2, ""), char3, "")

Ví dụ, để xóa bỏ các câu cảm thán và câu hỏi bình thường cũng như các dấu hiệu đảo ngược từ chuỗi văn bản trong A2, sử dụng công thức này:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "!", ""), "¡", ""), "?", ""), "¿", "")

Điều tương tự có thể được thực hiện với sự trợ giúp của hàm char, trong đó 161 là mã ký tự cho "" "và 191 là mã ký tự cho" ¿":

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3, "!", ""), "?", ""), CHAR(161), ""), CHAR(191), "")

Các chức năng thay thế lồng nhau hoạt động tốt cho một số lượng ký tự hợp lý, nhưng nếu bạn có hàng tá ký tự để loại bỏ, công thức trở nên quá dài và khó quản lý. Ví dụ tiếp theo cho thấy một giải pháp nhỏ gọn và thanh lịch hơn.

Xóa tất cả các ký tự không mong muốn cùng một lúc

Giải pháp chỉ hoạt động trong Excel cho Microsoft 365

Như bạn có thể biết, Excel 365 có một chức năng đặc biệt cho phép bạn tạo các chức năng của riêng mình, bao gồm cả các chức năng tính toán đệ quy. Chức năng mới này được đặt tên là Lambda và bạn có thể tìm thấy chi tiết đầy đủ về nó trong hướng dẫn được liên kết ở trên. Dưới đây, tôi sẽ minh họa khái niệm này với một vài ví dụ thực tế.

Chức năng Lambda tùy chỉnh để loại bỏ các ký tự không mong muốn như sau:remove unwanted characters is as follows:

=LAMBDA(string, chars, IF(chars<>"", RemoveChars(SUBSTITUTE(string, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), string))

Để có thể sử dụng chức năng này trong bảng tính của bạn, bạn cần đặt tên cho nó trước. Đối với điều này, nhấn Ctrl + F3 để mở trình quản lý tên, sau đó xác định tên mới theo cách này:Ctrl + F3 to open the Name Manager, and then define a New Name in this way:

1. Trong hộp tên, nhập tên của hàm: removechars.
2. Đặt phạm vi thành sổ làm việc.
3. Trong đề cập đến hộp, dán công thức trên.
4. Tùy chọn, nhập mô tả của các tham số trong hộp bình luận. Các tham số sẽ được hiển thị khi bạn nhập công thức trong ô.
5. Bấm OK để lưu chức năng mới của bạn.

Để biết hướng dẫn chi tiết, vui lòng xem cách đặt tên cho hàm Lambda tùy chỉnh.

Khi chức năng nhận được một tên, bạn có thể đề cập đến nó như bất kỳ công thức gốc nào.

Từ quan điểm của người dùng, cú pháp của chức năng tùy chỉnh của chúng tôi đơn giản như thế này:

Removechars (chuỗi, chao)

Where:

• Chuỗi - là chuỗi gốc hoặc tham chiếu đến ô/phạm vi chứa (các) chuỗi.
• Chars - ký tự để xóa. Có thể được biểu diễn bằng một chuỗi văn bản hoặc tham chiếu ô.

Để thuận tiện, chúng tôi nhập các ký tự không mong muốn vào một số tế bào, nói D2. Để loại bỏ các ký tự đó khỏi A2, công thức là:

=RemoveChars(A2, $D$2)

Để công thức hoạt động chính xác, vui lòng thông báo về những điều sau:

• Trong D2, các ký tự được liệt kê không có khoảng trống, trừ khi bạn cũng muốn loại bỏ khoảng trống.
• Địa chỉ của ô chứa các ký tự đặc biệt được khóa với dấu $ ($ D $ 2) để ngăn tham chiếu thay đổi khi đối phó với công thức sang các ô bên dưới.

Và sau đó, chúng tôi chỉ cần kéo công thức xuống và có tất cả các ký tự được liệt kê trong D2 bị xóa khỏi các ô A2 đến A6:

Để làm sạch nhiều ô bằng một công thức duy nhất, hãy cung cấp phạm vi A2: A6 cho đối số đầu tiên:

=RemoveChars(A2:A6, D2)

Vì công thức chỉ được nhập trong ô cao nhất, bạn không cần phải lo lắng về việc khóa tọa độ ô - một tham chiếu tương đối (D2) hoạt động tốt trong trường hợp này. Và do hỗ trợ cho các mảng động, công thức tự động tràn vào tất cả các ô được tham chiếu:

Xóa một bộ ký tự được xác định trước

Để xóa một tập hợp các ký tự được xác định trước khỏi nhiều ô, bạn có thể tạo một Lambda khác gọi hàm Main RemoveChars và chỉ định các ký tự không mong muốn trong tham số thứ 2. Ví dụ:

Để xóa các ký tự đặc biệt, chúng tôi đã tạo một chức năng tùy chỉnh có tên RemoveSpecarS:special characters, we've created a custom function named RemoveSpecialChars:

=LAMBDA(string, RemoveChars(string, "?¿!¡*%#@^"))

Để xóa số khỏi chuỗi văn bản, chúng tôi đã tạo thêm một hàm có tên RemoveBusumbers:remove numbers from text strings, we've created one more function named RemoveNumbers:

=SUBSTITUTE(A2, "¿", "")0

Cả hai hàm trên đều siêu dễ sử dụng vì chúng chỉ yêu cầu một đối số - chuỗi gốc.

Để loại bỏ các ký tự đặc biệt khỏi A2, công thức là:special characters from A2, the formula is:

=SUBSTITUTE(A2, "¿", "")1

Để chỉ xóa các ký tự số:numeric characters:

=SUBSTITUTE(A2, "¿", "")2

Làm thế nào chức năng này hoạt động:

Về bản chất, hàm removechars vòng lặp thông qua danh sách các ký tự và xóa một ký tự tại một thời điểm. Trước mỗi cuộc gọi đệ quy, chức năng IF kiểm tra các ký tự còn lại. Nếu chuỗi ký tự không trống (chars ""), chức năng sẽ tự gọi. Ngay sau khi ký tự cuối cùng được xử lý, công thức trả về chuỗi nó hiện tại của nó và thoát ra.

Đối với công thức chi tiết bị phá vỡ, vui lòng xem Lambda đệ quy để loại bỏ các ký tự không mong muốn.

Xóa các ký tự đặc biệt bằng VBA

Các chức năng hoạt động trong tất cả các phiên bản của Excel

Nếu chức năng Lambda không có sẵn trong Excel của bạn, không có gì ngăn bạn tạo chức năng tương tự với VBA. Một hàm do người dùng xác định (UDF) có thể được viết theo hai cách.

Chức năng tùy chỉnh để xóa các ký tự đặc biệt đệ quy:recursive:

Mã này mô phỏng logic của hàm Lambda được thảo luận ở trên.

Chức năng remoleUnWantedChars (asstring str, chars asstring) if ("" chars) sau đó str = thay thế (str, trái (chars, 1), "") , chars) RemoveUnwantedChars(str As String, chars As String) If ("" <> chars) Then str = Replace(str, Left(chars, 1), "") chars = Right(chars, Len(chars) - 1) RemoveUnwantedChars = RemoveUnwantedChars(str, chars) Else RemoveUnwantedChars = str End If End Function

Chức năng tùy chỉnh để xóa các ký tự đặc biệt không nhận được:non-recursive:

Ở đây, chúng tôi đạp xe qua các ký tự không mong muốn từ 1 đến LEN (chars) và thay thế các nhân vật được tìm thấy trong chuỗi ban đầu mà không có gì. Hàm MID kéo từng ký tự không mong muốn và chuyển chúng đến hàm thay thế.

Chức năng removeUnWantedChars (asstring ster RemoveUnwantedChars(str As String, chars As String) For index = 1 To Len(chars) str = Replace(str, Mid(chars, index, 1), "") Next RemoveUnwantedChars = str End Function

Chèn một trong các mã trên trong sổ làm việc của bạn như được giải thích trong cách chèn mã VBA vào Excel và chức năng tùy chỉnh của bạn đã sẵn sàng để sử dụng.

Không nhầm lẫn chức năng do người dùng xác định mới của chúng tôi với một chức năng do Lambda xác định, chúng tôi đã đặt tên cho nó khác nhau:

RemoveUnWantedChars (Chuỗi, chars)

Giả sử chuỗi ban đầu nằm trong A2 và các ký tự không mong muốn trong D2, chúng ta có thể loại bỏ chúng bằng cách sử dụng công thức này:

=SUBSTITUTE(A2, "¿", "")3

Chức năng tùy chỉnh với các ký tự được mã hóa cứng

Nếu bạn không muốn bận tâm đến việc cung cấp các ký tự đặc biệt cho từng công thức, bạn có thể chỉ định chúng trực tiếp trong mã:

Hàm Tháo rời Chức năng (Asstring) AsstringDim chars asstringDim INDEX ASLONG chARS = "? ¿! str, mid (chars, index, 1), "") tiếp theo loại bỏ phân tích = str endfeft RemoveSpecialChars(str As String) As String Dim chars As String Dim index As Long chars = "?¿!¡*%#$(){}[]^&/\~+-" For index = 1 To Len(chars) str = Replace(str, Mid(chars, index, 1), "") Next RemoveSpecialChars = str End Function

Xin lưu ý rằng mã trên là cho mục đích trình diễn. Để sử dụng thực tế, hãy nhớ bao gồm tất cả các ký tự bạn muốn xóa trong dòng sau:

=SUBSTITUTE(A2, "¿", "")4

Chức năng tùy chỉnh này được đặt tên là RemoveSpecarchars và nó chỉ yêu cầu một đối số - chuỗi gốc:

RemoveSpecialChars(string)

Để loại bỏ các ký tự đặc biệt khỏi bộ dữ liệu của chúng tôi, công thức là:

=SUBSTITUTE(A2, "¿", "")1

Xóa các ký tự không thể in trong Excel

Microsoft Excel có chức năng đặc biệt để xóa các ký tự không in - chức năng sạch. Về mặt kỹ thuật, nó loại bỏ 32 ký tự đầu tiên trong bộ ASCII 7 bit (mã từ 0 đến 31).

Ví dụ: để xóa các ký tự không thể in khỏi A2, đây là công thức sử dụng:nonprintable characters from A2, here's the formula to use:

=SUBSTITUTE(A2, "¿", "")6

Điều này sẽ loại bỏ các ký tự không in, nhưng không gian trước/sau văn bản và giữa các từ sẽ vẫn còn.

Để loại bỏ các không gian bổ sung, hãy bọc công thức sạch trong hàm trang trí:extra spaces, wrap the CLEAN formula in the TRIM function:

=SUBSTITUTE(A2, "¿", "")7

Bây giờ, tất cả các không gian dẫn đầu và dấu vết đã được gỡ bỏ, trong khi các không gian ở giữa được giảm xuống thành một ký tự không gian duy nhất:

Nếu bạn muốn xóa hoàn toàn tất cả các không gian bên trong một chuỗi, thì hãy thay thế ký tự không gian (mã số 32) bằng một chuỗi trống:all spaces inside a string, then additionally substitute the space character (code number 32) with an empty string:

=SUBSTITUTE(A2, "¿", "")8

Một số không gian hoặc các nhân vật vô hình khác vẫn còn trong bảng tính của bạn? Điều đó có nghĩa là những ký tự đó có các giá trị khác nhau trong bộ ký tự Unicode.

Chẳng hạn, mã ký tự của không gian không phá vỡ (& NBSP;) là 160 và bạn có thể thanh lọc nó bằng công thức này:non-breaking space ( ) is 160 and you can purge it using this formula:

=SUBSTITUTE(A2, "¿", "")9

Để xóa một ký tự không in cụ thể, bạn cần tìm giá trị mã của nó trước. Các hướng dẫn chi tiết và ví dụ công thức là ở đây: làm thế nào để loại bỏ một ký tự không in cụ thể.specific non-printing character, you need to find its code value first. The detailed instructions and formula examples are here: How to remove a specific non-printing character.

Xóa các ký tự đặc biệt với Ultimate Suite

Hỗ trợ Excel cho Microsoft 365, Excel 2019 - 2010

Trong ví dụ cuối cùng này, hãy để tôi chỉ cho bạn cách dễ nhất để loại bỏ các nhân vật đặc biệt trong Excel. Với bộ cuối cùng được cài đặt, đây là những gì bạn cần làm:

1. Trên tab Dữ liệu ABLEBITS, trong nhóm văn bản, bấm Xóa> Xóa các ký tự.Remove Characters.
   
2. Trên ngăn bổ trợ, chọn phạm vi nguồn, chọn Xóa các bộ ký tự và chọn tùy chọn mong muốn từ danh sách thả xuống (Biểu tượng & dấu chấm câu trong ví dụ này).Remove character sets and choose the desired option from the dropdown list (Symbols & punctuation marks in this example).
   
3. Nhấn nút Xóa.

Trong một khoảnh khắc, bạn sẽ nhận được một kết quả hoàn hảo:

Nếu có sự cố xảy ra, đừng lo lắng - bản sao lưu của bảng tính của bạn sẽ được tạo tự động vì hộp bảng này được chọn theo mặc định.

Tò mò muốn thử công cụ xóa của chúng tôi? Một liên kết đến phiên bản đánh giá là ngay bên dưới. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Tải xuống có sẵn

Xóa các ký tự đặc biệt - Ví dụ (tệp .xlsm) Ultimate Suite - Phiên bản dùng thử (tệp .exe)
Ultimate Suite - trial version (.exe file)

Bạn cũng có thể quan tâm