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