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
1 = vbTextCompare
2 = vbDatabaseCompare (uses an Access database)

REMARKS
* 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