Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.
Text is the text or the reference to a cell containing text for which you want to substitute characters.
Old_text is the text you want to replace.
New_text is the text you want to replace old_text with.
Instance_num 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.
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
- Create a blank workbook or worksheet.
- Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
- Press CTRL+C.
- In the worksheet, select cell A1, and press CTRL+V.
- To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
|Quarter 1, 2008
|Quarter 1, 2011
|=SUBSTITUTE(A2, "Sales", "Cost")
||Substitutes Cost for Sales (Cost Data)
|=SUBSTITUTE(A3, "1", "2", 1)
||Substitutes first instance of "1" with "2" (Quarter 2, 2008)
|=SUBSTITUTE(A4, "1", "2", 3)
||Substitutes third instance of "1" with "2" (Quarter 1, 2012)