REPLACE(expression, find, replace [,start] [,count] [,compare])

Returns the text string with a number of characters replaced (String).

expressionThe text string expression containing substring to replace (String).
findThe text string to search for (String).
replaceThe text string to replace it with (String).
start(Optional) Position within expression where substring search is to begin.
count(Optional) Number of substring substitutions to perform.
compare(Optional) A vbCompareMethod constant specifying the type of string comparison to use (Integer):
-1 = vbUseCompareOption (uses the "Option Compare" setting)
0 = vbBinaryCompare (default, case sensitive)
1 = vbTextCompare (not case sensitive)
2 = vbDatabaseCompare (uses an Access database)

* This function is case sensitive (by default).
* This function does not support wildcards (? and *).
* This function replaces all occurrences of the "find" text string.
* 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.
* If "expression" is Null, then Null is returned.
* If "expression = "" (zero length string), then a zero length string ("") is returned.
* If "find" = "" (zero length string), then "expression" is returned.
* If "replace = "" (zero length string), then all occurrences of "find" are removed.
* If "start" > Len("expression"), then a zero length string ("") is returned.
* If "start" is left blank, then it is assumed to be 1.
* If "count" = 0, then "expression" is returned.
* If "count" is left blank, then it is assumed to be -1, which means make all possible substitutions.
* If "compare" is left blank, then -1 is used. If there is no Option Compare statement provided then vbBinaryCompare (0) is used.
* If "compare" is Null, then an error occurs.
* This is an extremely slow function and should only be called when you know a replacement will take place.
* The reason this function is so slow is because a copy of the "expression" is always made even when there are no replacements.
* You can use the INSTR function to return the position of a substring within a larger string, starting at the beginning.
* You can use the JOIN function to return a text string containing all the elements in an array.
* You can use the SPLIT function to return the array containing a specified number of substrings (using wildcard searches).
* You can use the REPLACE$ function to return a String data type instead of Variant data type.
* You can use the LIKE Operator to pattern match using wildcards.
* For more information, refer to the Replacing Strings page.
* The equivalent .NET function is Microsoft.VisualBasic.Strings.Replace
* For the Microsoft documentation refer to

Dim sValue As String 
sValue = "this is the old text"
Debug.Print Replace(sValue,"old text","new text") = "this is the new text"

Dim sValue As String
sValue = "one two one two one"
Debug.Print Replace(sValue,"one","three") = "three two three two three"

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top