Excel - Formatting for zeros

  Dirty Dick 15:38 02 Dec 2005

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?



  VoG II 15:40 02 Dec 2005

type a ' in front of the first digit.

  DerekR 15:58 02 Dec 2005

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

  huzzar 15:59 02 Dec 2005

Type your numbers as "Text"

  HondaMan 16:17 02 Dec 2005

I think you will find "O" is a vowel

  Batch 16:44 02 Dec 2005

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 2005

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 2005

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 2005

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

  Simsy 17:28 02 Dec 2005

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.



  Simsy 17:36 02 Dec 2005

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


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

I hope this helps,



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

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

HomePod review

Comment regarder les Oscars 2018 ?