How to Check If Cell Contains Special Character in Excel: 2 Ways

Method 1 – Using Simple User-Defined Function

Steps:

• Go to the Developer tab and click on Visual Basic. If you don’t have that, you must display the Developer tab on the ribbon. You can press ‘Alt+F11’ to open the Visual Basic Editor.

• A dialog box will appear.
• In the Insert tab on that box, click the Module option.

• Write down the following visual code in that empty editor box.

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

• Press ‘Ctrl+S’ to save the code.
• Close the Editor tab.
• Select cell B5.
• Write down the following formula in the cell.

• Press Enter.
• As the cell contains a special character, dot(.), the function is showing us TRUE.

• Double-click the Fill Handle icon to copy the formula to cell B13.

• You will see the function shows TRUE for the cells which contain at least one special character. On the other hand, it is showing FALSE for the cell that doesn’t have any special character.

You were able to check if the cell contains a special character in Excel.

Method 2 – Applying Built-in VBA Function

Steps:

• Go to the Developer tab and click on Visual Basic. Or You can press ‘Alt+F11’ to open the Visual Basic Editor.

• A dialog box will appear.
• In the Insert tab, click the Module option.

• Write down the following visual code in that empty editor box.

Function Find_Special_Characters(Text_Value As String) As Boolean
Dim Initial_Character As Long
Dim Allowable_Character As String
For Initial_Character = 1 To Len(Text_Value)
Allowable_Character = Mid(Text_Value, Initial_Character, 1)
Select Case Allowable_Character
Case "0" To "9", "A" To "Z", "a" To "z", " "
Find_Special_Characters = False
Case Else
Find_Special_Characters = True
Exit For
End Select
Next
End Function

• Then, press ‘Ctrl+S’ to save the code.
• Finally, close the Editor tab.
• Next, select cell B5.
• Afterward, write down the following formula in the cell.

• Press Enter.
• Notice that cell B5 contains a special character, dot(.), so the function shows that it is TRUE.

• Double-click the Fill Handle icon to copy the formula to cell B13.

• The function shows TRUE for cells containing at least one special character and FALSE for cells without any special characters.

You were able to check if the cell contains a special character in Excel.

Download Practice Workbook

Download this practice workbook while you are reading this article.

<< Go Back to Learn Excel