SUBSTITUTE and REPLACE function.

Syntax and its argument is from EXCEL help(F1) :

First, observe the both syntax thoroughly.

SUBSTITUTE function Syntax:

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

The SUBSTITUTE function syntax has the following arguments:

• Text    Required. The text or the reference to a cell containing text for which you want to substitute characters.
• Old_text    Required. The text you want to replace.
• New_text    Required. The text you want to replace old_text with.
• Instance_num    Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

 REPLACE function syntax:

=REPLACE(old_text, start_num, num_chars, new_text)

The REPLACE function syntax has the following arguments: 

• Old_text    (Required). Text in which you want to replace some characters.
• Start_num   (Required). The position of the character in old_text that you want to replace with new_text.
• Num_chars   (Required). The number of characters in old_text that you want REPLACE to replace with new_text.
• New_text    (Required). The text that will replace characters in old_text.

If you notice , SUBSTITUTE  function is for when you know the character(s) you want to replace, but not position . And REPLACE function is for when you know the position of text but not actual text.  

Examples of SUBSTITUTE function:

=SUBSTITUTE("1000 quantities of television delivered.",1000,1002)
Result: 1002 quantities of television delivered.

=SUBSTITUTE("1000 quantities of television delivered.",0,2,1)
 Result: 1200 quantities of television delivered.

=SUBSTITUTE("1000 quantities of television delivered.",0,2,2)
 Result: 1020 quantities of television delivered.


=SUBSTITUTE("1000 quantities of television delivered.",0,2,3)
 Result: 1002 quantities of television delivered.

=SUBSTITUTE("1000 quantities of television delivered.","delivered","NOT delivered")
 Result: 1000 quantities of television NOT delivered.

=SUBSTITUTE("1000 quantities of television delivered."," ","")
 Result: 1000quantitiesoftelevisiondelivered.

Example of REPLACE function:

=REPLACE("students_marks_above_80percent",16,7,">")
Result: students_marks > 80percent