Excel help with phone number extension format

[hoihtah]

hi... once again, i'm a bit stuck with an excel problem.
if anyone can shed a light on this one... i'd greatly appreciate it.

i have a column of phone number with 7-14 digit numbers.
obviously 7 digit number special formating will be ###-####
and excel has a default phone formating as [<=9999999] ###-####;###-###-####
for 10 digits

but how do i deal with extensions.
let say i have a 12 digit number

987654321012 this should be translated to 987-654-3210 x12?

i need a formula that 'll deal with all numbers between 7-14 digit numbers.

once again... thanx guys for helping me out.

 

[BigSmooth]

Use this as your custom format: [<=9999999]###-####;[<=9999999999](###) ###-####;(###) ###-#### x##

 

[hoihtah]

bigsmooth,

thanx for that info... but the formula won't work if the number is 13 digits.
1234567890123 will list as 1234-567-8901 x23

 

[hoihtah]

i tried to create the condition the third time

[<=9999999]###-####;[<=9999999999]###-###-####;[<=99999999999]###-###-#### x#;[<=999999999999]###-###-#### x##
but this gives me an error.

is it because of the size of number that i'm dealing with? or could it be that excel doesn't like three arguments?

 

[hoihtah]

there must be another way of dealing with this... no?

 

[BigSmooth]

I tried to extend it, but the help section says you can only use four conditions. You need a separate format for 7, 10, 11, 12, 13, and 14 digits and I couldn't figure out a way to do this with a 4-condition limit. Sorry! Maybe someone else can figure it out.

 

[hoihtah]

thanx bigsmooth for trying.

anyone else? please help.

 

[kranky]

I think this will work but it will be a big pain in the butt.

Try using a big nested IF statement to test the length of the string then use LEFT, MID, RIGHT along with CONCATENATE to piece the dashes in.

The help text on IF shows how to use a nested IF to check multiple cases.

So, [I know there's probably some syntax problems, but you should get the idea]...

IF (LEN(A1)=7,CONCATENATE(LEFT(A1,3),"-",RIGHT(A1,4), IF (LEN(A1)=8,CONCATENATE....blah, blah)

It will be a pretty long formula.

 

[Psylence]

Click on Data, Text to Columns...., make sure Fixed width is checked and click on Next. Put a break line after the third digit, the sixth digit, and the 10th digit, then click on Finish. Go to the column to the right of the extension number and put in a formula like this: =CONCATENATE(A2,"-",B2,"-",C2,"x",D2) Copy the formula down and there you go!

Edit: A 7 digit number would just need the formula =CONCATENATE(A2,"-",B2)
A 10 digit number would just need the formula =CONCATENATE(A2,"-",B2,"-",C2)
And for all the numbers with extenstions, use the entire formula above.
I'd definitely sort your list of numbers first so you would just have to type in 3 formulas and paste the rest down.

 

[BigSmooth]

An alternative would be to simply sort the list by phone number (since all 10 digit numbers will be greater numerically than any 7 digit numbers, etc.), then highlight all of the 7-digit numbers, apply the correct format, then do the same for all the 10, 11, 12, 13, & 14 digit numbers.

Then you could resort the list however you needed to. Lateral thinking!

 

[hoihtah]

i'm with you guys on your thinking...
psylence,

your method works on 10+ digits... but for 7 digit numbers it won't.

thanx guys ... for helping me out.

i'm beginning to think that the long equation is the only way to go.
i need to set a macro so i won't have to manually separate 7s from 10+'s.

unless there's a better way to do that.
thanx again... you guys are the best.

 

[Psylence]

For all 7 digit numbers, just use the formula =CONCATENATE(A2,"-",B2).

 

[hoihtah]

thanx.
i'll use CONCATENATE(A2,"-",B2,C2) for 7digit.
i suppose i need to set up a if formula eitherway.