Count number of digits in a cell

[Guest]

I have a column of numbers that all need to be 14 digits long. So I need to
add leading zeros... Currently I am doing =00&A4 and that would be great if
they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10, and
11 digits. Is there a formula to count the number of digits in the cell?

 

[Niek Otten]

=REPT("0",14-LEN(A1))&A1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have a column of numbers that all need to be 14 digits long. So I need to
| add leading zeros... Currently I am doing =00&A4 and that would be great if
| they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10, and
| 11 digits. Is there a formula to count the number of digits in the cell?

 

[PCLIVE]

This returns the number of characters, including spaces, in A1.
=LEN(A1)

This will add the appropriate number of leading zeros.
=REPT(0,14-LEN(A1))&A1

HTH,
Paul

 

[Guest]

Format your cells as 00000000000000

 

[Guest]

That works like a dream. Thanks!!

 

[Guest]

That worked great. Thanks!

This returns the number of characters, including spaces, in A1.
=LEN(A1)

This will add the appropriate number of leading zeros.
=REPT(0,14-LEN(A1))&A1

HTH,
Paul

 

[Dave Peterson]

One more:

=RIGHT(REPT("0",14)&A1,14)

I have a column of numbers that all need to be 14 digits long. So I need to
add leading zeros... Currently I am doing =00&A4 and that would be great if
they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10, and
11 digits. Is there a formula to count the number of digits in the cell?