Excel REPLACE vs SUBSTITUTE function

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

Both functions work on TEXT editing. We replace or substitute the text using these functions.

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 occurrences)

<div="bbbb">=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.

Related Articles:

Remove leading and trailing spaces from text in Excel
How to use RIGHT function in Excel
Remove unwanted characters in Excel
Extract Text From A String In Excel Using Excel’s LEFT And RIGHT Function

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

 

Leave a Reply

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

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.