REPLACE |
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 (1). |
count | (Optional) Number of substring substitutions to perform (-1). |
compare | (Optional) A vbCompareMethod constant specifying the type of string comparison to use (Integer): -1 = vbUseCompareOption (uses the "Option Compare" setting) 0 = vbBinaryCompare (case sensitive) (default) 1 = vbTextCompare (not case sensitive) 2 = vbDatabaseCompare (uses an Access database) |
REMARKS |
* This function is case sensitive (by default). * This function does not support wildcards (? and *). * This function replaces all occurrences of the "find" text string. * 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 learn.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"
sValue = "one two one two one"
Debug.Print Replace(sValue,"one","three") '= "three two three two three"
sValue = "10 20 30"
Debug.Print Replace(sValue, "0", "1") '= "11 21 31"
sValue = "better SOLUTIONS"
Debug.Print Replace(sValue, "better", "great") '= "great SOLUTIONS"
sValue = "better SOLUTIONS" ' is case sensitive
Debug.Print Replace(sValue, "BETTER", "great") '= "better SOLUTIONS"
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top