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:
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