Simple VBA macro code to remove special characters from a string in excel. You can remove either all special characters or choose which special characters to remove from the string.
This macro retrieves each character from the string and checks whether it belongs to A-Z,a-z,0-9 or a space.If it belongs then it stores them into another string, else it ignores that. Finally, you will have a string without special characters.
Sub removeSpecialCharactersFromString()
Dim stringWithSpecChars, stringWithoutSpecChars, character, charPos
charPos = 1
stringWithSpecChars = "I have 15$ (Dollars)@" ' example string containing special characters $, @, (, )
Do
character = Mid(stringWithSpecChars, charPos, 1)
' If the character belongs to A-Z, a-z, 0-9 or space then store it in another string
If character Like "[A-Za-z0-9 ]" Then
stringWithoutSpecChars = stringWithoutSpecChars & character
Else
' Do nothing
End If
charPos = charPos + 1
Loop Until charPos > Len(stringWithSpecChars)
MsgBox ("The string after removing special characters is -> " & Chr(34) & stringWithoutSpecChars & Chr(34))
End Sub
Output:
Note: If you don’t want to remove any special character from the string, for example $, then you can replace “[A-Za-z0-9 ] with “[A-Za-z0-9 $]” in above code.
