Replace()

Category: Text

Syntax: Replace ( text; start; numberOfCharacters; replacementText )

Parameters:

textAny expression that resolves to a text string.

startAny positive number or expression that returns a positive number.

numberOfCharactersAny positive number or expression that returns a positive number.

replacementTextAny expression that resolves to a text string.

Data type returned: Text

Description:

The Replace() function extracts a segment of a text string and replaces it with some other string. The segment to extract begins with the start character number and extends for numberOfCharacters. The replacement string is specified by the replacementText parameter.

The extracted segment and the replacement text do not need to be the same length.

The Replace() and Substitute() functions are often confused with one another. Substitute() replaces all occurrences of a particular substring with another string, whereas Replace() replaces a specified range of characters with another string.

Replace() is often used for manipulation of delimited text arrays. There is no function that will directly replace the contents of a particular item in an array with another. The Replace() function can do this by finding the appropriate delimiters and inserting the replacement item. For instance, if you have a pipe-delimited list of numbers (for example, 34|888|150|43) and you wish to increase the third item in the list by 18, you could use the following formula:

 Let ([
 item = 3;
 increase = 18;
 start = Position (myArray; "|"; 1; (item-1)) + 1;
 end = Position (myArray; "|"; 1; item);
 itemValue = Middle (myArray; start; end-start);
 newValue = itemValue + increase ];
 Replace (myArray; start; end-start; newValue)
 )

Given the example string as myArray, this would produce the string 34|888|168|43. Typically, the item and increase values would be supplied by other fields and not hard-coded into the formula.

Another great use of the Replace() function is to use it as an "Insert" function: Pass a zero as the number of characters and you will simply insert some amount of text without having to use a combination of Left(), Middle(), and Right() functions.

Examples:

Function	Results
Replace ("abcdef"; 4; 2; "TEST")	Returns abcTESTf.
Replace ("Fred Smith"; 1; 4; "Joe")	Returns Joe Smith.
Replace ( "leftright"; 5; 0; "middle" )	Returns leftmiddleright.