This article describes the formula syntax and usage of the SUBSTITUTE function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.
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.
SUBSTITUTE(text, old_text, new_text, [instance_num])
The SUBSTITUTE function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
- 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.
The workbook below shows examples of this function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.
To work more in-depth with the example data in Excel, download the embedded workbook to your computer, and then open it in Excel.