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:

replace-phone-area-00

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 and please go to the bottom of the page to see the animated gif tutorial:

DOWNLOAD EXCEL WORKBOOK

 

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:

=REPLACE(A11,

replace-phone-area-01

 

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:

=REPLACE(A11, 2,

 replace-phone-area-02

 

num_chars

How many characters do we need to replace?

We want to replace the area code 370, which is 3 characters long:

=REPLACE(A11, 2, 3,

replace-phone-area-03

 

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)

replace-phone-area-04

 

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:

replace-phone-area-05

replace-phone-area

HELPFUL RESOURCE:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

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 ...
Replace Excel Formatting with Another Formatting Imagine this, you have a table full of bold text.  The bold text could also be all over your worksheet in random cells. Then you decide that the bold text does not suit your expected design and prefer red colored text instead. What would you do? Changing all of the forma...
CountIfs Formula in Excel What does it do? Counts the number of cells that matches your specified conditions Formula breakdown: =COUNTIFS(range1, criteria1, , , ...) What it means: =COUNTIFS(range of cells to check1, condition to check against1, , , ...) Do you have a scenario where ...
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 ...