How to Find Special Characters Using VBA in Excel (3 Quick Ways)

The article will show you how to find special characters in Excel using VBA. Special characters can be any characters other than uppercase or lowercase letters, numbers, etc. You can also define which characters you don’t want to consider special characters. In this article, I’ll show you how to identify whether a text or string contains commas, full stops, exclamation marks, symbols, etc. with the help of Excel VBA.

Find Special Characters Using VBA in Excel: 3 Ways

In the dataset, you will see some sentences, names, and equations with or without special characters. If the sentence names or equations contain any special characters other than numbers and letters, our VBA application will find them and identify them.

1. Using a Simple User Defined Function to Find Special Characters

In this section, we are going to create a very simple user-defined function to find special characters using Excel VBA. Let’s go through the procedure below for a better understanding.

Steps:

• First, go to the Developer Tab and then select Visual Basic.

• After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.

• Now, 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

Here, 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.

• Now, 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 CheckSpecialCharacters 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 in Excel using VBA.

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

We can use one or multiple built-in VBA functions to create a user-defined function to find special characters from a string. The process is given below.

Steps:

• First, follow the process described in Method 1 to open a VBA Module.
• Next, 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 Characters | Learn Excel