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

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

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

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

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

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

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