Phone number format on numbers with Extensions

[Access Joe]

Hi all,

I have a worksheet with imported data. One column contains phone numbers
that SOMETIMES have a three or four digit extension after them. Example of
exactly what it looks like:

9735551234
9731234443
9735559876473
97322292835743
9736758372
9734448573928
etc etc for thousands of rows

What I'd like to do is apply the custom phone number format so that they
look like phone numbers: i.e. (973) 555 -1234. Problem is, I can't apply it
to the entire column because those three or four digit extensions get in the
way.

My question - is there a way to remove that three or four digit extension
from the end of the phone number so I can apply a phone format to all the
cells at once?

Thanks everyone!

 

[akphidelt]

Say the first phone number is in cell A1... try this formula

="("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&MID(A1,7,4)

This will show it looking like the phone number you described.

Then to parse out the extension try this formula
=RIGHT(A1,LEN(A1)-10)

 

[Access Joe]

PERFECT! Exactly what I needed! Thank you!

Say the first phone number is in cell A1... try this formula

="("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&MID(A1,7,4)

This will show it looking like the phone number you described.

Then to parse out the extension try this formula
=RIGHT(A1,LEN(A1)-10)

 

[akphidelt]

No problem, glad I could help.

PERFECT! Exactly what I needed! Thank you!