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, " ", ",")
NextEnd 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, " ", "")
NextEnd Sub

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 .

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