Excel REPLACE vs SUBSTITUTE function

In this article, we will learn the difference between SUBSTITUTE vs REPLACE function.

Both functions work on TEXT editing. Replace or substitute the text using these function.

SUBSTITUTE function finds the particular text and replaces it whereas REPLACE function replaces the text defined by its position.

Syntax of SUBSTITUTE

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

Syntax of REPLACE

=REPLACE (old_text, start_num, num_chars, new_text)

We will perform the same task via both function to understand more.
Here we have the same text. We will perform multiple tasks on it.
75
Use Substitute function to replace 2013 to 2016 (First occurrence)

=SUBSTITUTE(A2,2013,B2,1)

Use Replace function to replace 2013 to 2016 (First occurrence)

=REPLACE(A3,17,4,B3)

76
Now we need to replace both 2013 text
Use Substitute function to replace 2013 to 2016 (All occurrence)

=SUBSTITUTE(A2,2013,B2)

Use Replace function to replace 2013 to 2016 (All occurrence)

=REPLACE(REPLACE(A5,17,4,B5),31,4,B5)

Nested REPLACE function replaces both texts
77
The results are in front of you. As we can see text been changed.
Hope you understood the difference between REPLACE and SUBSTITUTE function. Explore more Excel text functions here. Please state your queries here in the comment box below.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube