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.