Use the VBA Replace function to replace a substring of characters in a string with a new string. VBA Replace is similar to the Excel SUBSTITUTE function; both can be used to replace a portion of a string with another.

Sub VBA_Replace()
str1 = "One fish, two fish, red fish, blue fish"
str1 = Replace(str1, "fish", "cat")
End Sub

This is a very basic macro. We defined or string, then instantly changed it. You likely won’t be using your Replace function in this manner. In practice, you’ll probably be passing a cell, like Range("a1"). Yep, you can pass cells to the Replace function, too!

Anyway, when the macro finishes, the string str1 will be changed from:

One fish, two fish, red fish, blue fish


to


One cat, two cat, red cat, blue cat

All instances of fish were replaced with the string cat. This is the same way the Excel SUBSTITUTE() function replaces portions of a string.

You can see how a macro like this could be useful for replacing a “.csv” with a “.xlsx” extension in a list of file names, for example.

Okay, time to talk more about the VBA Replace function. The VBA Replace function requires 3 arguments, but it can accept as many as 6 arguments. We will give you a nice description of each of these arguments, then we will show you a few examples:

VBA Replace Function Arguments

• Expression: the original string you want to replace characters in.
• Find: the substring you want to find within your Expression. This will be the string removed from your Expression.
• Replace: what you want to replace the string you found with. This will be the string added to your Expression.
• Start (Optional): where in your Expression you want to begin finding and replacing. The default is 1, so it begins at the first character.
• Count (Optional): the number of replacements you want to make. If there are multiple instances of the substring Find, it will only replace however many you define in this argument. The default is to replace all instances.
• Compare (Optional): specifies the comparison method to be used. The options are vbBinaryCompare (default), vbDatabaseCompare, and vbTextCompare. You’ll rarely use this option so there’s no need to get too caught up on each of these options. The default is usually fine.

We know how to do a basic find and replace in a string using the Replace VBA function. Let’s look at a few examples of how the optional Replace arguments can change your results.

Replace first 2 instances in a string

Sub VBA_Replace2()
str1 = "One fish, two fish, red fish, blue fish"
str1 = Replace(str1, "fish", "cat", Count:=2)
End Sub

Result: One cat, two cat, red fish, blue fish.

Once the VBA Replace function finds the first 2 instances of the word cat, it stops replacing them and the macro ends.

Replace last occurrence of substring in a string

Sub VBA_Replace3()
str1 = "One fish, two fish, red fish, blue fish"
str1 = StrReverse(Replace(StrReverse(str1), StrReverse("fish"), StrReverse("cat"), Count:=1))
End Sub

Result: One fish, two fish, red fish, blue cat.

This is kind of an interesting one. We use the VBA function strReverse to write the string backward, and then we search for the first instance of the backward string inside the main string. The Replace function is really replacing the first instance of your string, but we have reversed the string so the first instance is really the last instance.

It sounds confusing, but it’s a neat little trick!

Replace starting at the 10th character

Sub VBA_Replace4()
str1 = "One fish, two fish, red fish, blue fish"
str1 = Replace(str1, "fish", "cat", Start:=10)
End Sub

Result: two cat, red cat, blue cat.

That’s right. When you specify a starting position, Replace truncates the characters from the 1st character to the Nth character you specify. The first 9 characters are missing! Here’s how you can fix that if you still want all the characters:

Replace starting at the 10th character and keep whole string

Sub VBA_Replace5()
i = 10
str1 = "One fish, two fish, red fish, blue fish"
str1 = Mid(str1, 1, i - 1) & Replace(str1, "fish", "cat", Start:=i)
Debug.Print str1
End Sub

Result: One fish, two cat, red cat, blue cat.

The first fish remains and the rest are replaced. We retained the first 9 characters by using the VBA Mid function.

Application Ideas - VBA Replace

The Replace function of VBA is great for manipulating strings. One great use is to replace date/time stamps, file extensions or personalized greetings for the person logged into a computer.