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

C

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.
 
R

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)
 
C

Cimjet

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

Colin Hayes

Cimjet <[email protected]> said:
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.
 
C

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
 
D

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
 
E

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top