October 5, 2010
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
gives us "brawn fax"
If we specify that we only want to replace the 2nd "o"
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
NOTE : You could also use FIND which is case sensitive. SEARCH is case insensitive.
So to REPLACE "fox" with "dog"
REPLACE allows you to replace a substring with a another of different length too. To change "brown fox" to "brown bear"
Please see attached workbook for these formulae