Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Format String (Special Phone Number) 2

Status
Not open for further replies.

BoulderBum

Programmer
Joined
Jul 11, 2002
Messages
2,179
Location
US
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.
 
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.
 
If you want to be tricky you can make C1:

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

 
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.
 
Man I need a cup of coffee...

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



My apologies.
 
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:
 
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.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top