SUBSTITUTE

Description

Use this function to replace occurrences of a specified substring within a text string with another substring. Supported in Chains.

SUBSTITUTE is useful for replacing specific text in a string, especially when you need to make multiple replacements.

SUBSTITUTE(text, old_text, new_text, [instance_num])

This function accepts the following arguments:

Name	Required	Description	Valid input
`text`	Yes	The text or the reference to a cell containing text in which you want to substitute characters.	A text string (including numbers), a cell reference containing the text string, or a formula which results in either of these.
`old_text`	Yes	The text you want to replace.	A text string (including numbers), a cell reference containing the text string, or a formula which results in either of these.
`new_text`	Yes	The text you want to replace `old_text` with.	A text string (including numbers), a cell reference containing the text string, or a formula which results in either of these.
`instance_num`	No	Specifies which occurrence of `old_text` you want to replace. If omitted, every occurrence of `old_text` in `text` is replaced.	A positive integer, a reference to a cell containing a positive integer, or a formula which results in either of these.

Formula	Description	Result
`=SUBSTITUTE(A1, "p", "b")`	Replaces all occurrences of "p" with "b" in "apple".	abble
`=SUBSTITUTE(B1, "a", "o")`	Replaces all occurrences of "a" with "o" in "banana".	bonono
`=SUBSTITUTE(A2, "e", "o", 1)`	Replaces the first occurrence of "e" with "o" in "grape".	grope
`=SUBSTITUTE(B2, "e", "a")`	Replaces all occurrences of "e" with "a" in "orange".	oranga
`=SUBSTITUTE(A3, "e", "i")`	Replaces all occurrences of "e" with "i" in "melon".	miloni
`=SUBSTITUTE(B3, "m", "p")`	Replaces all occurrences of "m" with "p" in "lemon".	lepon

SUBSTITUTE is case-sensitive. If you need a case-insensitive replacement, consider using UPPER or LOWER to normalize the text case first.
If instance_num is specified, only the specified instance of old_text is replaced. If omitted, all instances are replaced.
SUBSTITUTE does not support wildcards.

Use SUBSTITUTE to clean or normalize data by replacing unwanted characters or substrings.
Combine SUBSTITUTE with other text functions such as LEN, MID, or FIND to manipulate text strings more effectively.
SUBSTITUTE can be nested to perform multiple replacements in a single formula.