Help with Macro to Find Special Characters

[garylundblad]

I have a need to find special/foreign characters in a large file, basically anything that isn't on a list of acceptable characters (see attached file), and highlight the cell that contains any special character.

Thank you!

Gary

 

[Khalid NGO]

Hi Gary,
Good day...

If you are okay with formula based solution, try the following {array formula}

=SUM(--OR(ISNUMBER(SEARCH($B$4:$B$40,E5))))

Result will be 1 or 0
1 if the charachter is found within the cell, else 0.

{array formula needs to be entered with
a key combination of Ctrl+Shift+Enter}

E5 is supposed as your cell.

Regards,

 

[Khalid NGO]

One more with just enter:
=SUMPRODUCT(COUNTIF(E5,"*"&$B$4:$B$40&"*"))

Regards,

 

[Chihiro]

For VBA something like below?

Sub SpecialChar()
Dim strPatt As String
Dim cel As Range
Dim regEx As Object

strPatt = "[^a-z,.;:#$%()@" & Chr(34) & "]" 'change pattern as needed

Set regEx = CreateObject("VBScript.RegExp")

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPatt
    End With

    For Each cel In Range("E4:E" & Cells(Rows.Count, "E").End(xlUp).Row).Cells 'change column as needed
        If regEx.Test(cel.Text) Then
            cel.Interior.Color = vbRed
        End If
    Next cel

End Sub

 

[garylundblad]

Thank you very much. For some reason the first solution didn't work when the special character was in a cell with other characters, but maybe I wasn't doing something right. I did enter it as an array, which I missed at first. I didn't try the second option, but thank you for it. The macro works beautifully. I only had to add a space to the allowable characters.

Thank you all for your fast and great solutions!

Gary