Substitute Function

User Rating:  / 0

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

The syntax of the function is :

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

Where the function arguments are:

Where the function arguments are:

	A	B	C
1	text	-	The original text string containing the text to be replaced.
2	old_text	-	The text to be found and replaced by new_text.
3	new_text	-	The new text that is used to replace the old_text.
4	[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.

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.

Note also, 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

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

Formulas:   A B 1  =SUBSTITUTE("abcd abcd","a","b")  2  =SUBSTITUTE("abcd abcd","a","b",2)  3  Today is Saturday   =SUBSTITUTE(A3,"Saturday","Sunday")  4  John is 5 years old   =SUBSTITUTE(A4,"John","Jack")

Results:   A B 1  bbcd bbcd  2  abcd bbcd  3  Today is Saturday   Today is Sunday  4  John is 5 years old   Jack is 5 years old

Further information and examples of the Excel Substitute function can be found on the Microsoft Office website.

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 will give you unexpected results.

One solution to this problem is to convert the date, time or number into text, using 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