Pinterest Pixel

Clear Everything Before the Hyphen with Excel’s REPLACE Formula

What does it do? Replaces part of a text string, based on the number of characters you... read more

Download Excel Workbook
Bryan
Posted on

Steps To Follow

Overview

Clear Everything Before the Hyphen with Excel's REPLACE Formula | MyExcelOnline Clear Everything Before the Hyphen with Excel's REPLACE Formula | MyExcelOnline

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:

Clear Everything Before the Hyphen with Excel's REPLACE Formula | MyExcelOnline

Download excel workbookREPLACE-FORMULA-Clear-before-hyphen.xlsx

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

=REPLACE

Clear Everything Before the Hyphen with Excel's REPLACE Formula

 

See also  CHOOSE Formula in Excel

STEP 2: The Replace arguments:

old_text

Which text do we want to change?

Reference the cell that contains the text string:

=REPLACE(C10,

Clear Everything Before the Hyphen with Excel's REPLACE Formula

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,

 Clear Everything Before the Hyphen with Excel's REPLACE Formula

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

Clear Everything Before the Hyphen with Excel's REPLACE Formula

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

Clear Everything Before the Hyphen with Excel's REPLACE Formula

 

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.

See also  Change Font to All Caps Quickly: Top 3 Methods

Note that all of the phone numbers are now clean:

Clear Everything Before the Hyphen with Excel's REPLACE Formula

 

 

If you like this Excel tip, please share it
Clear Everything Before the Hyphen with Excel's REPLACE Formula | MyExcelOnline Clear Everything Before the Hyphen with Excel's REPLACE Formula | MyExcelOnline
Clear Everything Before the Hyphen with Excel's REPLACE Formula | MyExcelOnline
Bryan

Bryan is a best-selling book author of the 101 Excel Series paperback books.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...