SUBSTITUTE function

SUBSTITUTE(originalText, oldText, newText, which?) SUBSTITUTE(originalText; oldText; newText; which?)

originalText — Text or { Text }

The original text string.

oldText — Text or { Text }

The text string to be replaced.

newText — Text or { Text }

The replacement text.

which — Number or { Number } (optional)

A number which specifies which occurrence of the second parameter to replace (counting from the left). If omitted, 1 is assumed.

Returns — Text or { Text }

The resulting text string.

Substitutes new text for old text in a text string and returns it. SUBSTITUTE("abc def", "def", "xyz")SUBSTITUTE("abc def"; "def"; "xyz") returns "abc xyz", a version of the first parameter where all occurrences of the second parameter, "def", have been replaced by the third parameter, "xyz".

Use REPLACE to replace text where the position of the text to be replaced is known. Use REGEXREPLACE to replace text using powerful regular expressions which find the text to replace.

Examples

SUBSTITUTE("abc def", "def", "xyz")SUBSTITUTE("abc def"; "def"; "xyz")

Returns "abc xyz".

REGEXREPLACE("abc def", "d\w\w", "xyz")REGEXREPLACE("abc def"; "d\w\w"; "xyz")

Returns "abc xyz". The regular expression "d\w\w" matches a literal "d", followed by exactly two letters, thereby matching "def".