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.
Syntax
SUBSTITUTE(text,old_text,new_text,instance_num)
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.
Example
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.
|
|
| A |
| Data |
| Sales Data |
| Quarter 1, 2008 |
| Quarter 1, 2011 |
| Formula |
Description (Result) |
| =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) |
|
