I want to display numbers with zeros in front of them, eg 005

[Tom]

Hi,
I have a friend who wishes to display zeros in front of other numbers and
the Excel program keeps disappearing them. I tried to modify the
Tools/Options/View/Zero Values but whether this box is checked or not doesn't
seem to make any difference.
Any ideas would be greatly appreciated.
Tom

 

[Joel]

You need to format cells using Format - Numbers - Special

Use something like this to make 4 digits
#0000

 

[Tom]

Hi Joel,

Thanks for your reply - does that mean you have to see the # symbol or is
there a way of only seeing the zeros?

Cheers

Tom

 

[Joel]

The # is invisible. I seem to be using the work visible/Invisible a lot
tonight.

 

[Tom]

Hi again Joel,

Do you have to change a setting to make the # invisible?
I can see it in front of my numbers.

Cheers
Tom

 

[Joel]

One of my replies didn't get posted. Try this formula

=Text(0,"#000")

The formula should do the same thing as special format. You should see 3
zeroes with no #. I want to see if this works because it should using the
same internal function in excel as formating.

 

[Dave Peterson]

If you want to see 5 digits, you can use a custom format of:
00000

If you want to see 3 digits, you can use a custom format of:
000

 

[Tom]

Hi again Joel,

Thanks for the formula - it works for me, I have emailed it to my friend to
give it a go in her 'real' task - will let you know if it's successful.

Cheers
Tom

PS By any chance do you have a relatively simple formula for calculating
the days, months and years between two dates? The challenge is to administer
a school test to children on a certain date, then apply a formula which will
produce their exact age in years, months, at that point in time.

 

[Gord Dibben]

Tom

Use the undocumented DATEDIF function.

See Chip Pearson's site for details.

http://www.cpearson.com/excel/datedif.aspx


Gord Dibben MS Excel MVP