VBA macro to remove special characters from a string in excel

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:

excel-macro-output-remove-special-characters

 

 

 

 

 

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.

About the Author

SRINI S

A passionate blogger. Love to share solutions and best practices on wordpress hosting, issues and fixes, excel VBA macros and other apps

Leave a Reply

Your email address will not be published. Required fields are marked *