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.

Find Special Characters in Excel: 3 Easy Methods

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 steps below 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.

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

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

• Afterward, the Custom Column window will open on the screen.
• Then paste the following formula in the Custom column formula tab.

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

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

• Lastly, you will get the result below.

2. Applying VBA Code

In this case, we want to find a special character by using 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.

• 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

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

• Next, you will find the result for that cell. In this case, as the cell contains special characters, it shows the result as TRUE.
• Then, use the Fill Handle to use the same for the whole column.

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

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

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

• After that, you will find the result below.

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.

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

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.

Download Practice Workbook

You can download the practice workbook from here.

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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

<< Go Back to Characters | Learn Excel