Replace Function

Returns a string in which a specified substring has been replaced with another substring a specified number of times.

Syntax

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

Arguments

  • expression
    Required. String expression containing substring to replace.

  • find
    Required. Substring being searched for.

  • replacewith
    Required. Replacement substring.

  • start
    Optional. Position within expression where substring search is to begin. If omitted, 1 is assumed. Must be used in conjunction with count.

  • count
    Optional. Number of substring substitutions to perform. If omitted, the default value is -1, which means make all possible substitutions. Must be used in conjunction with start.

  • compare
    Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values. If omitted, the default value is 0, which means perform a binary comparison.

Settings

The compare argument can have the following values:

Constant

Value

Description

vbBinaryCompare

0

Perform a binary comparison.

vbTextCompare

1

Perform a textual comparison.

Return Value

Replace returns the following values:

If

Replace returns

expression is zero-length

Zero-length string ("").

expression is Null

An error.

find is zero-length

Copy of expression.

replacewith is zero-length

Copy of expression with all occurrences of find removed.

start > Len(expression)

Zero-length string.

count is 0

Copy of expression.

Remarks

The return value of the Replace function is a string, with substitutions made, that begins at the position specified by start and concludes at the end of the expression string. It is not a copy of the original string from start to finish.

The following example uses the Replace function to return a string:

' This is a binary comparison (because the last argument is omitted),
' so the comparison is case-sensitive.
' Returns ABC123def
Result = Replace ("ABC123abc", "abc", "def")

' This is a text comparison, so the comparison is case-insensitive.
' Returns def123def
Result = Replace ("ABC123abc", "abc", "def", 1, -1, vbTextCompare)

Requirements

Version 2

Change History

Date

History

Reason

September 2009

Modified example.

Information enhancement.

See Also

Filter Function