How to Find Special Characters Using VBA in Excel: 3 Quick Methods

Method 1 – Using a Simple User Defined Function to Find Special Characters

Steps:

• Go to the Developer Tab and select Visual Basic.

• The VBA editor will appear. Select Insert >> Module to open a VBA Module.

• Type the following code in the VBA Module.

Public Function CheckSpecialCharacters(nTextValue As String) As Boolean
CheckSpecialCharacters = nTextValue Like "*[!A-Za-z0-9 ]*"
End Function

We created a user-defined function named CheckSpecialCharacters. The function will return TRUE if it finds a special character in the text or string of our dataset. We defined lowercase and uppercase letters, numbers, and space as non-special characters meaning the function will return FALSE if it gets them only in a text or string.

• Go back to your sheet and type the following formula in cell C5.

The formula will return TRUE as the sentence in B5 contains a comma (,) and exclamation mark (!), which the CheckSpecialCharacters function considers as special characters.

• Hit the ENTER button, and you will see the output in C5.

• Use the Fill Handle to AutoFill the lower cells.

You can find special characters in Excel using VBA.

Method 2 – Applying Built-in VBA Function to Find Special Characters in Excel

Steps:

• Follow the process described in Method 1 to open a VBA Module.
• Type the following code in the Module.

Function FindSpecialCharacters(TextValue As String) As Boolean
Dim Starting_Character As Long
Dim Acceptable_Character As String
For Starting_Character = 1 To Len(TextValue)
Acceptable_Character = Mid(TextValue, Starting_Character, 1)
Select Case Acceptable_Character
Case "0" To "9", "A" To "Z", "a" To "z", " "
FindSpecialCharacters = False
Case Else
FindSpecialCharacters = True
Exit For
End Select
Next
End Function

• After that, go back to your sheet and type the following formula in cell C5.

Here, the formula will return TRUE as the sentence in B5 contains a comma (,) and exclamation mark (!) which the FindSpecialCharacter function considers as special characters.

• Next, hit the ENTER button, and you will see the output in C5.

• After that, use the Fill Handle to AutoFill the lower cells.

Thus, you can find special characters using the built-in functions of VBA.

3. Highlight to Find Special Characters Using VBA

This section will show you how to highlight a cell if it contains special characters and thus find them in the process. Let’s go through the procedure below.

Steps:

• First, follow the process described in Method 1 to open a VBA Module.
• Next, type the following code in the Module.

Sub HighlightBySpecialCharacter()
Dim mnR As Range, nText_Range As Range, mnS As String
Dim mnI As Long, nCharacter_Length As Long
Set nText_Range = Intersect(Range("B4:B11"), ActiveSheet.UsedRange)
For Each mnR In nText_Range
If mnR.Value <> "" Then
mnS = Replace(mnR.text, "-", "")
nCharacter_Length = Len(mnS)
For mnI = 1 To nCharacter_Length
If Not Mid(mnS, mnI, 1) Like "[0-9a-zA-Z ]" Then
mnR.Interior.Color = vbGreen
End If
Next mnI
End If
Next mnR
End Sub

• After that, go back to your sheet and run the Macro.

• Thereafter, you will see the cells in the range B4:B11 that have special characters filled with green.

Thus, you can highlight special characters in Excel using VBA.

Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own.

Download Practice Workbook

Conclusion

In the end, we can pull the bottom line, considering that you will learn some effective tactics to find special characters in Excel using VBA. If you have any better methods questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.

<< Go Back to Learn Excel