VBA Replace function

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

Description

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

vba replace

Quick example:

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

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:

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

Examples

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

Replace all instances

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 first 2 instances

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

Replace distingiush Letter Case

In some cases the replacement Letter Case is important to you

Otherwise the default is no letter case:

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:

Similarly:

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):