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:

Regex -> http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — DejaVuSansMono, Oct 28 '16 at 18:40
It seems that beside the letters and digits, the cells can have `-`, too, right? — Wiktor Stribiżew, Oct 28 '16 at 19:28
It can also be done without VBA macro with Conditional Formating formula http://stackoverflow.com/questions/29855647/check-if-cell-contains-non-alpha-characters-in-excel — Slai, Oct 28 '16 at 22:21

score 5 · Accepted Answer · answered Oct 28 '16 at 19:40

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

The above code works fine.. I need the special characters count — Vinoth Narayan, Jun 06 '18 at 07:32
@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`. — Wiktor Stribiżew, Jun 06 '18 at 07:55

Gary's Student · Answer 2 · 2016-10-28T21:33:54.467

4

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.

edited Oct 28 '16 at 21:33

answered Oct 28 '16 at 20:01

Gary's Student

95,722
10
59
99

1

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. – Slai Oct 28 '16 at 22:16
@Slai Thee may be a bug in there somewhere.............I will re-examine the answer. – Gary's Student Oct 28 '16 at 23:23
I did not downvote your answer, thanks for your time – Deluq Oct 31 '16 at 14:31
@Deluq I usually get downvotes if I post code with no explanation....thanks for the feedback! – Gary's Student Oct 31 '16 at 14:33
Yeah, actually I liked your answer without regex – Deluq Oct 31 '16 at 15:16

Identify Special Characters

2 Answers2

Linked