How to search for special characters in Excel
By
SpreadCheaters
This dataset contains information about individuals’ IDs, names, occupations, and salaries. It includes names with special characters from various languages and scripts, such as diacritics, non-Latin characters, and symbols, adding linguistic and cultural diversity to the dataset. In this tutorial, we will learn how to highlight the cells containing special characters making it easier to search for special characters.
In Excel, special characters refer to any character that is not a letter, number, or typical punctuation mark. These characters often have specific functions or meanings within Excel formulas, data formatting, or other operations.
Step 1 – Add a module
For adding a module, navigate to the Developer tab.
After that, click on the first option named Visual Basic.
You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well.
Then, click on the Insert tab in this window and click on the Module option.
Now, a new module would open.
Step 2 – Write the code
– Now, write the following code in the module
Sub Highlight_SPC_CHAR()
Dim rng As Range
Dim cell As Range
Set rng = ActiveSheet.UsedRange ‘ Change to the desired sheet
For Each cell In rng
If ContainsSpecialCharacters(cell.Value) Then
cell.Interior.Color = RGB(0, 100, 100) ‘ Change the color as desired
cell.Font.Color = RGB(255, 255, 255)
End If
Next cell
End Sub
Function ContainsSpecialCharacters(str As String) As Boolean
Dim i As Long
For i = 1 To Len(str)
ch = Mid(str, i, 1)
Select Case ch
Case “0” To “9”, “A” To “Z”, “a” To “z”, ” “
‘ Continue checking
Case Else
ContainsSpecialCharacters = True
Exit Function
End Select
Next i
ContainsSpecialCharacters = False
End Function
– Then, close the VBA Editor by using the “Alt+Q” shortcut key.
Step 3 – Run the macro
Use the “Alt+F8” key to open the “Run a macro” dialogue box.
Then, click on the macro named “Highlight_SPC_CHAR”.
After doing that, click on the “Run” button and all the cells containing special characters will be highlighted.
