You can use the Substitute() method in VBA to replace all occurrences of specific text in a string with new text.
This method uses the following basic syntax:
Substitute(Text, Old_text, New_text, Instance_num)
where:
- Text: The text you want to substitute characters in
- Old_text: The text to replace
- New_text: The text to use as replacement
- Instance_num (optional): Which occurrence of old text to replace. If none is specified, then all occurrences of old text are replaced.
The following examples shows how to use this method in practice.
Example: How to Use Substitute in VBA
Suppose we have the following list of phrases in Excel:
Suppose that we would like to replace each space in each cell with a comma instead.
We can create the following macro to do so:
Sub SubstituteText()
Dim rng As Range, cell As Range
Set rng = Range("A2:A9")
For Each cell In rng
cell = WorksheetFunction.Substitute(cell, " ", ",")
Next
End Sub
When we run this macro, we receive the following output:
Notice that each space in each cell of column A has been replaced with a comma.
Note that we could also use the following syntax to replace each space with nothing:
Sub SubstituteText()
Dim rng As Range, cell As Range
Set rng = Range("A2:A9")
For Each cell In rng
cell = WorksheetFunction.Substitute(cell, " ", "")
Next
End Sub
When we run this macro, we receive the following output:
Notice that each space in each cell of column A has been replaced with nothing.
In other words, we have removed each of the spaces from each cell in column A.
Note: You can find the complete documentation for the VBA Substitute 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
