Excel field length issue 2

[reitter]

I have a 3000+ spreadsheet that lists fax numbers. The problem is sometimes the number is only 4 charachters (an internal extension) but it could also be a regualr 10 digit phone number in the field or a 7 digit phone number. I need to get all numbers into the following format (000)-000-0000

Example:

Cell A1 Cell A2
Dr. 1 520-694-1111
Dr. 2 520-694-1112
Dr. 3 694-1113
Dr. 4 1114

 

[AnotherHiggins]

So how do you want a four-digit number to be displayed?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[DaleWatson123321]

With phone # is B1, copy this formula to C1.

=IF(LEN(B1)=12,B1,IF(LEN(B1)=8,"000-"&B1,"000-000-"&B1))

Then copy down for rest of rows.

Regards, Dale Watson

 

[reitter]

I figured I needed to do this in a few steps. Any of the numbers that are 4 digits (internal extensions) always will have the following area codes/prefixs: 520-694-xxxx


I did copy the formula below and this does get all of my 4 digit numbers to a 10 digit format - but it causes any number that was more than 4 digits to start with it adds that whole thing the the left:

(928) 329-8824 is coming out to: 000-000-9283298824

 

[DaleWatson123321]

I understand (now ) that your numbers are "formatted" for the way they are displayed.

In that case, the following formula will work.

=IF(LEN(B1)=10,"("&LEFT(B1,3)&") "&MID(B1,4,3)&"-"&RIGHT(B1,4),IF(LEN(B1)=7,"(000) "&LEFT(B1,3)&"-"&RIGHT(B1,4),IF(LEN(B1)=4,"(000) 000-"&RIGHT(B1,4))))

This will convert the numbers to text.

Regards, Dale Watson

 

[dallen43]

I think Dale assumed that B1 contained 520-694-1111, 694-1113 or 1114; where the field included the hyphens.

If B1 contains 5206941111, 6941113 or 1114; then try this formula.

=IF($B2>10000000,MID($B2,1,3)&"-"&MID($B2,4,3)&"-"&MID($B2,7,4),IF($B2>10000,"520-"&MID($B2,1,3)&"-"&MID($B2,4,4),"520-649-"&MID($B2,1,4)))

Hope this helps,
Deb

 

[reitter]

Got it -thanks for the help!