Identify Special Characters

excel, regex, vba

Solution

You can use a regex for this task.

A useful regex construct here is a negated character class: you use `[^...]` and insert the ranges you do not want to match in there. So, to match a char other than ASCII letters, digits, and a hyphen, use `[^a-zA-Z0-9-]`.

And use it like

Dim strPattern As String: strPattern = "[^a-z0-9-]"
Dim regEx As Object

Set regEx = CreateObject("VBScript.RegExp")
regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = strPattern

For Each cell In ActiveSheet.Range("C:C") ' Define your own range here
    If strPattern <> "" Then              ' If the cell is not empty
        If regEx.Test(cell.Value) Then    ' Check if there is a match
            cell.Interior.ColorIndex = 6  ' If yes, change the background color
        End If
    End If
Next

Problem

I need to identify cells that have certain special characters (example: !,.=]\') and mark them with a color. The column can only contain numbers (0-9), letters (a-z), as caps (A-Z) and hyphen (-). Example:

Original source

Related problems