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.


Download Practice Workbook

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


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

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 enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

Embedding VBA Code

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

Embedding VBA Code

  • 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 into the cell.

=Check_Special_Characters(B5)

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

Using Simple User-Defined Function to Check If Cell Contains Special Character

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

Using Simple User-Defined Function to Check If Cell Contains Special Character

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

Using Simple User-Defined Function to Check If Cell Contains 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.

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


Similar Readings


2. Applying Built-in VBA Function

In this approach, we are going to write a VBA code using Excel’s built-in VBA LEN and 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. If you don’t have that, you have to enable the Developer tab. Or You can also press ‘Alt+F11’ for opening the Visual Basic Editor.

Embedding VBA Code

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

Embedding VBA Code

  • 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 into the cell.

=Find_Special_Characters(B5)

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

Applying Built-in VBA Function to Check If Cell Contains Special Character

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

Applying Built-in VBA Function to Check If Cell Contains Special Character

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

Applying Built-in VBA Function to Check If Cell Contains 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.

🔎 Breakdown of VBA Code

👉 The name of the user-defined function is Find_Special_Characters, which takes the Text_Value String as input.

👉 Next, we declare some variables; Initial_Character As Long and Allowable_Character As String.

👉 Then, we used a VBA For Loop to set these variables to find and identify the special characters.

👉 Moreover, we used the built-in VBA LEN and VBA MID functions.

👉 Finally, we input the VBA function code at our desired cell.

Read More: How to Find Special Characters in Excel (3 Easy Methods)


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you 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.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Soumik Dutta
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo