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:

    Table 11-19 Comparison Constants and Descriptions

    Constant Value Description
    vbBinaryCompare 0 Perform a binary comparison
    vbTextCompare 1 Perform a textual comparison

Return Value

Replace returns the following values:

Table 11-20 Replace Return 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:

Example 1: 

' A binary comparison starting at the beginning of the string. 
Dim MyString
MyString = Replace("XXpXXPXXp", "p", "Y")
' Output: " XXYXXPXXY".

Example 2: 

' A textual comparison starting at position 4.
Dim MyString
MyString = Replace("XXpXXPXXp", "p", "Y", 4)
'Output: XXPXXY

Example 3: 

Dim MyString
MyString = Replace("XXpXXPXXp", "X", "Y", 1, 4)
'Output: YYpYYPXXp

Example 4: 

Dim MyString
MyString = Replace("XxpXxPXxp", "X", "Y", 1, -1, 0)
'Output: YxpYxPYxp
MyString = Replace("XxpXxPXxp", "X", "Y", 1, -1, 1)
'Output: YYpYYPYYp

Example 5: 

Dim MyString2
MyString2 = Replace("XXpXXPXXp", "p", "")
' Output: "XXXXPXX".

Example 6: 

Dim MyString4
MyString4 = Replace("XXpXXPXXp", "p", "Y", 1, 0)
' Output: "XXpXXPXXp".

Example 7: 

Dim MyString5
MyString5 = Replace("XXpXXPXXp", "", "Y")
'Output: "XXpXXPXXp".