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


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:

Excel Tips - Replace vs. Substitute 2.png

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?


  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.


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:

Excel Tips - Replace vs. Substitute 3.png

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”??




This entry was posted in Formula and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s