REPLACE(expression, find, replace [,start] [,count] [,compare])
Returns the text string with a number of characters replaced (String).
|expression||The text string expression containing substring to replace (String).|
|find||The text string to search for (String).|
|replace||The 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
1 = vbTextCompare
2 = vbDatabaseCompare (uses an Access database)
|* This function is not case sensitive (by default). |
* 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 function helps to simplify string search and replace operations however it doesn't allow you to perform wildcard searches.
* 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 check for replacements.
* You can use the JOIN function to return the text string containing the elements in an array (using wildcard searches).
* 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/String data type.
* This function was added in Office 2000.
* The equivalent .NET function is Microsoft.VisualBasic.Strings.Replace
* For the Microsoft documentation refer to docs.microsoft.com
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"
© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited Top