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



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



STEP 2: The Replace arguments:


Which text do we want to change?

Reference the cell that contains the text string:





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

=REPLACE(C10, 4,




How many characters do we want to replace?

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

=REPLACE(C10, 4, 3,




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:






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


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Cleaning Data with Excel’s TRIM Formula What does it do? Removes unneeded spaces in your text, except single spaces in between words Formula breakdown: =TRIM(text) What it means: =TRIM(text that you want extra spaces to be removed) In the quest for cleaner data, one of the common scenarios is remo...
Add Leading Zeros in Excel Do you have a lot of numbers with an uneven number of digits in your Excel list? Do you want to make them uniform by adding leading zeros to them? Well, it's a pain to add zeros in front of them one by one! Thankfully, Excel allows you to add leading zeros with one singl...
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 ...
CountIf Formula in Excel What does it do? Counts the number of cells that matches your specified condition Formula breakdown: =COUNTIF(range, criteria) What it means: =COUNTIF(range of cells to check, condition to check against) Do you have a scenario where you want to count the num...