Difference between substitute and Replace function in excel|General Excel Questions & Answers|Excel Forum|My Online Training Hub

Hi Mehdi,

SUBSTITUTE allows you to:

1.Replace all occurrences of a substring in a string 

2. Replace a particular instance of a substring within a string

Syntax : =REPLACE(text , old_text, new_text, [instance])

Example 1

MyString = "axbxcx"

=SUBSTITUTE(MyString,"x","z")

will give the result azbzcz because all occurrences of x were replaced with z

Example 2

MyString = "axbxcxdx"

=SUBSTITUTE(MyString,"x","z",3)

will give the result axbxcz because only the 3rd occurrence of x was replaced with z

SUBSTITUTE does not require you to know where in the string your substring occurs.

REPLACE allows you to change part of a string to another string.

Syntax : =REPLACE(old_text ,start_num, num_chars, new_text)

Example 1

MyString = "axbxcx"

=REPLACE(MyString, 2, 1, "z")

gives the result azbxcx

Example 2

MyString = "axbxcx"

=REPLACE(MyString, 3, 3, "p")

gives the result axpx

Example 3

MyString = "axbxcx"

=REPLACE(MyString, 3, 3, "vvv")

gives the result axvvvx

REPLACE requires you to know where your substring is in the string.  You may need to use string functions like FIND to help you out here 

Example 4

MyString = "axbxcx"

=REPLACE(MyString, FIND("xbx",MyString), 1, "OOO")

gives the result aOOObxcx

REPLACE allows you to insert more or less characters than you remove from a string.