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
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.