VBA Replace function

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)

Replace Function Description

The VBA Replace function replaces a certain substring (find) within a given string (expression) with another substring (replace).


The syntax for the Replace function in VBA is:

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


The string to replace a substring with replace substrings.

The string searched for the expression string.

The replacement for find substrings found in expression.

Optional. The index position in expression to start search. If omitted, search will begin at position 1.

Optional. The number of occurrences to replace in expression. If omitted, will replace all occurrences of find with replace.

Optional. This can be one of the following VBA values:

Constant Value Description
vbUseCompareOption -1 Uses option compare (default)
vbBinaryCompare 0 Binary comparison (distinguishes letter case)
vbTextCompare 1 Textual comparison (ignores letter case)

Example usage

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

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

Replace "Hello Hello Hello", "Hello", "Tony", 7, 1
'Result: "Tony Hello"

Replace "Hello Hello Hello", "Hello", "Tony", 7, 2
'Result: "Tony Tony"

Replace "Hello hello Hello", "Hello", "Tony", 7, vbBinaryCompare
'Result: "hello Hello"

Replace "Hello hello Hello", "Hello", "Tony", 7, vbTextCompare
'Result: "hello Tony"

Simply the best place to learn VBA!