Custom Cell Format for Phone numbers

**annieb** · 11-25-2010, 01:57 AM

Hi

Using MS Excel 2003

A single column in spreadsheet contains (and will continue to receive) phone numbers which are:
either 9-digit: 000-000-000
or 10-digit: 000-000-0000

The first digit in either format is a zero, which I want always to display.

Looking for a single Custom format, to format the entire column - which will allow either a 9 digit or a 10 digit string to be entered in any cell and always have the first 2 sets (6 digits, which start with a zero) displaying as 000-000- then allow the final set to show as either 000 or 0000

Is this possible? I've tried all ways I can think of but can't get it to work having a single format which will accept both types of ph.numbers.

I'd like to use the Format>Cells>Custom Format if possible - not VBA or Insert Function.

Thanks
Annieb

**DonkeyOte** · 11-25-2010, 04:24 AM

you state that in either case the first digit is always 0 so I wonder if the below Custom Format would work for you ?

Please Login or Register  to view this content.

of course the above assumes the numbers entered are numbers (which might not be the case) - eg 12345678 would be 012-345-678 and 123456789 would be 012-345-6789

**annieb** · 11-25-2010, 02:56 PM

Hi DonkeyOte
Works like a charm! Yes, all numbers (digits) in the data. Thanks so much for your help.

Cheers
Annieb

**annieb** · 11-25-2010, 05:44 PM

Hi DonkeyOte
Me again

I've just found some of the numbers I'm having to enter, have an extra zero on the end - so:
000-000-00000

[already have 000-000-000 and 000-000-0000 which format perfectly now, with the code you provided]

Is that code able to be expanded to allow fro the extra possible digit? Still keeping the leading number as a zero and having the last set comprising 5digits

I did try working it out myself, but not getting it right......

Thanks!
Anne

**annieb** · 11-25-2010, 11:44 PM

Grrr, I thought I had it - but no....
whichever way I try, I get any 2 of the required formats to display correctly - but not the 3rd...

I don't mind if the number 000-000-00000 (5 digits in last set) has to be : 000-000-000-00, if that helps - but want to keep the other numbers dsplaying format 000-000-0000 or 000-000-000

Hope you can help!

Cheers
Anne

**DonkeyOte** · 11-26-2010, 04:00 AM

Originally Posted by annieb

I've just found some of the numbers I'm having to enter, have an extra zero on the end - so:
000-000-00000

[already have 000-000-000 and 000-000-0000 which format perfectly now, with the code you provided]

Is that code able to be expanded to allow fro the extra possible digit?

Add a 2nd rule:

Please Login or Register  to view this content.

**annieb** · 11-26-2010, 07:29 AM

Perfect, Thank you again!

Cheers
Annieb

**Ju1cy** · 02-06-2013, 08:37 AM

Hi, I appreciate that this thread may be ignored as it is old but my problem relates to this question about telephone numbers.

The difference with mine is I need the following:

I need telephone numbers to be entered like this

0123 1234567 if area code is only 4 digits followed by 7 digits, (11 total)

01234 123456 if area code is 5 digits followed by 6 numbers, (11 total)

and if 01234 12345 if area code is 5 digits but only followed by 5, (10 total)

All three are possible options and I need to allow this so that when the numbers are entered like this: 01231234567 it changes to: 0123 1234567 etc.

I've tried doing the above formula: [>999999999]0000 0000000;[>99999999]00000 000000 obviously unsuccessfully, however the first part does work.

Please help as I am getting fed up of correcting others mistakes.

Thank you in advance

**deyarne** · 02-06-2013, 06:47 PM

Hi I also have a phone number problem, only i think mine is the opposite. I have phone numbers entered in the following format 0402 0402 0402 in one spreadsheet and they seem to be a text file, I then need to vlookup into another spreadsheet which has the mobile numbers entered as numbers 40204020402??? Help it is doing my head in.

Have tried custom formatting etc but the text ones stay as text.

Cheers

**arlu1201** · 02-07-2013, 06:14 AM

deyarne,

Welcome to the Forum.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.