Zehad Rian Jim
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.
- Then, go to the Data tab, and from the Get & Transform Data option select From Table/Range option.
- After, 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.
=Text.Remove([Global Trade Item Number],{"A".."z","0".."9"})
- 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 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.
- 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:
=FindSpecialCharacters(B5)
- Next, you will find the result for that cell. In this case, as the cell contains special characters so 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.
Similar Readings
- How to Set Character Limit in Excel
- Check Character Limit in Excel (With Easy Steps)
- How to Insert Character Between Text in Excel (5 Easy Methods)
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.
- 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.
- 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.
