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.


Download Practice Workbook


3 Ways to Find Special Characters Using VBA in Excel

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

how to find special characters in excel using vba intro


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.

how to find special characters in excel using vba

  • 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.

=CheckSpecialCharacters(B5)

how to find special characters in excel using vba using simple user defined function

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

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

how to find special characters in excel using vba

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

Read More: How to Filter Special Characters in Excel (An Easy Guide)


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

Code Explanation

  • The name of the user-defined function here is FindSpecialCharacters, which takes the TextValue String as input.
  • Then we declare some variables; Starting_Character As Long and Acceptable_Character As String.
  • After that, we used a VBA For Loop to set these variables to find and identify the special characters.
  • The built-in functions that we used here are the VBA LEN and MID functions.
  • Finally, we ran the code.
  • After that, go back to your sheet and type the following formula in cell C5.

=FindSpecialCharacters(B5)

how to find special characters in excel using vba using built-in vba functions

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.

how to find special characters in excel using vba

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


Similar Readings


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 finds 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

Code Explanation

  • First, we named our Sub Procedure as HighlightBySpecialCharacter.
  • Then we declare some variables; mnR and nText_Range As Range, mnS As String, mnI, and nCharacter_Length As Long.
  • After that, we used a VBA For Loop to set these variables to find and identify the special characters. Here we also used the Range property to define the column (B4:B11) where we stored our strings with or without special characters.
  • Also, we set the green color to highlight the string containing special characters after the Macro is run.
  • After that, go back to your sheet and run the Macro.

how to find special characters in excel using vba by highlighting

  • 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.

Read More: How to Convert Special Characters in Excel (6 Easy Ways)


Practice Section

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

how to find special characters in excel using vba


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 or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo