VBA: How to Remove Special Characters from String


You can use the following basic syntax in VBA to remove special characters from strings:

Sub ReplaceSpecialChars()
Dim i As Integer
    
For i = 2 To 8
Range("B" & i) = Replace(Replace(Replace(Range("A" & i), "!", ""), "@", ""), "#", "")
Next i
End Sub

This particular example replaces the following special characters from each string in the cell range A2:A8 and outputs the new strings in cells B2:B8:

  • !
  • @
  • #

Notice that we used three nested Replace methods to remove each of these special characters from the strings.

To remove even more special characters, simply use more nested Replace methods.

The following example shows how to use this syntax in practice.

Example: Remove Special Characters from String Using VBA

Suppose we have the following list of strings in Excel:

Suppose we would like to remove the following special characters from each string:

  • !
  • @
  • #

We can create the following macro to do so:

Sub ReplaceSpecialChars()
Dim i As Integer
    
For i = 2 To 8
Range("B" & i) = Replace(Replace(Replace(Range("A" & i), "!", ""), "@", ""), "#", "")
Next i
End Sub

When we run this macro, we receive the following output:

VBA remove special characters from string

Column B displays each of the strings in column A with the special characters removed.

Note: You can find the complete documentation for the VBA Replace method here.

Additional Resources

The following tutorials explain how to perform other common tasks using VBA:

VBA: How to Count Occurrences of Character in String
VBA: How to Check if String Contains Another String
VBA: How to Count Cells with Specific Text

Leave a Reply

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