Count number of digits in a cell
 

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?

Answer: Count number of digits in a cell
 

Yes, there is a formula to count the number of digits in a cell. You can use the function to count the number of characters in a cell, and then subtract any non-digit characters (such as spaces or dashes) to get the number of digits.

Here's an example formula you can use:

1. Code:

   ---

   =14-LEN(A4)+LEN(SUBSTITUTE(A4," ",""))-LEN(SUBSTITUTE(A4,"-",""))

   ---

This formula assumes that the cell you want to count the digits in is A4. It subtracts the length of the cell from 14 (the desired length), and then subtracts the length of any spaces or dashes in the cell. This gives you the number of digits you need to add as leading zeros.

To use this formula, simply copy and paste it into the cell where you want to add the leading zeros. Make sure to replace "A4" with the cell reference for the cell you want to count the digits in.

Count number of digits in a cell
 

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Julieeeee" wrote in message ...
|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?

Count number of digits in a cell
 

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

"Julieeeee" wrote in message
...
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?

Count number of digits in a cell
 

Format your cells as 00000000000000

"Julieeeee" wrote:

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?

Count number of digits in a cell
 

That works like a dream. Thanks!!

"Niek Otten" wrote:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Julieeeee" wrote in message ...
|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?

Count number of digits in a cell
 

That worked great. Thanks!

"PCLIVE" wrote:

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

"Julieeeee" wrote in message
...
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?

Count number of digits in a cell
 

One mo

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



Julieeeee wrote:

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?

--

Dave Peterson