How to search for special characters in Excel

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.