How to make a number have a certain number of characters.

[Colin Hayes]

Hi All

I have a small problem.

I column A I have a row of cells , some of which have numbers , some of
which are empty.

The numbers usually have 12 or 13 chars.

Some have fewer than 12 chars. I need to make these up to 12 chars by
placing zeros at the start of each.

Cells with numbers already at 12 or 13 chars , or which are blank should
remain unaltered.


Can someone help with a formula to achieve this?

Thanks. Grateful for any advice.

 

[Ron Rosenfeld]

Hi All

I have a small problem.

I column A I have a row of cells , some of which have numbers , some of
which are empty.

The numbers usually have 12 or 13 chars.

Some have fewer than 12 chars. I need to make these up to 12 chars by
placing zeros at the start of each.

Cells with numbers already at 12 or 13 chars , or which are blank should
remain unaltered.


Can someone help with a formula to achieve this?

Thanks. Grateful for any advice.

You can custom format the cells: Type: 000000000000 (That's 12 0's)

 

[Cimjet]

Hi Colin
Select your cells, right click, format cell and select Custom the type 12
"0"zeros e.g. 000000000000
HTH
Cimjet

 

[Colin Hayes]

Hi Colin
Select your cells, right click, format cell and select Custom the type 12
"0"zeros e.g. 000000000000
HTH
Cimjet

Hi

OK Thanks.

I did try this , and it works fine on the screen.

I save it as tab delimited. When I re-open the file the zeros have all
disappeared again , and I'm back with my too-short numbers.

How can I make the zeros stick?


Thanks again.

 

[Cimjet]

Hi again
Start your custom code with a apostrophe. e.g. '000000000000
Excel will not accept zeros at the start of a number, the Apostrophe turn it in
to text
HTH
Cimjet

 

[David Biddulph]

Hi

OK Thanks.

I did try this , and it works fine on the screen.

I save it as tab delimited. When I re-open the file the zeros have all
disappeared again , and I'm back with my too-short numbers.

How can I make the zeros stick?

When you save as tab-delimited you are saving as a text file. That file
will include the leading zeroes. The problem arises if you use Excel to
open the text file using Excel's default options. If you want to import
the data from the text file, tell Excel at the import stage that the
relevant column is text, not General.

David Biddulph

 

[eliano]

Hi

OK Thanks.

I did try this , and it works fine on the screen.

I save it as tab delimited. When I re-open the file the zeros have all
disappeared again , and I'm back with my too-short numbers.

How can I make the zeros stick?

Thanks again.




- Mostra testo citato -

Hi Colin.
Try: =TEXT(A1,"0000000000000")
Regards
Eliano