Excel - Formatting for zeros

  Dirty Dick 15:38 02 Dec 05
Locked

I am making a database of names, addresses and telephone numbers using Excel 2003. When I type in telephone numbers and press enter, the first zero disappears. Ive tried all sorts of formatting, but can't find any solution. Anybody help, please?

Thanks

DD

  VoG II 15:40 02 Dec 05

type a ' in front of the first digit.

  DerekR 15:58 02 Dec 05

you will need to use the consenant O (capital) rather than a zero. Thats how I do it anyway.

  huzzar 15:59 02 Dec 05

Type your numbers as "Text"

  HondaMan 16:17 02 Dec 05

I think you will find "O" is a vowel

  Batch 16:44 02 Dec 05

If you format the cell, one of the formatting categories is "Special" and this includes a "Phone Number" type that you can select.

  Dirty Dick 16:46 02 Dec 05

Thanks for all the replies. I don't know whats happened, but some of the cells will accept the initial zero, but some drop them off as I hit ENTER. I've re-formatted all the cells in the "phone" column, but some cells have a zero and some don't. I have typed a ' asuggested by VoG™, that works but it puts a little green triangle in the top left hand corber of the cell. I've also tried foratting the cells as "text" and that seems to work, but I'm getting a bit confused as to why some cells are ok but others not, when they are all formatted the same.

  Dirty Dick 16:53 02 Dec 05

I've tried formatting the cells as you suggest, but when I click on "Specail", there are no "Phone Number"selections just a list of countries ?

  VoG II 17:03 02 Dec 05

You have to select English (United States) as le Locale then you will get the phone number option.

  Simsy 17:28 02 Dec 05

and I get the phone number option under special, even though I'm set as English UK...

However, it doesn't seem to solve the problem; The initial 0 is still dropped. It just puts the first 3 digits, after the inital 0, in brackets, as a US area code.

The way to do it, as huzzar suggested, is to format the cells as "Text"

If the numbers have already been entered and have had the zero cut off, then just reformatting as text wont be sufficient... you'll have to add the zero back on.

Regards,

Simsy

  Simsy 17:36 02 Dec 05

another way round it...

The method VoG™ suggests works because by having a character that isn't one of the number digits, (0-9), Excel knows that this can't possibly be a number. Putting a ' at the front achieves this, and you can leave the cell formatted as "General"

So does a space.

If the number is, (with apologies to the person concerned if this is a real number!), 020 7555 5555, entering it like this

020 7555 5555 will not give a problem, because of the spaces.

entering it like this

02075555555

Excel thinks it's a number, and the initial 0 isn't needed so drops it off.

I hope this helps,

Regards,

Simsy

This thread is now locked and can not be replied to.

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…