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

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, "")

Remove-Special-Character = iString

End Function