Introduction | Example | Tutorial | Applications
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 spreadsheets with our free VBA Developer's Guide This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy to your email address below.
This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Guide can help. It’s loaded with VBA shortcuts to help you make your own macros a whole lot faster - we’ll send a copy 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
One fish, two fish, red fish, blue fish
One cat, two cat, red cat, blue cat
All instances of
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
|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
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
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!