VBA Replace Function

Description

The Excel VBA Replace function searches for a substring within a string and replaces occurrences of the substring with a second substring.

The syntax of the function is:

Replace( Expression, Find, Replace, [Start], [Count], [Compare] )

Where the function arguments are:

Expression

The string that you want to search.

Find

The substring that you want to find (that is to be replaced).

Replace

The substring that you want to replace the Find substring with.

[Start]

An optional integer argument, representing the position within the supplied Expression that the returned string should start at.

If omitted, the [Start] argument takes on the default value of 1.

[Count]

The number of occurrences of the Find substring that you want to replace.

If omitted, the [Count] argument takes on the default value of -1, meaning that all occurrences of the Find substring should be replaced.

[Compare]

An optional argument, specifying the type of comparison to make when evaluating the substrings.

This can be any of the following values:

vbBinaryCompare	-	performs a binary comparison
vbTextCompare	-	performs a text comparison
vbDatabaseCompare	-	performs a database comparison

If omitted, the [Compare] argument takes on the default value vbBinaryCompare.

VBA Replace Function Examples

Example 1

' Replace all occurrences of the name "John" with "Bill".

								Dim oldStr As String

								Dim newStr As String

								oldStr = "It is John's Birthday today. Happy Birthday John!"

								newStr = Replace( oldStr, "John", "Bill" )

' Now, the variable newStr = "It is Bill's Birthday today. Happy Birthday Bill!".

In the above example, the VBA Replace function returns the string "It is Bill's Birthday today. Happy Birthday Bill!".

Note that:

The [Start] argument has not been provided to the function and so it uses the default value 1;
The [Count] argument has not been provided to the function and so it uses the default value -1 (i.e. replace all occurrences of the substring);
The [Compare] argument has also not been provided to the function and so it uses the default value vbBinaryCompare;

Example 2

								' Starting from position 30 of the supplied expression, replace

									' all occurrences of the substring "John" with the substring "Bill".

								Dim oldStr As String

								Dim newStr As String

								oldStr = "It is John's Birthday today. Happy Birthday John!"

								newStr = Replace( oldStr, "John", "Bill", 30 )

' The variable newStr now equals "Happy Birthday Bill!".

In the above example, the VBA Replace function returns the string "Happy Birthday Bill!".

Note that the supplied [Start] argument is equal to 30 and so the returned text string starts at position 30 of the orginal Expression.

Example 3

								' Replace the first two occurrences of the substring "5" with the substring "4".
							

								Dim oldStr As String

								Dim newStr As String

								oldStr = "We have 5 oranges, 5 apples and 5 bananas"

								newStr = Replace( oldStr, "5", "4", , 2 )

' Now, the variable newStr = "We have 4 oranges, 4 apples and 5 bananas".

In the above example, the VBA Replace function returns the string "We have 4 oranges, 4 apples and 5 bananas".

Note that:

The [Start] argument has not been provided to the function and so it uses the default value 1;
The supplied [Count] argument is equal to 2, so only the first two occurrences of the substring "5" are replaced.

Example 4

' Remove all occurrences of the substring "5 ".

								Dim oldStr As String

								Dim newStr As String

								oldStr = "We have 5 oranges, 5 apples and 5 bananas"

								newStr = Replace( oldStr, "5 ", "" )

' Now, the variable newStr = "We have oranges, apples and bananas".

In the above example, the VBA Replace function returns the string "We have oranges, apples and bananas".

In this example, the substring "5 " has been replaced with an empty string "". This has the effect of simply removing all occurrences of the substring "5 ".

Return to the List of VBA Functions
Return to the ExcelFunctions.net Home Page