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)

Once I was given a list of phone numbers in their international format.  But for my needs, I did not want to include the country code and I just wanted the phone number.

I was looking for a quick way to remove the country code.

I discovered a cool way to do this using the REPLACE formula! 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 remove the country code, so it starts from the first character.

=REPLACE(C10, 1,



How many characters do we want to replace?

We want to remove all characters up to and including the first hyphen.

We will use the FIND formula.

FIND(“-“, C10) will get the location of the first hyphen.

=REPLACE(C10, 1, FIND(“-“, C10),


What text will serve as the replacement?

Since we want to remove this, you guessed it! We want the value to be an empty string which is depicted by the double quotations.

=REPLACE(C10, 1, FIND(“-“, C10), ” “)


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

CountBlank Formula in Excel What does it do? Counts the number of cells that are blank Formula breakdown: =COUNTBLANK(range) What it means: =COUNTBLANK(range of cells to check) Do you have a scenario where you want to count the number of cells that are blank in your Excel data? If y...
Add Comma After the Surname with Excel’s SUB... What does it do? It replaces specific text in a text string i.e. Substitutes new text for old text in a text string. Formula breakdown: =SUBSTITUTE(text, old_text, new_text, ) What it means: =SUBSTITUTE(This cell, From this TEXT character, To this TEXT character, )...
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 CLEAN Formula   What does it do? Removes all nonprintable characters from text Formula breakdown: =CLEAN(text) What it means: =CLEAN(this dirty text cell) There are times when imported text from other applications contain characters that are unprintable. The CLEAN...