Substitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.
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: Let’s look at some Excel SUBSTITUTE function examples and explore how to use the SUBSTITUTE function as a worksheet function in Microsoft Excel:
Syntax: =SUBSTITUTE(A2,B2,C2,D2)
Result:
Based on the Excel spreadsheet above, the following SUBSTITUTE examples would return:
Syntax: =SUBSTITUTE(A3,B3,C3,D3)
Result: Sophuc
Syntax: =SUBSTITUTE(A4,B4,C4,D4)
Result: Excel SUBSTITUTE Function
Syntax: =SUBSTITUTE(A5,B5,C5,D5)
Result: Excel SUBSTITUTE
Syntax: =SUBSTITUTE(A6,B6,C6,D6)
Result: SUBSTITUTE Function
Syntax: =SUBSTITUTE(A7,B7,C7,D7)
Result: SUBSTITUTE function in excel
Note:
- SUBSTITUTE finds and replaces old_text with new_text in a text string.
- Instance limits SUBSTITUTE replacement to one particular instance of old_text. if not supplied, all instances of old_text are replaced with new_text.
- Use SUBSTITUTE to replace text based on content. Use the REPLACE function to replace text based on its location.
- SUBSTITUTE is case-sensitive and does not support wildcards.