Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
You need to format cells using Format - Numbers - Special
Use something like this to make 4 digits #0000 "Tom" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
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" wrote: You need to format cells using Format - Numbers - Special Use something like this to make 4 digits #0000 "Tom" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
The # is invisible. I seem to be using the work visible/Invisible a lot
tonight. "Tom" wrote: 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" wrote: You need to format cells using Format - Numbers - Special Use something like this to make 4 digits #0000 "Tom" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
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" wrote: The # is invisible. I seem to be using the work visible/Invisible a lot tonight. "Tom" wrote: 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" wrote: You need to format cells using Format - Numbers - Special Use something like this to make 4 digits #0000 "Tom" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
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. "Tom" wrote: 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" wrote: The # is invisible. I seem to be using the work visible/Invisible a lot tonight. "Tom" wrote: 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" wrote: You need to format cells using Format - Numbers - Special Use something like this to make 4 digits #0000 "Tom" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
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 wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
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. "Joel" wrote: 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. "Tom" wrote: 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" wrote: The # is invisible. I seem to be using the work visible/Invisible a lot tonight. "Tom" wrote: 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" wrote: You need to format cells using Format - Numbers - Special Use something like this to make 4 digits #0000 "Tom" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
I want to display numbers with zeros in front of them, eg 005
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 On Sun, 8 Jun 2008 06:15:00 -0700, Tom wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I display negative numbers as zeros? | Excel Discussion (Misc queries) | |||
add zeros in front of numbers | Excel Discussion (Misc queries) | |||
How do I keep zeros in front of other numbers (like a zip code)? | Excel Discussion (Misc queries) | |||
How do I keep the zeros in front of numbers when i split a cell | Excel Worksheet Functions | |||
How do I get zeros to stay in front of a # when using the concate. | Excel Worksheet Functions |