Excel Function: SUBSTITUTE

Purpose

The SUBSTITUTE function swaps a sequence of characters in a text value for another text value. Its output is always a value of type text. A sequence of characters to be swapped is defined with the old_text argument and the text value it is to be replaced with is defined with the new_text argument. By default, every instance of old_text will be swapped for new_text, however this behaviour can be overridden by leveraging the instance_num argument which is used to specify which instance of a sequence of characters that appears many times in a text value will be swapped.

Invocations of the SUBSTITUTE function take the form:

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

Limitations

Wildcard characters are not supported.

Example

Consider a cell containing the formula:

=SUBSTITUTE("Give every man thy ear but few thy voice.","thy","thine",2)

The formula instructs the SUBSTITUTE function to swap the second instance of “thy” in “Give every man thy ear but few thy voice.“¹ for “thine“. Numbers are assigned to instances of old_text by reading the value passed to the text argument from left to right, then assigning 1 to the first instance, 2 to the second instance and so on. The process is illustrated below:

Step 1: Identify every instance of “thy” in “Give every man thy ear but few thy voice.”. Step 2: assign a number to each instance of “thy” identified in step 1. The instance of “thy” preceding “ear” is assigned an instance number of 1, and the instance of “thy” preceding “voice” is assigned an instance number of 2. Step 3, swap the instance assigned a number of 2 for “thine”. The text value “Give every man thy ear but few thine voice.” is generated. Step 4, return “Give every man thy ear but few thine voice.”.

Arguments

Argument	Argument type	Description
text	Mandatory	A text value containing a section that will be swapped for the new_text argument.
old_text	Mandatory	A sequence of characters in the text argument to be swapped for new_text.
new_text	Mandatory	A text value used to replace one or all instances of old_text in the text argument.
instance_num	Optional	Used to specify which one instance of old_text is to be replaced in the text argument.

Related Functions

Function	Example	Purpose
REPLACE	`REPLACE(old_text,start_num,num_chars,new_text)`	Its purpose is identical to that of the SUBSTITUTE function: the REPLACE function swaps a section of a text value with another text value. REPLACE differs from SUBSTITUTE in that: Only the location of the sequence of characters to be swapped needs to be passed to it. It can swap only 1 instance of a sequence of characters for another value.

Error Conditions

Error value	Condition type	Condition
#VALUE!	Bad argument	The value passed to the instance_num argument was ≤ 0.
#VALUE!	Bad argument	A value of type currency, geography or stock was supplied to the text argument.
#VALUE!	Bad argument	A value of type currency, geography or stock was supplied to the old_text argument.
#VALUE!	Bad argument	A value of type currency, geography or stock was supplied to the new_text argument.

Examples

Example	Formula	Result	Description
1	`=SUBSTITUTE("ABabAB","AB","CD")`	`CDabCD`	Swap every instance of “AB” for “CD” in “ABabAB“. The SUBSTITUTE function: Identifies 2 instances of “AB” in “ABabAB“. “ab” is not an instance of “AB” because their case differs. Swaps both instances for “CD“. Returns the text value “CDabCD“.
2	`=SUBSTITUTE("ABabAB","AB","CD",2)`	`ABabCD`	Swap the second instance of “AB” for “CD” in “ABabAB“. The SUBSTITUTE function: Identifies 2 instances of “AB” in “ABabAB“. “ab” is not an instance of “AB” because their case differs. Swaps the second instance of “AB” for “CD“. Returns the text value “ABabCD“.
3	`=SUBSTITUTE("ABabAB","AB","CD",3)`	`ABabAB`	Swap the third instance of “AB” for “CD” in “ABabAB“. The SUBSTITUTE function: Identifies 2 instances of “AB” in “ABabAB“. “ab” is not an instance of “AB” because their case differs. Does not change “ABabAB” because no third instance of “AB” exists in it. Returns the text value “ABabAB“.
4	`=SUBSTITUTE("ABabAB","EF","CD",3)`	`ABabAB`	Substitute a sequence of characters which does not exist (“EF“) in “ABabAB” for “CD“. The SUBSTITUTE function: Identifies 0 instances of “EF” in “ABabAB“. Does not change “ABabAB” because no instances of “EF” exist in it. Returns the text value “ABabAB“.
5	`=SUBSTITUTE("abcd","","e")`	`abcd`	Swap the empty text value (“”) for “e” in “abcd“. The SUBSTITUTE function: Identifies 0 instances of the empty text value (““) in “abcd“. Does not change “abcd“. Returns the text value “abcd“.
6	`=SUBSTITUTE("abcd","c","")`	`abd`	Swap “c” for the empty text value (“”) in “abcd“. The SUBSTITUTE function: Identifies 1 instance of “c” in “abcd“. Swaps c” in “abcd” for the empty text value (“”). Returns the text value “abd“.

Examples: Errors

Example	Formula	Result	Description
1	`=SUBSTITUTE("ABabAB","AB","CD",0)`	`#VALUE!`	Swap the zeroth instance of “AB” for “CD” in “ABabAB“. No zeroth instance of any sequence of characters can exist in a text value Because of this, #VALUE! is returned.

Footnotes

¹From Hamlet. Listen to what others have to say, but permit only a few people to speak on your behalf.