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

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.

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

On Thu, 15 Sep 2011 00:45:51 +0100, Colin Hayes wrote:



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)

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

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


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.

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

In article , Cimjet
writes
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.




"Colin Hayes" wrote in message
...


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.

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

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
"Colin Hayes" wrote in message
...
In article , Cimjet writes
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.




"Colin Hayes" wrote in message
...


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.

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

On 15/09/2011 01:18, Colin Hayes wrote:
In article , Cimjet
writes
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?

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

"Colin Hayes" wrote in message
...


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.

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

On 15 Set, 02:18, Colin Hayes wrote:
In article , Cimjet
writes

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.



"Colin Hayes" wrote in message
...

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.- Nascondi testo citato

- Mostra testo citato -

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