Identify Special Characters

Question

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:

Solution 1

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

Solution 2

Without regex:

This macro processes column B:

Sub marine()
    Dim r As Range, rng As Range, s As String
    Dim i As Long, L As Long

    Set rng = Intersect(Range("B:B"), ActiveSheet.UsedRange)

    For Each r In rng
        If r.Value <> "" Then
            s = Replace(r.Text, "-", "")
            L = Len(s)
            For i = 1 To L
                If Not Mid(s, i, 1) Like "[0-9a-zA-Z]" Then
                    r.Interior.Color = vbYellow
                End If
            Next i
        End If
    Next r
End Sub

It will accept only numerals, upper and lower case letters, and the dash.

Comments

[DejaVuSansMono]

Regex -> stackoverflow.com/questions/22542834/…

[Wiktor Stribiżew]

It seems that beside the letters and digits, the cells can have -, too, right?

[Slai]

It can also be done without VBA macro with Conditional Formating formula stackoverflow.com/questions/29855647/…

[Slai]

The downvotes on your answers confuse me so I +1 as I don't see any issue. Downvoters please give any hint as to why this answer not useful.

[Gary's Student]

@Slai Thee may be a bug in there somewhere.............I will re-examine the answer.

[Deluq]

I did not downvote your answer, thanks for your time

[Gary's Student]

@Deluq I usually get downvotes if I post code with no explanation....thanks for the feedback!

[Deluq]

Yeah, actually I liked your answer without regex

[Vinoth Narayan]

The above code works fine.. I need the special characters count

[Wiktor Stribiżew]

@VinothNarayan Is it a new question? Actually, it is too easy: instead of the whole For Each block, just use Set ms = regEx.Execute(cell.Value) and then use ms.Count.