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

Sometimes Excel cell contains special characters like comma(,), dot(.), hyphen(-), brackets, etc., and we need to find if any cell contains such characters. Excel has fantastic features to check and find them. In this article, we will demonstrate two quick examples to check if the cell contains a special character in Excel. If you are curious about it, download our practice workbook and follow us.

Check If Cell Contains Special Character in Excel: 2 Quick Ways

To demonstrate the examples, we consider the detailed information of an employee of an organization. Our data is in the range of cells B5:B13. We are going to check if there are any special characters lying in those cells, and the checking status will be in column C.

1. Using Simple User-Defined Function

In this method, we will write a simple VBA code to build a user-defined function that will check if the cell contains a special character in our dataset. The steps of this process are given as follows:

📌 Steps:

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

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

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

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

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

• After that, double-click on the Fill Handle icon to copy the formula up to cell B13.

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

Thus, we can say that our visual code worked perfectly, and we are able to check if the cell contains a special character in Excel.

2. Applying Built-in VBA Function

In this approach, we are going to write a VBA code to find special characters in Excel with built-in VBA LEN and VBA MID functions to check if the cell contains a special character. The procedure of this method is given below:

📌 Steps:

• First, go to the Developer tab and click on Visual Basic. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

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

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

• Similarly, press Enter.
• You may notice that cell B5 contains a special character, dot(.), so the function is showing us TRUE.

• Then, double-click on the Fill Handle icon to copy the formula up to cell B13.

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

Finally, we can say that our visual code worked successfully, and we are able to check if the cell contains a special character in Excel.

Download Practice Workbook

Download this practice workbook for practice while you are reading this article.

Conclusion

That’s the end of this article. I hope that this article will be helpful for you and will be able to check if a cell contains a special character in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

<< Go Back to Characters | Learn Excel