Table of Contents
Removing special characters from a string can be done by using the string replace() or regex functions to remove any non-alphanumeric characters. The replace() function can be given a string of the characters to be removed, while the regex function allows for more complex character matching. Both of these functions can be used to create a new string with only the desired alphanumeric characters.
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.
The following tutorials explain how to perform other common tasks using VBA: