VBA Replace Function to Replace Characters in a String

Introduction - VBA Replace

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.

Example - VBA Replace

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

Make powerful macros with our free VBA Developer Kit

This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy, along with our Big Book of Excel VBA Macros, to your email address below.

Tutorial - VBA Replace

This is a very basic macro. I defined my 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. I’ll give you a nice description of each of these arguments, then I’ll show you a few examples:

VBA Replace Function Arguments

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. I use the VBA function strReverse to write the string backward, and then I 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 I’ve 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. I retained the first 9 characters by using the VBA Mid function.

Reader’s Note: I’ve created a really awesome user-defined function to help you replace the Nth occurrence of a substring in a string if that’s what you’re searching for!

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. I’m sure you have other great uses for it and I’d love to hear about them!

For more VBA tips, techniques, and tactics, join our VBA Insiders email series using the form below. After you do that, share this article on Twitter and Facebook.