extract 7 digit phone number from 10 digit phone number

[batt4u]

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

 

[Rick Rothstein]

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)

 

[circledchicken]

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

 

[batt4u]

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.

 

[Rick Rothstein]

...the formatting ( ) - are actually included in the data itself.

If that is the case, I am pretty sure the two short formulas I posted should work for you then.

 

[Pat_Mc_64]

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