All You Need to Know About

# REPLACE Formula in Excel

The Excel REPLACE Function is one of the most used Excel Formulas. Learn about the different ways you can apply the REPLACE Function in Excel!

Here are the top things on what you can do with the REPLACE Formula in Excel:

## Cleaning Data with Excel’s REPLACE Formula

What does it do?
Replaces part of a text string, based on the number of characters you specify, with a different text string
Formula breakdown:
=REPLACE(old_text, start_num, num_chars, new_text)
What it means:
=REPLACE(this cell, starting from this number, all the way to this number, with this new text)

There are times when we are given a bunch of data that is not clean, is incorrect or just needs to be updated and we need to make some manual adjustments to it.
For example, we have a set of phone numbers that have incorrect area codes in it:

We want to change the area code of 817, into the area code 123.
The REPLACEÂ formula in Excel can help you changeÂ the text very very easily…goodbye to manual adjustments!
I explain how you can do this below:

STEP 1: We need to enter the ReplaceÂ function next to the cell that we want toÂ cleanÂ the data from:

## =REPLACE

STEP 2:Â The ReplaceÂ arguments:

## old_text

Which text do we want to change?
Reference the cell that contains the text string:

## start_num

Which character do we want to start the replacement from?
We want to replace the area code 817, which starts fromÂ the 4th characterÂ in the phone number sequence:
1st character is the +
2nd character is the 1
3rd character is the
4th character is the 8

## num_chars

How many characters do we want to replace?
We want to replace the area code 817, which is 3 characters long:

## new_text

What text will serve as the replacement?
We want to replace the area code 817 with the area code 123:

## =REPLACE(C10, 4, 3, “123”)

STEP 3: Do the same for the rest of the cells by dragging the REPLACEÂ formula all the way down using the left mouse button.
Note that all of the phone numbersÂ are now clean:

## Change Phone Area Codes with Excelâ€™s REPLACE Formula

What does it do?
Replaces part of a text string, based on the number of characters you specify, with a different text string
Formula breakdown:
=REPLACE(old_text, start_num, num_chars, new_text)
What it means:
=REPLACE(this cell, starting from this number, all the way to this number, with this new text)

There are times when we are given a bunch of data that is not clean, and we needed to make manual adjustments to it.
For example, we have a set of phone numbers that have incorrect area codes in them.
We want to replaceÂ each phone number’s old area code with a newÂ area code,Â specified in the NEW column:

We want to change the area code of 370, into the area code of 111.
ForÂ the second phone number, we want to change the area code of 961 to 222…and so on.
The REPLACEÂ formula in Excel can help you clean & replace the text very very easily…goodbye to slow manual adjustments!
I explain how you can do this below:

STEP 1: We need to enter the ReplaceÂ function next to the cell that we want toÂ cleanÂ the data from:

## =REPLACE

STEP 2:Â The ReplaceÂ arguments:

## old_text

Which text do we want to change?
Reference the cell that contains the text string:

## start_num

Which character do we want to start the replacement from?
We want to replace the area code 370, which is the 2ndÂ character in the phone number. Â The open parenthesis ( is the 1st character:

## num_chars

How many characters do we need to replace?
We want to replace the area code 370, which is 3 characters long:

## new_text

What text will serve as the replacement?
We want to replace the area code 370Â with the area code of 111. The replacement values are in the next column (column B):

## =REPLACE(A11, 2, 3,Â B11)

STEP 3: Do the same for the rest of the cells by dragging the REPLACEÂ formula all the way down using the left mouse button.
Note that all of the phone numbersÂ are now clean:

## Extract Last Name with Excelâ€™s REPLACE Formula

What does it do?
Replaces part of a text string, based on the number of characters you specify, with a different text string
Formula breakdown:
=REPLACE(old_text, start_num, num_chars, new_text)
What it means:
=REPLACE(this cell, starting from this number, all the way to this number, with this new text)

I had a scenario where I wanted to extract the last names from a list of names using a formula.
Did you know that we can creatively use theÂ Replace formulaÂ to replace the first name with an empty string, leaving us with the SURNAME?
Here is what we want to happen:

I explain how you can do this below:

STEP 1:Â To start off, let us try the Search function and see what it will give us. We want to search on which character the space resides on. Â  Type in:

## =SEARCH(” “, C11)

You will see that it returnedÂ 6.
This means our space is on the 6th character of the name Talon Ferguson.
We will use this in ourÂ Replace function later in STEP 3.

STEP 2: Clear the Search function.Â We need to enter the ReplaceÂ function next to the cell that we want toÂ cleanÂ the data from:

## =REPLACE

STEP 3:Â The ReplaceÂ arguments:

## old_text

Which text do we want to change?
Reference the cell that contains the text string:

## start_num

Which character do we want to start the replacement from?
We want to replace the first name, which resides on the first character:

## num_chars

How many characters do we need to replace?
We don’t have the exact number of characters, so this is where the SEARCH function comes in handy from STEP 1 above.
Search for the space character ” “ which tells us the end of the First Name:

## new_text

What text will serve as the replacement?
Now that we have accounted for all the characters from the First Name, we need to clear these.
We can do this by replacing it with an empty string.
This will “erase” the First Name, and leave us with the Last Name.

## =REPLACE(C11, 1, SEARCH(” “, C11),Â “”)

STEP 4: Do the same for the rest of the cells by dragging the REPLACEÂ formula all the way down using the left mouse button.
Now we have all of the last names:

Latest Tutorials

## Clear Everything Before the Hyphen with Excel’s REPLACE Formula

February 13, 2017

## Change Phone Area Codes with Excelâ€™s REPLACE Formula

November 15, 2016

November 8, 2016

November 2, 2016