This is an old revision of the document!
Table of Contents
Replace(text, old_text, new_text)
Category: Text function
Description
This function replaces all instances of old_text with new_text within text.
Arguments
| Argument | Type | Description |
|---|---|---|
| text | Text | Any text value or value that can be implicitly converted to text. |
| old_text | Text | The text to be found within text and replaced by new_text, if found. |
| new_text | Text | The text that replaces old_text if found within text. |
Return value type: Text
Remarks
This function is case sensitive.
Entering '' (two single quotes; an empty string) as the new_text value has the same effect as using the RemoveText function.
Number values entered for text, old_text, and new_text are implicitly converted to text.
Multiple numeric 0s (zeroes) as old_text or new_text
Use caution when searching for, or replacing with, a numeric value of multiple zeroes, such as 000. These values are implicitly converted to a single zero in text form and will search for/replace with a single zero. To search for or replace using multiple zeros, enter the value as text, using quotes.
replace(123456,345,000) //Returns 1206, as the 000 is converted to a single 0.
replace(120056, 00, 99) //Returns 12999956, as the 00 is converted to 0 and replaced with 99 in each instance.
To remedy this issue, enter the 000 and 00 values as text: "000" and "00", respectively.
replace(123456,345,'000') //Returns 120006, as expected.
replace(120056, '00', 99) //Returns 129956, as expected.
Examples
replace('Food & Beverage', '&', 'and') //Returns 'Food and Beverage'
replace('New York', 'york', 'Orleans') //Returns 'New York' ('york' isn't found due to the case of 'y'.)
replace('New Mexico', ' ', '_') //Returns 'New_Mexico' (Replaces spaces with underscores.)
replace(123456789, 456, 654) //Returns '123654789' (Number implicitly converted; return value is text.)
replace(190091, "00", 5) //Returns '19591' (Enter multiple zeroes as text; return value is text.)