How To Use The REPLACE Function In Excel: Replace Text Easily

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

If you need to make edits to a large body of text, Excel’s functions can make the process much easier. Both the REPLACE and SUBSTITUTE functions can help—but they both do it differently.

Let’s take a look at how the two functions work, how they differ, and how you might put them to use in a real spreadsheet.

*This tutorial is for Excel 2019 for Windows. Got a different version? No problem, you can still follow the exact same steps.

REPLACE vs. SUBSTITUTE

Before we get into the details, we should look at the subtle difference between the REPLACE and SUBSTITUTE functions.

Both of them replace a string (or partial string) of text with another string.

The difference lies in how the first string is identified.

REPLACE selects the first string based on the position. So you might replace four characters, starting with the sixth character in the string.

SUBSTITUTE selects based on whether the string matches a predefined search. You might tell Excel to replace any instance of “2016” with “2017,” for example.

Other than that, the two functions are identical.

I’ll be using “replace” to talk about both of these actions. If you see “replace” written in lower-case letters, I’m referring to the action, not the function.

Kasper Langmann, Co-founder of Spreadsheeto

Get your FREE exercise file

We’ve put together an example workbook to help you learn the REPLACE and SUBSTITUTE functions.

Download it below and follow along with the article. It’ll be much easier to learn!

Download the FREE Exercise File

Download exercise file
Download free exercise file

Replacing characters with REPLACE

We’ll start with REPLACE. Let’s take a look at the syntax first:

The syntax of the REPLACE function

=REPLACE(old_text, start_num, num_chars, new_text)

old_text is the cell containing the text you want to replace.

start_num tells Excels where the text you want to replace starts within the cell.

num_chars is the number of characters you want to replace.

new_text is the text you’d like to replace that number of characters.

Keep in mind that new_text does not need to be the same length as num_chars. You could tell Excel to replace “K” with “Kasper,” and it would work.

Open up the example workbook, and take a look at the first sheet. You’ll see that we have a sequence of ID numbers, with associated birth dates, and names in another cell.

We’ll use REPLACE to remove the ID numbers and replace them with the names. At the end, the names and the dates will be in the same cells, and we’ll have gotten rid of the (presumably confidential) ID numbers.

To get started, click into cell C2.

Then type the following formula:

=REPLACE(A2, 1, 10, B2)

replace-formula

Looking back to the syntax definition, you can see that A2 is old_text, and B2 is new_text. So Excel will take text from B2 and replace some string from A2 with it. We’re using 1 as start_num, to make sure Excel starts at the beginning of the cell. And because the ID numbers are 10 digits long, we used 10 as the num_chars.

If you were to read this formula, you’d say something like this: “Replace ten characters, starting with the first, from cell A2, with the contents of cell B2.”

Hit Enter and let’s see what happens.

replace-results

Excel took the first ten digits of A2 and replaced them with the contents of B2! Exactly what we wanted.

Now use the fill handle to drag B2 down to the end of the column:

replace-fill

No matter how many characters are in each record’s name, Excel replaces only ten characters.

Kasper Langmann, Co-founder of Spreadsheeto

REPLACE works if you’re always replacing the same number of characters, and they’re always in the same place in the cell.

Let’s take a look at SUBSTITUTE, which may be more useful if your data isn’t quite as clean as it needs to be for REPLACE.

Replacing strings with SUBSTITUTE

If the string you want to replace doesn’t always appear in the same place, you’ll be better off with SUBSTITUTE. It has its own limitations, like REPLACE, but it can also be very useful. Let’s take a look at the syntax:

The syntax of the SUBSTITUTE function

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

This is a little different from the last syntax we looked at, so be careful not to get them mixed up.

text is the cell that contains the string you want replaced.

old_text is the sequence of characters that you want Excel to replace.

new_text is what Excel will insert in its place.

instance_num is optional, and tells Excel how many times it should replace old_text if it finds more than one instance.

We’ll take a look at that last one in a moment to clear it up.

On the second sheet of the example workbook, you’ll see a list of dates. If the dates were all formatted in the same way, we could use REPLACE to change the years. But because they have very different formats, we’ll use SUBSTITUTE instead.

Click into cell B2. Here’s the formula we’ll use:

=SUBSTITUTE(A2, “2016,” “2017”)

substitute-formula-cell

You can probably see what this will do: Excel will search cell A2 for the string “2016,” and if it’s there, replace it with “2017.”

Hit Enter and let’s see how it works!

substitute-results

Success!

Excel replaced “2016” with “2017.”

To see where SUBSTITUTE really shines, though, we’ll need to use the fill handle to drag the formula down through the entire column.

Kasper Langmann, Co-founder of Spreadsheeto
substitute-fill

As you can see, Excel replaced “2016” with “2017” no matter where it found the first string.

That’s where SUBSTITUTE differs from REPLACE.

Making multiple replacements with SUBSTITUTE

Remember the instance_num from the syntax above? Don’t forget about it—it’s a useful tool.

When you leave the argument blank, Excel replaces every instance of old_text that it can find. So if we used the above formula on a cell that contained “2016 2016 2016,” the result would have been “2017 2017 2017.”

With an instance_num of one, we would have gotten “2017 2016 2016.” With an instance_num of 2, it would have been “2017 2017 2016.”

This can be very useful when you have repetitive data and you only want to replace some of the matching strings.

Surgically replace with ease

With REPLACE and SUBSTITUTE, you can replace very specific strings with other strings. You can use letters, numbers, or other characters.

In short, you can replace text with extreme accuracy. And that saves you a great deal of time when you need to make a lot of edits.

If you combine REPLACE and SUBSTITUTE with other functions, you can do even more impressive replacements.

Try to come up with a combination of functions that will save you time in your work!

Kasper Langmann, Co-founder of Spreadsheeto