Quick Jump
Introduction | Example | Tutorial | Applications | Comments

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

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
VBA Replace Function Arguments

Argument   Description
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. 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!

Comments

Please, please, please subscribe to my email list and let’s have a discussion in the comments section!
Share this article on social media and follow me on Google+ and Twitter for more great VBA content!


Get wellsrPRO for Free

Get these macros delivered right to your spreadsheet for free

Access tutorials and import my macros without ever leaving Excel

Show me - It's free

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.