Deleting Special Characters

[iamtheboss32]

Hi Good Day everyone!


I have an excel file that needs to remove all special characters except for the following

1.)A-Z & a-z
2.) 0-9
3.), . - _ é É

I have found a vba code but I really cant update it cause I might break the file. This code only removes the characters under xChars. is there any way to reverse since characters that I want to retain are fewer than all special characters.

here is the code I found on google

Function RemoveSpecial(Str As String) As String
Dim xChars As String
Dim I As Long
xChars = "#$%()^*&"
For I = 1 To Len(xChars)
Str = Replace$(Str, Mid$(xChars, I, 1), "")
Next
RemoveSpecial = Str
End Function


Thanks,

 

[iamtheboss32]

additional to that is it possible to also highlight the cells with special characters ?

 

[wideboydixon]

Public Function RemoveSpecial(inputString As String) As String

Const AllowedCharacters = "abcdefghijklmnopqrstuvwxyz0123456789,.-_é"

Dim i As Long

For i = 1 To Len(inputString)
    If InStr(1, AllowedCharacters, Mid$(inputString, i, 1), vbTextCompare) > 0 Then
        RemoveSpecial = RemoveSpecial & Mid$(inputString, i, 1)
    End If
Next i

End Function

WBD

 

[wideboydixon]

For highlighting, use a conditional formatting formula e.g.

=A1<>RemoveSpecial(A1)

WBD

 

[iamtheboss32]

Public Function RemoveSpecial(inputString As String) As String

Const AllowedCharacters = "abcdefghijklmnopqrstuvwxyz0123456789,.-_é"

Dim i As Long

For i = 1 To Len(inputString)
    If InStr(1, AllowedCharacters, Mid$(inputString, i, 1), vbTextCompare) > 0 Then
        RemoveSpecial = RemoveSpecial & Mid$(inputString, i, 1)
    End If
Next i

End Function

WBD

Thanks for the reply. just wondering I am going to add this on my module or should I create a new module for this. Sorry this is my 1st time to use VBA. really have limited knowledge in understanding the codes.

 

[wideboydixon]

Hey. Use [Alt]+[F11] to open the VBA editor and the Insert->Module. Paste the above function into that module. You can then use it in a cell on the sheet like this:

=RemoveSpecial(A1)

WBD

 

[iamtheboss32]

Hey. Use [Alt]+[F11] to open the VBA editor and the Insert->Module. Paste the above function into that module. You can then use it in a cell on the sheet like this:

=RemoveSpecial(A1)

WBD

Thaks a lot It is now working. I think I need to enroll myself on an advance class for excel. Thanks again!!!