Excel REPLACE vs Substitute Function

A fundamental skill for data cleaning in Excel is text manipulation. Excel offers both REPLACE and SUBSTITUTE functions for modifying text in a cell. While both functions seem similar, they have distinct uses. How do you know which one to choose? If you are unsure about what function to use, you are in the right place. This article will clarify any uncertainties you may have.

The Replace Function

The REPLACE function allows you to swap a specific number of characters in a text string with another character or a set of characters from a defined position within the text string.

The Syntax:

REPLACE(old_text, start_num, num_chars, new_text)

The Excel REPLACE function has 4 arguments:

Old_text – the original text (or a reference to a cell with the original text) in which you want to replace some characters.
Start_num – the position of the first character within old_text that you want to replace.
Num_chars – the number of characters you want to replace.
New_text – the replacement text.

For example, to change the word “big” to “bug“, you can use like so:

=REPLACE(“big”, 2, 1, “u”)

The REPLACE function can also work with numbers, however, note that the results will be converted to text/string data type. For example,

REPLACE and Dates

Working with the REPLACE function and dates in Excel can be a little tricky. However, I will work you through the tips that can make it easier. In the example below, we would like to replace the month Dec with Oct.

The right way to use the REPLACE function when working with dates is to use the TEXT function nested in the replace function. You need to embed the old_text argument with the Text function specifying the date format and other arguments will work the same. Remember that the result text will be a string and so aligned to the left. In case you need the results for further calculations, you will need to use the DATEVALUE function to convert to date. Here is the right way to do it.

Multiple REPLACE(s) in a cell?

Supposing you need to replace two items in a cell, you can use the REPLACE function in a column and then use the function again to take care of the second replacement in another column. While this method works, a more efficient and professional way to handle multiple replacements is to utilize nested REPLACE functions.

For Example, you have a column with phone numbers looking like 123456789 and you would prefer it to look like 123-456-789 which clearly looks more like a phone number. You can approach the problem like so:

Step 1: First REPLACE function

=REPLACE(A1, 4, 0, “-”)

We used the zero (0) here to indicate that we are replacing zero/no characters with a hyphen at the fourth position.

Step 2: the nested replace function

=REPLACE(REPLACE(A3,4,0,”-“),8,0,”-“)

Remember we already added a hyphen so we need to start from the 8 characters.

The SUBSTITUTE function

The SUBSTITUTE function replaces occurrences of a specified character(s)/text string with another character(s)/text string in each text.

The syntax:

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

The first three arguments are required and the last one is optional.

Text – the original text in which you want to substitute characters. It can be supplied as a text string (in double quotes), or a cell reference.
Old_text – the character(s) you want to replace.
New_text – the new character(s) to replace old_text with.
Instance_num – the occurrence of old_text you want to replace. If omitted, every occurrence of the old text will be changed to the new text

For example, all of the formulas listed below will substitute “pie” with “cake” in cell A2, but return different results depending on which number you supply in the last argument:

=SUBSTITUTE(A2, “pie”, “cake”) – Substitutes all occurrences of “pie” with cake”.

=SUBSTITUTE(A2, “pie”, “cake”, 1) – Substitutes the first occurrence of “pie” with “cake”.

Note that the SUBSTITUTE function is case sensitive so “pie” is not the same as “Pie”

NESTED SUBSTITUTE functions?

Just like we saw with the replace function, we can also nest the substitute function if we have more than one item in a cell we want to substitute.

Assume that in cell B2 you have a text string that reads ML Engineer, BI Analyst where ML should be Substituted for “Machine Learning” and BI for “Business Intelligence”.

=SUBSTITUTE(B2, “ML”, “Machine Learning”)

=SUBSTITUTE(B2, “BI”, “Business Intelligence”)

So we nest the formulas into one and it will look like this:

=SUBSTITUTE(SUBSTITUTE(B2, “ML”, “Machine Learning”), “BI”, “Business Intelligence”)

Closing

In summary, the REPLACE function is more about replacing a specific portion of text based on position and length while the SUBSTITUTE function is more focused on replacing specific substrings throughout the text one or more time(s). Simply put, If you know the text to be replaced, use the SUBSTITUTE function in Excel. If you know the position of the text to be replaced, use the REPLACE function.

I hope you learnt something new. Do well to try out the functions and let me know your use case in the comment session. See you in our next blog!