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

This tutorial will demonstrate the steps to how to find special characters in excel. We use different special characters like comma(,), dot(.), hyphen(), brackets(), etc. in our day-to-day work life. But sometimes when presenting the data these special characters can create confusion or in some cases, they can look very odd to see. So, it is very important to find special characters.


Download Practice Workbook

You can download the practice workbook from here.


3 Easy Methods to Find Special Characters in Excel

Our goal is to find special characters for a better presentation. For that, we can use 3 methods. We will describe the 3 methods below.

1. Using Power Query to Find Special Characters in Excel

Our aim is to find special characters by using the Power Query method. We will follow the below steps to learn the method.

Steps:

  • First, we have a data table in which we have Global Trade Item Number in Column B and Special Characters in Column C.

Power Query to Find Special Characters in Excel

  • Then, go to the Data tab, and from the Get & Transform Data option select From Table/Range option.

Power Query to Find Special Characters in Excel

  • After, the Table2 worksheet will open on the display screen.
  • Next, go to the Add Column tab and select the Custom Column option.

Power Query to Find Special Characters in Excel

  • Afterward, the Custom Column window will open on the screen.
  • Then paste the following formula in the Custom column formula tab.
=Text.Remove([Global Trade Item Number],{"A".."z","0".."9"})

Power Query to Find Special Characters in Excel

  • After pressing the OK button, you will find the result below.

Power Query to Find Special Characters in Excel

  • Then, hit the Close & Load option from the Home tab.

  • Lastly, you will get the below result.

Read More: Apply Formula to Identify Special Characters in Excel (4 Methods)


2. Applying VBA Code

In this case, we want to find a special character by using the VBA code. The full description of the steps is given below:

Steps:

  • First, press Alt+F11 to open the VBA window.
  • Then, select the Module option from the Insert tab.

Methods to Find Special Characters in Excel

  • Then, insert the following VBA code into the window:
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

Methods to Find Special Characters in Excel

  • After that, click the Run option or F5 to see if there is an error in the code.
  • Next, save the code by pressing Ctrl+S.

  • Then, return to the main worksheet, and in the C5 cell insert the following formula:

=FindSpecialCharacters(B5)

Methods to Find Special Characters in Excel

Methods to Find Special Characters in Excel

  • Finally, you will get the result in the image below.

  • If we change any data where no special character exists then it will show FALSE in the box.


Similar Readings


3. Applying User-Defined Function

Now, we will try finding special characters by applying a User-Defined Function. The steps are described below:

Steps:

  • Like the second method, at first open the VBA window and insert the following code and save the code.
Public Function Check_Special_Characters(nTextValue As String) As Boolean
Check_Special_Characters = nTextValue Like "*[!A-Za-z0-9 ]*"
End Function
  • After saving the code in the C5 cell insert the following formula.
=Check_Special_Characters(B5)
  • Like the previous method, you will find TRUE if the cell contains a special character. Otherwise, it will show FALSE. Then use Fill Handle to apply it to all columns.

Methods to Find Special Characters in Excel

  • After that, you will gonna find the below result.


How to Replace Special Characters in Excel

In some cases, the special characters create so much confusion that they need to be replaced. To replace special characters we will follow the below steps:

Steps:

  • First, write the data without the characters in the C5 cell.

Replace Special Characters in Excel

  • Then, go to the Home tab and select Flash Fill from the Fill option.

  • Finally, you will get a result similar to the below image.

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


Things to Remember

  • The first method is the quickest and easiest way among all of them.
  • In the case of using VBA code, it should be remembered that it needs to be saved as an Enable-Macro file.
  • In the case of VBA code, the code has to be saved first after that the formulas will work.

Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to find special characters in excel. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Zehad Rian Jim
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo