The Excel Substitute function replaces one or more instances of a given text string, within an original text string.
Syntax
The syntax for the SUBSTITUTE function in Microsoft Excel is:
=SUBSTITUTE( text, old_text, new_text, [Instance] )
Parameters or Arguments
text
The original string to use to perform the substitution.
old_text
The existing characters to replace.
new_text
The new characters to replace old_text with.
Instance
Optional. It is the nth appearance of old_text that you wish to replace. If this parameter is omitted, then every occurrence of old_text will be replaced with new_text.
Substitute Function Examples
Column B of the spreadsheet below shows four examples of the Excel Substitute Function.
Formulas:
Results:
Note:
- Use the Substitute function when you want to replace text based on its content, not position. Use the REPLACE function to replace text based on its location.
- Substitute finds and replaces old_text with new_text in a text string.
- instance is optional. It limits Substitute replacement to one particular instance of old_text. if not supplied, all instances of old_text are replaced with new_text.
- Substitute is case-sensitive and does not support wildcards.
- The Excel Substitute function is designed for use with text strings and returns a text string. Therefore, if you attempt to use the substitute function with a date, time or a number, it may return unexpected results.