SUBSTITUTE function
Summary
Substitutes new text for old text in a text string
Syntax
SUBSTITUTE(text, old_text, new_text, [instance_num])
The SUBSTITUTE function syntax has the following arguments:
• Text Required. The text or the reference to a cell containing text for which you want to substitute characters.
• Old_text Required. The text you want to replace.
• New_text Required. The text you want to replace old_text with.
• Instance_num Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
• Text Required. The text or the reference to a cell containing text for which you want to substitute characters.
• Old_text Required. The text you want to replace.
• New_text Required. The text you want to replace old_text with.
• Instance_num Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.
Example
=SUBSTITUTE(A2, "Sales", "Cost")
=SUBSTITUTE(A3, "1", "2", 1)
=SUBSTITUTE(A4, "1", "2", 3)
=SUBSTITUTE(A3, "1", "2", 1)
=SUBSTITUTE(A4, "1", "2", 3)