SUBSTITUTE function: Description, Usage, Syntax, Examples and Explanation
What is SUBSTITUTE function in Excel?
SUBSTITUTE function is one of TEXT functions in Microsoft Excel that 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 of SUBSTITUTE function
SUBSTITUTE(text, old_text, new_text, [instance_num])
The SUBSTITUTE function syntax has the following arguments:
- Text: The text or the reference to a cell containing text for which you want to substitute characters.
- Old_text: The text you want to replace.
- New_text: 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 of SUBSTITUTE function
Steps to follow:
1. Open a new Excel worksheet.
2. Copy data in the following table below and paste it in cell A1
Note: For formulas to show results, select them, press F2 key on your keyboard and then press Enter.
You can adjust the column widths to see all the data, if need be.
| Data | ||
| Sales Data | < | |
| Quarter 1, 2008 | < | |
| Quarter 1, 2011 | < | |
| Formula | Description (Result) | Result |
| =SUBSTITUTE(A2, “Sales”, “Cost”) | Substitutes Cost for Sales (Cost Data) | Cost Data |
| =SUBSTITUTE(A3, “1”, “2”, 1) | Substitutes first instance of “1” with “2” (Quarter 2, 2008) | Quarter 2, 2008 |
| =SUBSTITUTE(A4, “1”, “2”, 3) | Substitutes third instance of “1” with “2” (Quarter 1, 2012) | Quarter 1, 2012 |