find all the special characters in the sheets

[ramyadixit99]

Hi,

I have 3 sheets of excel each having a size of 10 mb.
I need to find all the special characters in that sheet and then replace it with other values.
I did it manually and it took me almost 6 hrs to do.
I have started using macros, and now i need to a macro to find all the special characters there is in the sheet.

Regards,
Ramya

 

[yaniskyr]

Reviving this thread

Is there a way to replace the special characters with non-special ones instead of highlighting them?

 

[yaniskyr]

Try this code.

It paints in yellow all the cells that have a text constant that has a character not in the list that you posted.

Sub ReplChars()
Dim sCharOK As String, s As String
Dim r As Range, rc As Range
Dim j As Long

sCharOK = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,-.:;{}[]_"

Set r = Worksheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants, xlTextValues)

' loop through all the cells with text constant values and paints in yellow the ones with characters not in sCharOK
For Each rc In r
    s = rc.Value
    For j = 1 To Len(s)
        If InStr(sCharOK, Mid(s, j, 1)) = 0 Then
            rc.Interior.Color = vbYellow
            Exit For
        End If
    Next j
Next rc

End Sub

Actually highlighting works fine for my process, however I have another question. How can I restrict this to run in 3 specific columns only?