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:

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(C10,

start_num

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,

 

num_chars

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),

new_text

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:

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 ...
Cleaning Data with Excel’s SUBSTITUTE Formul... What does it do? Substitutes new_text for old_text in a text string. Formula breakdown: =SUBSTITUTE(text, old_text, new_text, ) What it means: =SUBSTITUTE(This cell, By this text character, To this text character, ) When you needed to replace a specific text...
Round Off Numbers with Excel’s ROUND Formula What does it do? Rounds a number to the nearest decimal based on your specified number of digits Formula breakdown: =ROUND(number, num_digits) What it means: =ROUND(the number, number of decimal places to round off) Ever had the need to round off numbers? ...
Extracting Data with Excel’s LEFT Formula What does it do? It returns the first character or characters in a text string, based on the number of characters you specify. Formula breakdown: =LEFT(text, ) What it means: =LEFT(look in this cell, extract X characters) There are times when you will need t...