Substitutes text in a string.
SUBSTITUTE("I use spreadsheet.com","use","love")
SUBSTITUTE(C5, "1", "2", 3)
SUBSTITUTE(value, old_text, new_text, [occurrence])
- value - The text or cell range within which the substitution must be made.
- old_text - The text segment that is to be replaced.
- new_text - The text that replaces the old text.
- occurrence - [OPTIONAL] Specifies which occurrence of old_text is to be replaced with new_text.
SUBSTITUTE("I use spreadsheet.com","use","love") returns the string "I love spreadsheet.com".
SUBSTITUTE(C5,"1","2",3) substitutes the third instance of "1" in cell C5 with "2".
- old_text will match parts of words as well as whole words.
- By default, all occurrences of old_text are replaced; however, if occurrence is specified, only the indicated instance of old_text is replaced.
- SUBSTITUTE can be used to replace one or all instances of a string within value . It cannot be used to replace multiple, but not all instances within a single call.
- This function returns only text as output.