Format Telephone Numbers

[trevorbranton]

Hi All

First of all, if this has already been posted and resolved, SORRY!. I could not find it when searching.

My question is quite simple, yet, I cant find an answer/solution as yet.

I wish to enter a phone number in to excel (using 2007 version) in the following way, (01234) 456789 (5 digits for area code and 6 digits for number) - I have figured out that the format to use is something like '(0####)_######'

However some locations where I live still have a 5 digit phone number and therefore I will need to have the facility to have a number like (01234) 56789 (5 digits for area code and 5 digits for number)

My problem is that what ever combination of codes I try, I end up with a mess Its the occasional 10 digit combination is the route of the problem and would love to see if I can overcome this.

Any suggestions or help would be greatly appreciated.

Thanks

 

[Gerald Higgins]

Hi, welcome to the board.
Do you need to do this via formating, or is it an option to resolve it using text formulas ?
Also, it might help to give some examples of what the source data actually looks like, for both examples.

 

[Jon von der Heyden]

Hi and welcome to the board!

Try the following custom number format:

[<1000000000](0#####) #####;[>=1000000000](0#####) ######;""

 

[trevorbranton]

I dont mind - its just a data base I use for keeping my client info in, but I want the info to be shown correctly

How I want the numbers to be shown is:

(01234) 123456 & if a 10 digit number (01234) 12345

Does this help?

 

[Jon von der Heyden]

Did you see my suggestion? Here's a sample:

Excel Workbook
	A	B
1	Formatted	Unformatted
2	(01234) 456789	1234456789
3	(01234) 56789	123456789
...

Excel 2003

 

[trevorbranton]

Hi and welcome to the board!

Try the following custom number format:

[<1000000000](0#####) #####;[>=1000000000](0#####) ######;""

PERFECT - that is it...

Thanks ever so much, I knew some one would have the answer.

Cheers