Excel VBA user defined function to remove special characters from text in excel

Many times we need to remove specific character from text string in excel worksheet. We usually perform this activity as a data cleansing process before generating reports/dashboard in reporting tools like Microsoft Excel, Tibco Spotfire, Tableau, Qlikview and so on. Removing specific character from excel cells manually is a quiet time consuming task. Also, there might be a possibility to forget to miss few characters in data which is not adequate.

We can automate this task by creating a user defined function which will be used to delete specific character from the input text. We can customize a function in such a way that it will remove specific character or special character from input text.

Here is the VBA function which is used to remove special character from input text:

Function Remove_Special_Character(ByVal iString As String) As String
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

Dim char As Variant

Const SpecialCharacters As String = "~,!,@,#,$,%,^,&,*,(,),{,[,],},<,>"  'Modify Special Characters as per your needed

For Each char In Split(SpecialCharacters, ",")
    iString = Replace(iString, char, "")
Next

Remove_Special_Character = iString

End Function