Excel Format String (Special Phone Number) 2

[BoulderBum]

We have data stored like this:

3035557777
303555777744 <- extension 44

With the built-in formatting, it's easy enough to display the first number like:

(303) 555-7777

but the second number will get displayed like:

(30355) 577-7744

Is there a format string to say, put the first few numbers in the normal phone number, then dump all additional characters at the end?

I tried:

(###) ###-#### ##

Which sort of works, but I need a # symbol for each possible digit of the extension (i.e. if there is a 3 digit extension, everything will get screwed up again.

 

[Corgano]

Yes, you need to split that out.

Say the data is in A1

In B1:
=LEFT(A1,10)

This pulls the normal phone number. Format that cell accordingly.

In C1:
=MID(A1,10,8)

Pulls the data from cell A1, starting at character #10 (after the phone number ends) for 8 characters. You can make the 8 whatever you need to.

 

[Corgano]

If you want to be tricky you can make C1:

="Ext. "&MID(E18,10,8)

 

[Corgano]

Bah! Did a tad more testing and found it didn't like using a text function on a number with a special format. Go figure.

The resolution (with data in A1) is:

In B1:
=TEXT(LEFT(A1,10),"(###)-###-####")

In C1:
="Ext. "&MID(E18,10,8)

The text function is turning to formula result into a number with the format (###)-###-#### which you can change to suit your needs.

 

[Corgano]

Man I need a cup of coffee...

C1:
="Ext. "&MID(E18,11,8)



My apologies.

 

[Dreamboat]

Your best bet without using VBA:

Make all extensions be 5 characters long, with leading zeroes or put them in a separate column. Wish I could offer more.

Anne Troy
VBA Coders, please help:

http://www.vbaexpress.com/kb

 

[BoulderBum]

Thanks guys. Unfortunately I don't have any control over the format of the data, so I'll have to look to Corgano's solution.

 

[macropod]

If you were to input the numbers with extensions as +ve values and numbers without extensions as -ve values, you could use a custom number format like:
(000) 000-0000 x 00;(000) 000-0000
This would cause 303555777744 to display as:
(303) 555-7777 x 44
and 3035557777 to display as:
(303) 555-7777

Cheers

 

[TonyJollans]

Hi macropod,

Surely he could do ..

[blue][tt][<=9999999999](###) ###-####;(###) ###-#### x ##[/tt][/blue]

.. without having to mess about with signs, but that does not help if he doesn't know the length of the extension number.

I've been scratching my head but can't see any way to do it with cell formatting alone (without, at least, some constraints on the number).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.