SUBSTITUTE

Replaces occurrences of a subtext with another subtext

Usage

SUBSTITUTE(text, old_text, new_text, occurrence: ALL, case_sensitive: TRUE)

text	(Required) The text string in which replacements will be made.
old_text	(Required) The substring to be replaced.
new_text	(Required) The substring that will replace occurrences of the old_text.
instance_num	The instance to replace. Default is `NOTHING`, replacing all occurrences.

Example	Result
SUBSTITUTE(“apple, banana, cherry”, “banana”, “orange”)	“apple, orange, cherry”
SUBSTITUTE(“Many words here! Many words here!”, “Many words”, “Over”, 2)	“Many words here! Over here!”
SUBSTITUTE(“Canada”, “a”, “x”, 4)	“Canada”
SUBSTITUTE(“aaAAbbBB”, “b”, “x”)	“aaAAxxBB”

If there are too few instances of the old_text to reach instance_num, then nothing happens.