Cách tìm bất kỳ ký tự đặc biệt nào trong chuỗi trong Excel
Các hàm Excel được sử dụng để xóa các ký tự không thể in và khoảng trắng thừa khỏi chuỗi nhưng chúng không giúp ích nhiều trong việc xác định các chuỗi chứa bất kỳ ký tự đặc biệt nào như @ hoặc! vv Trong những trường hợp như vậy, chúng tôi sử dụng UDF.
Vì vậy, nếu bạn muốn biết liệu một chuỗi có chứa bất kỳ ký tự đặc biệt nào hay không, bạn sẽ không tìm thấy bất kỳ công thức hoặc hàm Excel nào để làm như vậy. Chúng ta có thể tìm thấy các ký tự đặc biệt có trên bàn phím bằng cách nhập thủ công nhưng bạn không thể biết liệu có ký hiệu trong chuỗi hay không.
Việc tìm kiếm bất kỳ ký tự đặc biệt nào có thể rất quan trọng cho mục đích làm sạch dữ liệu. Và trong một số trường hợp, nó phải được thực hiện. Vậy làm cách nào để thực hiện điều này trong Excel? Làm thế nào chúng ta có thể biết nếu một chuỗi chứa bất kỳ ký tự đặc biệt nào?
Chúng ta có thể sử dụng UDF để làm như vậy. Công thức dưới đây sẽ trả về TRUE nếu bất kỳ ô nào chứa bất kỳ ký tự nào khác từ 1 đến 0 và A đến Z (trong cả hai trường hợp). Nếu nó không tìm thấy bất kỳ ký tự đặc biệt nào, nó sẽ trả về FALSE.
Công thức chung
|
=ContainsSpecialCharacters(string) |
Chuỗi: Chuỗi mà bạn muốn kiểm tra các ký tự đặc biệt.
Để công thức này hoạt động, bạn sẽ cần đặt mã bên dưới vào mô-đun của sổ làm việc của bạn.
Vì vậy, hãy mở Excel. Nhấn ALT + F11 để mở VBE. Chèn một mô-đun từ menu Chèn. Sao chép mã bên dưới và dán vào mô-đun.
Function ContainsSpecialCharacters(str As String) As Boolean For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False Case Else ContainsSpecialCharacters = True Exit For End Select Next End Function
Bây giờ chức năng đã sẵn sàng để sử dụng.
Đi tới trang tính trong sổ làm việc có chứa các chuỗi mà bạn muốn kiểm tra.
Viết công thức dưới đây vào ô C2:
|
=ContainsSpecialCharacters(B13) |
Nó trả về TRUE cho chuỗi đầu tiên vì nó chứa một ký tự đặc biệt. Khi bạn sao chép công thức xuống, nó sẽ hiển thị FALSE cho chuỗi B14, v.v. Nhưng kỳ lạ là nó hiển thị TRUE cho chuỗi cuối cùng “Exceltip.com”. Đó là vì nó chứa một dấu chấm (.). Nhưng tại sao vậy? Hãy kiểm tra mã để hiểu.
Chức năng hoạt động như thế nào?
và chức năng giữa của VBA. Hàm Mid trích xuất một ký tự tại một thời điểm từ chuỗi và lưu trữ nó vào biến ch.
For I = 1 To Len(str) ch = Mid(str, I, 1)
Bây giờ đến phần chính. Chúng ta sử dụng câu lệnh select case để kiểm tra xem biến ch chứa những gì.
Chúng tôi yêu cầu VBA kiểm tra xem ch có chứa bất kỳ giá trị nào được xác định hay không. Tôi đã xác định từ 0 đến 9, A đến Z, a đến z và “” (dấu cách). Nếu c h chứa bất kỳ giá trị nào trong số này, chúng tôi đặt giá trị của nó thành Sai.
Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " ContainsSpecialCharacters = False
Bạn có thể thấy rằng chúng tôi không có dấu chấm (.) Trong danh sách và đây là lý do tại sao chúng tôi nhận được TRUE cho chuỗi có chứa dấu chấm. Bạn có thể thêm bất kỳ ký tự nào vào danh sách để được miễn khỏi công thức.
Nếu ch chứa bất kỳ ký tự nào khác với các ký tự được liệt kê, chúng tôi đặt kết quả của hàm thành True và kết thúc vòng lặp ngay tại đó.
Case Else ContainsSpecialCharacters = True Exit For
Vì vậy, đây là cách nó hoạt động. Nhưng nếu bạn muốn xóa các ký tự đặc biệt, bạn sẽ cần thực hiện một số thay đổi trong hàm.
Làm thế nào để làm sạch các ký tự đặc biệt khỏi chuỗi trong Excel? Để xóa các ký tự đặc biệt khỏi một chuỗi trong Excel, tôi đã tạo một hàm tùy chỉnh khác trong VBA. Cú pháp của hàm là:
|
=CleanSpecialCharacters(string) |
Hàm này trả về một phiên bản đã làm sạch của chuỗi được truyền vào nó.
Chuỗi mới sẽ không chứa bất kỳ ký tự đặc biệt nào. Mã của hàm này như sau:
Function CleanSpecialCharacters(str As String) Dim nstr As String nstr = str For I = 1 To Len(str) ch = Mid(str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " 'Do nothing Case Else nstr = Replace(nstr, ch, "") End Select Next CleanSpecialCharacters = nstr End Function
Sao chép điều này trong cùng một mô-đun mà bạn đã sao chép trong đoạn mã trên.
Khi bạn sử dụng công thức này trên các chuỗi, kết quả sẽ như thế nào:
Bạn có thể thấy mọi ký tự đặc biệt bị xóa khỏi chuỗi bao gồm cả dấu chấm.
Nó hoạt động như thế nào?
Nó hoạt động giống như chức năng trên. Sự khác biệt duy nhất là hàm này thay thế mọi ký tự đặc biệt bằng một ký tự rỗng. Tạo một chuỗi mới và trả về chuỗi này.
Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " 'ContainsSpecialCharacters = False Case Else nstr = Replace(nstr, ch, "") End Select
Nếu bạn muốn loại bỏ bất kỳ ký tự nào khỏi việc làm sạch, bạn có thể thêm nó vào danh sách trong mã. Excel sẽ tha thứ cho ký tự đặc biệt đó.
Vì vậy, các bạn, đây là cách bạn có thể tìm và thay thế các ký tự đặc biệt từ một chuỗi trong Excel. Tôi hy vọng điều này đủ giải thích và hữu ích.
Nếu nó không giúp bạn giải quyết vấn đề của mình, hãy cho chúng tôi biết trong phần bình luận bên dưới.
Bài viết liên quan:
Hàm REPLACE sử dụng vị trí của văn bản trong chuỗi để thay thế nó.
Bài viết phổ biến:
liên kết: / công thức-và-chức-năng-giới-thiệu-của-vlookup-chức-năng [Excel]
Bạn không cần phải lọc dữ liệu của mình để đếm các giá trị cụ thể. Chức năng Countif là cần thiết để chuẩn bị bảng điều khiển của bạn.