Description

The VBA Replace function replaces a certain substring (find) within a given string (expression) with another substring (replace). It allows you to mind or ignore Letter Case when replacing substrings.

See this quick example below of how you can use the VBA Replace function to replace a single word “there” with “Michael” in the sentence “Hello there!”:

expression = "Hello there!"
find_txt = "there"
replace_with = "Michael"
new_txt = Replace(expression, find_txt, replace_with)

'Result: Hello Michael!

VBA Replace is similar to the Excel SUBSTITUTE function. In fact you can even use the Excel Subsitute function in VBA (although not necessary):

WorksheetFunction.Substitute(expression, find_txt, replace_with)

Syntax

The syntax for the Replace function in VBA is:

Replace ( expression, find, replace, [start, [count, [compare]]] )

Parameters

expression
The string to replace a substring with replace substrings.
find
The string searched for the expression string.
replace
The replacement for find substrings found in expression.
start
Optional. The index position in expression to start search. If omitted, search will begin at position 1.
count
Optional. The number of occurrences to replace in expression. If omitted, will replace all occurrences of find with replace.
compare
Optional. This can be one of the following VBA values:

Examples

The Replace function can be used in VBA code. Let’s look at some VBA Replace function examples:

Replace all instances

Replace "Hello World!", "World", "Tony"
'Result: "Hello Tony!"

Replace first 1 instance

Use the Count parameter to specify the amount of instances you want to replace. Below will replace only the first instance:

Replace "Hello Hello Hello", "Hello", "Tony", count:= 1
'Result: "Tony Hello"

Replace first 2 instances

Similarly as above we can replace only the first 2 instances of Hello:

Replace "Hello Hello Hello", "Hello", "Tony", count:= 2
'Result: "Tony Tony"

Replace distingiush Letter Case

In some cases the replacement Letter Case is important to you

Replace "Hello hello Hello", "Hello", "Tony", compare:= vbBinaryCompare
'Result: "hello Hello"

Otherwise the default is no letter case:

Replace "Hello hello Hello", "Hello", "Tony", compare:= vbTextCompare
'Result: "hello Tony"

Replace all instances starting as specific character

Assuming you simply want to replace any number of finds starting at a certain character use the Start parameter:

Replace "Hello hello Hello", "Hello", "Tony", start:=7
'Result: "Hello Tony Tony"

Similarly:

Replace "Hello hello Hello hello", "Hello", "Tony", start:=7
'Result: "Hello Tony Tony, Tony"

Replace last occurance

To replace the last occurance (or any number of occurances from the end) we need to use the VBA StrReverse function.

The following will replace the last occurance (or first from the end):

StrReverse(Replace(StrReverse("Hello Hello Hello"), StrReverse("Hello"), StrReverse("Tony"), Count:=1))
'Result: "Hello Hello Tony"