extract 7 digit phone number from 10 digit phone number

batt4u

New Member
Joined
Feb 20, 2013
Messages
2
Hi Guys,

Glad I found the forum. I'm in a pinch:

I have a list of phone numbers in excel in this format (123) 456-7890

I need to:
- extract the area code to a new field
- the phone number to a new field without the ( )

It would look like column1 column2
. . . 123 4567890

Is there a code that will save me?

Thank you for the help!
Jason
 
Last edited:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
I have a list of phone numbers in excel in this format (123) 456-7890

I need to:
- extract the area code to a new field
- the phone number to a new field without the ( )

It would look like column1 column2
. . . 123 4567890

Is there a code that will save me?
Assuming your data starts in cell A1, put these formulas where indicated and copy them down as needed...

B1: =MID(A1,2,3)

C1: =MID(A1,7,8)
 
Upvote 0
Hi and welcome to the forum,

Assuming that is not just formatting that has been applied to a number, maybe try:

B1 (area code):
=MID(A1, 2, FIND(")", A1) - 2)

C1 (phone number):
=TRIM(SUBSTITUTE(SUBSTITUTE(A1, "(" & B1 & ")", ""), "-", ""))
 
Upvote 0
Correct Chicken,

The numbers were exported out of a database and the formatting ( ) - are actually included in the data itself. I will try this right now.
 
Upvote 0
You can do it with almost the exact same formula:

=LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), " ", ""), ")", ""), "(", "")),3)
=RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), " ", ""), ")", ""), "(", "")),7)

and if you just needed the 10 digits in single cell (for say a dialing campaign):
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "-", ""), " ", ""), ")", ""), "(", ""))
 
Last edited:
Upvote 0

Forum statistics

Threads
1,226,695
Messages
6,192,475
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top