VBA Replace Function

Syntax:

In the examples below we will show you usage and explain the arguments in the Replace function.

An example of using the expression, find and replace arguments

Our expression (source string) in the example below is:

VBA code:

Sub replace_ex()

Dim Str As String

    Str = "That is good!"

    MsgBox (Replace(Str, "is", "was"))

End Sub

Output:

So, in the above example, we replaced the word “is” with “was”.

The example of using the start argument in the Replace function

You can specify where to start replacement in the source string by using the Start argument.

For example, our source string is:

“This is good”

We specify start = 5 in the example below:

Sub replace_ex()

Dim Str As String

    Str = "This is good!"

    MsgBox (Replace(Str, "is", "was", 5))

End Sub

Result:

So returned result was only replaced from the 5^th character and returned the remaining string after replacement.

Using the count argument example

Consider this string that we will use for our example below:

“COBOL is cool! VBA is good”

We only want to replace “is” with “was” for the first sentence and want to get a complete sentence. See how it is done:

Sub replace_ex()

Dim Str As String

    Str = "COBOL is cool! VBA is good"

    MsgBox "String After Replace = " & (Replace(Str, "is", "was", , 1))

End Sub

Output:

If we had not used Count, the output after using this line:

Does Replace perform case-sensitive replacements?

The default behavior of the Replace function is case-sensitive replacements. The example below shows this:

Sub replace_ex()

Dim Str As String

    Str = "COBOL was Good! VBA is good"

    MsgBox "String After Replace = " & (Replace(Str, "good", "Awesome"))

End Sub

Result:

So, we wanted to replace “good” with “Awesome” and replace only done for starting with a small ‘g’.

How to perform case-insensitive by Compare argument

So, if you want to perform case-insensitive replacements, the answer is simple:

Use the sixth argument - Compare

The compare argument has the following possible constant values:

Our answer is the third constant i.e. vbTextCompare. You may either use it or its value 1.

See the same example as above and now the output:

Sub replace_ex()

Dim Str As String

    Str = "COBOL was Good! VBA is good"

    MsgBox "String After Replace = " & (Replace(Str, "good", "Awesome", , , vbTextCompare))

End Sub

Result:

You can see that both occurrences, irrespective of the case are replaced.

Using the VBA Replace function in Excel cells

Now, as you have seen examples of using all arguments, and hope the function is pretty clear. Let us look at examples of using the VBA Replace function in the Excel sheet.

For this example, we have a number in A3 cell in this format:

457-4575-4574-7

We will write the number to B3 cell by replacing the first two dashes with spaces.

See the code and output below:

Sub replace_ex()

Range("B3").Value = Replace(Range("A3"), "-", "", , 2)

End Sub

Output:

Example of replacing in range of cells

Sub replace_ex()

Dim rng_replace As Range
Dim x

Set rng_replace = Range("A2:A5")
x = 2

For Each cell In rng_replace

Range("B" & x).Value = Replace(Range("A3"), "-", " ")

x = x + 1
Next

End Sub

Result: