What it Does: The SUBSTITUTE() function in Excel substitutes one or more instances of a string (or number) for something else.

Arguments: SUBSTITUTE(CELL, old text, new text, [instance])

• CELL – The cell you’d like to replace something in
• OLD TEXT – The text you want to replace. This is key. It can be all or part of the text string currently within the CELL.
• NEW TEXT – What do you want to replace the OLD TEXT with?
• [Instance] – Optional – Which occurrence of the old text do you want to replace? If there are 4 dashes in your string and you want to change the first dash to a colon, you’d enter “1” for this field. Examples below.

Example 1: 

Replace “.net” with “.com”

In this example we’ve replaced “.net” with “.com” to correct the address of the website.

Note: If you’ve used FIND and SEARCH a lot you may be confused at first because the SUBSTITUTE function first takes the CELL you want to target first, THEN the text you’re substituting. FIND() and SEARCH() both take the text you’re searching for as their first argument

Other Blog Posts using Substitute:

• Using Substitute and CHAR() to clean a block of text
• How to Clean Up a Block of Text in Excel