The Excel SUBSTITUTE Function

Home » Excel-Built-In-Functions » Excel-Substitute-Function

Search this site:

Custom Search

Related Function :

Replace Function

Basic Description

The Excel SUBSTITUTE function replaces occurrences of a search text string, within an original text string, with the supplied replacement text

The format of the function is :

SUBSTITUTE( Text, Old_text, New_text, [Instance_Num] )

Where the arguments are as follows :

Text	-	The original text string containing the text to be replaced
Old_text	-	The text to be found and replaced by New_text
New_text	-	The new text that is used to replace the Old_text
[Instance_Num]	-	An optional argument which specifies which occurrence of Old_text should be replaced by the New_text. If Instance_Num is specified, just that instance of the Old_text is replaced; Otherwise, all instances of Old_text are replaced with the New_text

Note that the Excel Substitute function is similar to the Excel Replace Function, but the Substitute function replaces one or more instances of a given text string, while the Replace function replaces text in a specified position of a supplied string.

Example

The spreadsheets on the right show examples of use of the Excel Substitute Function. The spreadsheet at the top shows the format of the functions and the spreadsheet below shows the results.

Formulas
Examples of use of the Excel Substitute Function

Results

Common Problem:

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 or a number, it will give you unexpected results.

If your initial value is in a cell and you want to convert it to text, you can use the Excel Text To Columns tool:

Use the mouse to select the cell(s) you want to convert to text (this must not span more than one column)
From the Data tab at the top of your Excel workbook, select the Text to Columns ... option
Make sure the Delimited option is selected and click next
Make sure all the delimiter options are unselected and then click next again
You should now be offered a selection of Column Data Formats. Select Text and click the Finish button