Difference between Substitute and Replace Function|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi John,

There are subtle differences and in some cases you can get the same result using both SUBSTITUTE and REPLACE.

SUBSTITUTE allows you to replace 1 or all occurrences of a string in another string.  If we have the string "brown fox" in A1 then

=SUBSTITUTE(A1,"o","a")

gives us "brawn fax"

If we specify that we only want to replace the 2nd "o" 

=SUBSTITUTE(A1,"o","a",2)

we get "brown fax"

With REPLACE we are replacing a specific part of the string with another and we need to specify from where in the string to start looking for the occurrence of the string we want to replace.

REPLACE requires us to know the position of the substring and its length, SUBSTITUTE is more flexible.

With our string "brown fox" if we want to replace "fox" with "dog" we need to know where in the string "fox" occurs

=SEARCH("fox",A1)

NOTE : You could also use FIND which is case sensitive.  SEARCH is case insensitive.

So to REPLACE "fox" with "dog"

=REPLACE(A5,SEARCH("fox",A5),3,"dog")

REPLACE allows you to replace a substring with a another of different length too.  To change "brown fox" to "brown bear"

=REPLACE(A5,SEARCH("fox",A5),3,"bear")

Please see attached workbook for these formulae

Regards

Phil