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

 

[pgc01]

Hi Ramya
Welcome to the board

Sorry, If you read your post you'll see that it's difficult to make sense of it. You say you want to find some "special characters" but you don't explain what that means. What characters are you referring to?

Also, you could post the code you wrote and that is not working.

 

[ramyadixit99]

Hi,

Am sorry for not being clear.

I would like to write a macro that can detect any special character other than the following
1. a-z and A-Z
2. 0-9
3. , - . : ; { } [ ] _

Thanks and Regards,
Ramya

 

[pgc01]

Hi Ramya

OK.

What are those "special characters" replaced with?

 

[ramyadixit99]

Hi,

Maybe just highlight those cells that have these characters.

Regards,
Ramya

 

[pgc01]

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

 

[ramyadixit99]

Hi,

Thank you. It recognized most of it.
But there are sentences with spaces in the end of the line. I wanna ignore those too..
i changed the character string to
sCharOK = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789,-.:;{}[]_ &'\/<>&%+=@#!$^*()" & Chr(34)
to include a few more characters. How do i include white spaces?

Regards,
Ramya

 

[pgc01]

HI

Just include it in the string like any other character, ex: "abc ", you have a,b,c and a space.

 

[ramyadixit99]

Thank you so much for the reply.
It worked!

 

[pgc01]

You're welcome. Thanks for the feedback.