On UrbanPro

View My Profile       Proud to be a Teacher       Write a Review  

Monday, April 18, 2016

Substitute Function in Excel

Use the SUBSTITUTE function when you want to replace text based on its content, not position and it is  also use to Replace All or Part of a Text String With Another Text String - Function Description and Examples are mentioned below:

Syntex: =SUBSTITUTE (text, old_text, new_text, [instance])

SUBSTITUTE function finds and replaces old_text with new_text in a text string. Instance limits SUBSTITUTE replacement to one particular instance of old_text.

SUBSTITUTE function is case-sensitive and does not support wildcards.


    text     - The text to change.
    old_text     - The text to replace.
    new_text     - The text to replace with.
    instance     - [optional] The instance of old_text to replace with new_text. Optional; if not supplied, all instances of old_text are replaced with new_text.

Examples are as follows:

1. Replacing "2010" to "2013" in below example:

2. Below example is self defined and has its all aspects i.e. indicate which occurrence you want to substitute, Case sensitive, what happens, if instance is not defined.

Use SUBSTITUTE to replace text based on content. Use REPLACE when to replace text based on its location.