Home |
Search |
Today's Posts |
#1
|
|||
|
|||
formatting numbers beginning with zero
Hi all
I have a large worksheet with lots of numbers on it beginning with 00, however, whenever I enter a number, Excel automatically removes the zeros. I have tried formatting the cells as General, Numbers but it still removes the zeros. What can I do to get around this?? I realise I can put ' before the numbers but I need them to be recognised as numbers, not text. Thank you. Louise |
#2
|
|||
|
|||
Hi Louise
Try formating the cell NumberCustom0000 or as many digits as you need. Regards Roger Govier Louise wrote: Hi all I have a large worksheet with lots of numbers on it beginning with 00, however, whenever I enter a number, Excel automatically removes the zeros. I have tried formatting the cells as General, Numbers but it still removes the zeros. What can I do to get around this?? I realise I can put ' before the numbers but I need them to be recognised as numbers, not text. Thank you. Louise |
#3
|
|||
|
|||
Try
Format Cells Custom and then enter "00"General This will always include two leading zeros Format Cells Custom and then enter 00000 This will always display 5 digits, supplying as many leading zeros as necessary -- Gary's Student "Louise" wrote: Hi all I have a large worksheet with lots of numbers on it beginning with 00, however, whenever I enter a number, Excel automatically removes the zeros. I have tried formatting the cells as General, Numbers but it still removes the zeros. What can I do to get around this?? I realise I can put ' before the numbers but I need them to be recognised as numbers, not text. Thank you. Louise |
#4
|
|||
|
|||
Hi Roger
Thank you for your quick reply. This has formatted the cells perfectly. Is there any way I can get it to show the actual number in the formula bar too? For example, it still shows 97 instead of 00097. Thanks again. Louise "Roger Govier" wrote: Hi Louise Try formating the cell NumberCustom0000 or as many digits as you need. Regards Roger Govier Louise wrote: Hi all I have a large worksheet with lots of numbers on it beginning with 00, however, whenever I enter a number, Excel automatically removes the zeros. I have tried formatting the cells as General, Numbers but it still removes the zeros. What can I do to get around this?? I realise I can put ' before the numbers but I need them to be recognised as numbers, not text. Thank you. Louise |
#5
|
|||
|
|||
Hi Louise
I don't think so. But there may be workaround that someone else knows. The formula bar always shows the contents of the cell without any formatting. The cell displays the contents of the cell subject to whatever formatting has been applied to that cell. Regards Roger Govier Louise wrote: Hi Roger Thank you for your quick reply. This has formatted the cells perfectly. Is there any way I can get it to show the actual number in the formula bar too? For example, it still shows 97 instead of 00097. Thanks again. Louise "Roger Govier" wrote: Hi Louise Try formating the cell NumberCustom0000 or as many digits as you need. Regards Roger Govier Louise wrote: Hi all I have a large worksheet with lots of numbers on it beginning with 00, however, whenever I enter a number, Excel automatically removes the zeros. I have tried formatting the cells as General, Numbers but it still removes the zeros. What can I do to get around this?? I realise I can put ' before the numbers but I need them to be recognised as numbers, not text. Thank you. Louise |
#6
|
|||
|
|||
I tried Roger's earlier suggestion of this and it worked fine, however, is
there any way I can get the number to display the zeros in the actual formula bar too? It stil displays 94 instead of 00094 there. Thanks. Louise "Gary''s Student" wrote: Try Format Cells Custom and then enter "00"General This will always include two leading zeros Format Cells Custom and then enter 00000 This will always display 5 digits, supplying as many leading zeros as necessary -- Gary's Student "Louise" wrote: Hi all I have a large worksheet with lots of numbers on it beginning with 00, however, whenever I enter a number, Excel automatically removes the zeros. I have tried formatting the cells as General, Numbers but it still removes the zeros. What can I do to get around this?? I realise I can put ' before the numbers but I need them to be recognised as numbers, not text. Thank you. Louise |
#7
|
|||
|
|||
yes, I thought so.
Thanks for all your help. Louise "Roger Govier" wrote: Hi Louise I don't think so. But there may be workaround that someone else knows. The formula bar always shows the contents of the cell without any formatting. The cell displays the contents of the cell subject to whatever formatting has been applied to that cell. Regards Roger Govier Louise wrote: Hi Roger Thank you for your quick reply. This has formatted the cells perfectly. Is there any way I can get it to show the actual number in the formula bar too? For example, it still shows 97 instead of 00097. Thanks again. Louise "Roger Govier" wrote: Hi Louise Try formating the cell NumberCustom0000 or as many digits as you need. Regards Roger Govier Louise wrote: Hi all I have a large worksheet with lots of numbers on it beginning with 00, however, whenever I enter a number, Excel automatically removes the zeros. I have tried formatting the cells as General, Numbers but it still removes the zeros. What can I do to get around this?? I realise I can put ' before the numbers but I need them to be recognised as numbers, not text. Thank you. Louise |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting numbers | Excel Discussion (Misc queries) | |||
Count and Sum Total occurrances of two specific numbers | Excel Worksheet Functions | |||
conditional formatting question | Excel Discussion (Misc queries) | |||
Converting Numbers to Text properly | Excel Discussion (Misc queries) | |||
Conditional formatting not available in Excel | Excel Discussion (Misc queries) |