SUBSTITUTE

  • Updated

Substitutes text in a string.

Sample Usage

SUBSTITUTE("I use spreadsheet.com","use","love")
SUBSTITUTE(C5, "1", "2", 3)

Syntax

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. 

 

Examples

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". 


Notes

  • 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.