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