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:
Based on the Excel spreadsheet above, the following SUBSTITUTE examples would return:
- 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.