Replace text based on content
=SUBSTITUTE(text, old_text, new_text, [instance_num])| Parameter | Description |
|---|---|
text |
The text to change. |
old_text |
The text to replace. |
new_text |
The text to replace with. |
instance_num |
[optional] The instance to replace. If not supplied, all instances are replaced. |
The SUBSTITUTE function is used to replace one text string with another using a generic syntax like this:
=SUBSTITUTE(text,old_text,new_text,[instance_num])Where text is the value to process (typically a cell reference), old_text is the text to find, new_text is the text to replace with, and instance_num
=SUBSTITUTE(B5,"t","b") // replace all t's with b's
=SUBSTITUTE(B6,"t","b",1) // replace first t with b
=SUBSTITUTE(B7,"t","b") // replace all t's with b's
=SUBSTITUTE(B8,"cat","dog") // replace cat with dog
=SUBSTITUTE(B9,"#","") // replace # with nothing
=SUBSTITUTE(B10,"-",", ") // replace hyphens with commasBy default, the SUBSTITUTE function will replace all instances of one text string with another. You can see this behavior in the worksheet below, wher
=SUBSTITUTE(B5,".","-")By default, SUBSTITUTE will replace all instances of one text string with another. The optional fourth argument, called instance_num, can be used to r
=SUBSTITUTE(B5," ","-",2)SUBSTITUTE can be combined with other functions to solve more difficult problems. The example below uses the SUBSTITUTE function to replace line break
=SUBSTITUTE(B5,CHAR(10),", ")You can use the SUBSTITUTE function to completely remove unwanted text by providing an empty string for the new_text argument. You can see this approa
=SUBSTITUTE(B5:B16,"*","")+0