Another pair of functions that has very similar meaning literally.   Although I am not able to tell you the differences between “replace” and “substitute” in English, I can show you the differences of the REPLACE and SUBSTITUTE in Excel.  Are you ready?

Let’s start with the function.  Both functions, as you may guess, look into a text string, then based on your instruction, replace part(s) of a string with any text(s)/character(s) specified.

The key difference between REPLACE and SUBSTITUTE

REPLACE requires you to specify a staring position and length of character you want to replace with something

SUBSTITUTE looks for specific texts/characters and then substitute it with something. Sound like FIND?  If you don’t know FIND, take a look at FIND vs. SEARCH. 

REPLACE

The syntax

REPLACE(old_text, start_num, num_chars, new_text)

All arguments are required.

• old_text.  The argument name is a bit confusing here.  It means the text string you want to look into.
• start_num.  The starting position in old_text where you want the replacement takes place
• num_chars.  The number of characters, or the length of the text string from the starting position, you want the replacement takes place.
• new_text.  Obviously, the replacement text sting.  Remember to enclose your text with double quotation “”

Picture time:

In the first example, it tells Excel to look into A1, i.e. “ABCDEabcdeABCDE”, starts with the 5th character for a length of 1, i.e. “E”,  then replace that portion with “_”.

In the second example, the only difference is the third argument num_chars, which is 3.  That means 3 characters starting from the 5th position, i.e. “Eab” in “ABCDEabcdeABCDE”; then replace it with “_”.

Make sense?

Tips:

1. When the num_chars is larger than the number of characters remained in the text string  from start_num, it replaces all characters from the starting point with the text you specified.
2. You may input 0 as num_chars.  I think this actually makes the function special.  Try and see what will happen.

SUBSTITUTE

The syntax

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

Comparing to REPLACE, SUBSTITUTE is more simple and intuitive, if you have a specific text in mind… 🙂

• text.  The text you want to look into
• old_text.  The text you want to FIND and to be substituted
• new_text.  The text you want to replace old_text with
• [instance_num] optional.  It instructs which instance to be replace, when multiple matches found.  When omitted, it replaces all instances found

Picture time, again:

In the first example, it looks into the text is A1, look for all “E” (as the last argument is omitted) and then replace with “_”.

Note: SUBSTITUTE is case-sensitive.  If you need to perform case-insensitive SUBSTITUTE, you need to do it together with either LOWER or UPPER…… But this is not a perfect solution.

Do you have a suggestion? Try to figure it out and post it in comment.  In case no answer is posted in comment, I will tell next week. 🙂

By the way, is “replace with” the same as “substitute to”??