The Excel SUBSTITUTE Function

Excel Substitute vs. Excel Replace

The Excel Substitute function is similar to the Excel Replace Function. The difference between the two functions is:

The Substitute function replaces one or more instances of a given text string;
The Replace function replaces text in a specified position of a supplied string.

Related Function:

REPLACE

Function Description

The Excel Substitute function replaces one or more instances of a given text string, within an original text string.

The syntax of the function is:

SUBSTITUTE( text, old_text, new_text, [instance_num] )

Where the function arguments are:

text	-	The original text string containing the text to be replaced.
old_text	-	The sub-string that is to be found and replaced by new_text.
new_text	-	The new text string that is to be 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 a value of [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 case sensitive. Therefore, if the old_text argument is the text string "A", this will NOT replace instances of the lower case text string "a".

Substitute Function Examples

Column B of the spreadsheet below shows four examples of the Excel Substitute Function.

Formulas:

	A	B
1	abab	=SUBSTITUTE( A1, "a", "X" )
2	abab	=SUBSTITUTE( A2, "a", "X", 2 )
3	John is 5 years old	=SUBSTITUTE( A3, "John", "Jack" )
4	John is 5 years old	=SUBSTITUTE( A4, "5", "6" )

Results:

	A	B
1	abab	XbXb
2	abab	abXb
3	John is 5 years old	Jack is 5 years old
4	John is 5 years old	John is 6 years old

For further details and examples of the Excel Substitute function, see the Microsoft Office website.

Substitute Function Common Problem

Use of the Excel Substitute Function with Numbers, Dates and Times

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.

If you are not planning to use the date, time or number in further calculations, you could solve this problem by converting these values into text, using the Excel Text To Columns tool. To do this:

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 of the Excel ribbon, select the Text to Columns ... option (see rightabove);
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.

The Substitute function should now work as expected on the values that have been converted to text.

Return to the Excel Text Functions Page
Return to the List of All Built-In Excel Functions