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(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 “”
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 “_”.
- 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.
- You may input 0 as num_chars. I think this actually makes the function special. Try and see what will happen.
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
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”??