SUBSTITUTE Function
Written by kazamraza
no comments
Classified in : Excel Formulas, Text Functions
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])
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.
Example
| ◢ | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Data | Formula | Result | Description | |
| 2 | Sales Data | =SUBSTITUTE(A2, "Sales", "Cost") | Cost Data | Substitutes Cost for Sales (Cost Data) | |
| 3 | |||||
| 4 | ="Kazam"&CHAR(9)&"Raza" | =SUBSTITUTE("Kazam"&CHAR(9)&"Raza",CHAR(9)," ") | Kazam Raza | replace Tab character with a space |