Using REPLACE and SUBSTITUTE functions in Excel – formula examples

Using Excel REPLACE function with numeric values

The REPLACE function in Excel is designed to work with text strings. Of course, you can use it to replace numeric characters that are part of a text string, for example:

=REPLACE(A2, 7, 4, "2016")

Notice that we enclose “2016” in double quotes as you usually do with text values.

In a similar manner, you can replace one or more digits within a number. For example:

=REPLACE(A4, 4, 4,"6")

And again, you have to enclose the replacement value in double quotes (“6”).

Using Excel REPLACE function with dates

As you have just seen, the REPLACE function works fine with numbers, except that it returns a text string 🙂 Remembering that in the internal Excel system, dates are stored as numbers, you may try to use some Replace formulas on dates. Results would be quite embarrassing.

For instance, you have a date in A2, say 1-Oct-14, and you want to change “Oct” to “Nov“. So, you write the formula REPLACE(A2, 4, 3, “Nov”) that tells Excel to replace 3 chars in cells A2 beginning with the 4^th char… and got the following result:

Why’s that? Because “01-Oct-14” is only a visual representation of the underlying serial number (41913) that represents the date. So, our Replace formula changes the last 3 digits in the above serial number to “Nov” and returns the text string “419Nov”.

To get the Excel REPLACE function to correctly work with dates, you can convert dates to text strings first by using the TEXT function or any other technique demonstrated in How to convert date to text in Excel. Alternatively, you can embed the TEXT function directly in the old_text argument of the REPLACE function:

=REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov")

Please remember that the result of the above formula is a text string, and therefore this solution works only if you are not planning to use the modified dates in further calculations. If you do need dates rather than text strings, use the DATEVALUE function to turn the values returned by the Excel REPLACE function back to dates:

=DATEVALUE(REPLACE(TEXT(A2, "dd-mmm-yy"), 4, 3, "Nov"))

Nested REPLACE functions to do multiple replacements in a cell

Quite often, you may need to do more than one replacement in the same cell. Of course, you could do one replacement, output an intermediate result into an additional column, and then use the REPLACE function again. However, a better and more professional way is to use nested REPLACE functions that let you perform several replacements with a single formula. In this context, “nesting” means placing one function within another.

Consider the following example. Supposing you have a list of telephone numbers in column A formatted as “123456789” and you want to make them look more like phone numbers by adding hyphens. In other words, your goal is to turn “123456789” into “123-456-789”.

Inserting the first hyphen is easy. You write a usual Excel Replace formula that replaces zero characters with a hyphen, i.e. adds a hyphen in the 4^th position in a cell:

=REPLACE(A2,4,0,"-")

The result of the above Replace formula is as follows:

Okay, and now we need to insert one more hyphen in the 8^th position. To do this, you place the above formula within another Excel REPLACE function. More precisely, you embed it in the old_text argument of the other function, so that the second REPLACE function will handle the value returned by the first REPLACE, and not the value in cell A2:

=REPLACE(REPLACE(A2,4,0,"-"),8,0,"-")

As the result, you get the phone numbers in the desired formatting:

In a similar manner, you can use nested REPLACE functions to make text strings look like dates by adding a forward slash (/) where appropriate:

=(REPLACE(REPLACE(A2,3,0,"/"),6,0,"/"))

Moreover, you can convert text strings into real dates by wrapping the above REPLACE formula with the DATEVALUE function:

=DATEVALUE(REPLACE(REPLACE(A2,3,0,"/"),6,0,"/"))

And naturally, you are not limited in the number of functions you can nest within one formula (the modern versions of Excel 2010, 2013 and 2016 allow up to 8192 characters and up to 64 nested functions in a formula).

For example, you can use 3 nested REPLACE functions to have a number in A2 appear like date and time:

=REPLACE(REPLACE(REPLACE(REPLACE(A2,3,0,"/") ,6,0,"/"), 9,0, " "), 12,0, ":")

Replacing a string that appears in a different position in each cell

So far, in all the examples we have been dealing with values of a similar nature and have made replacements in the same position in each cell. But real-life tasks are often more complicated than that. In your worksheets, the characters to be replaced may not necessarily appear in the same place in each cell, and therefore you will have to find the position of the first character that should be replaced. The following example will demonstrate what I’m talking about.

Supposing you have a list of email addressing in column A. And the name of one company has changed from “ABC” to, say, “BCA”. So, you have to update all of the clients’ email addressing accordingly.

But the problem is that the client names are of different length, and that is why you cannot specify exactly where the company name begins. In other words, you do not know what value to supply in the start_num argument of the Excel REPLACE function. To find it out, use the Excel FIND function to determine the position of the first char in the string “@abc”:

=FIND("@abc",A2)

And then, supply the above FIND function in the start_num argument of your REPLACE formula:

=REPLACE(A2, FIND("@abc",A2), 4, "@bca")

As you see in the following screenshot, the formula has no problem with finding and replacing the old text with the new one. However, if the text string to be replaced is not found, the formula returns the #VALUE! error:

And we want the formula to return the original email address instead of the error. So, let’s enclose our FIND & REPLACE formula in the IFERROR function:

=IFERROR(REPLACE(A2, FIND("@abc",A2), 4, "@bca"),A2)

And this improved formula works perfectly, doesn’t it?

Another practical application of the REPLACE function is to capitalize the first letter in a cell. Whenever you deal with a list of names, products, and the like, you can use the above-linked formula to change the first letter to UPPERCASE.

Substitute multiple values with a single formula (nested SUBSTITUTE)

As is the case with the Excel REPLACE function, you can nest several SUBSTITUTE functions within a single formula to do several substitutions at a time, i.e. substitute several characters or substrings with a single formula.

Supposing you have a text string like “PR1, ML1, T1” in cell A2, where “PR” stands for “Project, “ML” stands for “Milestone” and “T” means “Task”. What you want is to replace the three codes with full names. To achieve this, you can write 3 different SUBSTITUTE formulas:

=SUBSTITUTE(A2,"PR", "Project ")

=SUBSTITUTE(A2, "ML", "Milestone ")

=SUBSTITUTE(A2, "T", "Task ")

And then nest them into each other:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"PR","Project "),"ML","Milestone "),"T","Task ")

Notice that we’ve added a space at the end of each new_text argument for better readability.

To learn other ways to replace multiple values at a time, please see How to do mass find and replace in Excel.