Adding recognised Zeros

[Colin Hayes]

Hi All

I need to add zeros to the beginning of numbers of less than 13 digits.

I've been using the custom format method 0000000000000 , but find it
'forgets' the zeros when counting the length of the cell contents. This
is causing errors.

I'm now using

=IF(LEN(A2)=12,("0"&A2),A2)

This correctly produces a cell which has a zero added when the length is
12 , and then correctly shows 13 digits when the length is queried.

Can someone suggest an amended formula to add 2 zeros when length is
11 , 3 zeros when length is 10 , 4 zeros when length is 9 and 5 zeroes
when length is 8?

Trying to work these into a single formula is beyond me , but I'm hoping
someone can help.

Grateful for any advice.

 

[Claus Busch]

Hi Colin,

Am Thu, 28 Nov 2013 17:15:25 +0000 schrieb Colin Hayes:

I've been using the custom format method 0000000000000 , but find it
'forgets' the zeros when counting the length of the cell contents. This
is causing errors.

why does this not working for you? Or do you need really 13 digits
number and not only the format?

I'm now using

=IF(LEN(A2)=12,("0"&A2),A2)

try:
=REPT(0,13-LEN(A2))&A2


Regards
Claus B.

 

[Claus Busch]

Hi Colin,

Am Thu, 28 Nov 2013 18:26:45 +0100 schrieb Claus Busch:

=REPT(0,13-LEN(A2))&A2

or try:
=TEXT(A2;"0000000000000")
Copy the column with the formulas and paste it back as values


Regards
Claus B.

 

[Colin Hayes]

try:

=REPT(0,13-LEN(A2))&A2

Hi Claus

OK Thanks again. This works perfectly. I do need 13 solid digit numbers
and not just format.

BTW I find I have a couple of 14 digit numbers in my column which give
#VALUE! Errors. Is there a way to ignore these numbers when running the
REPT formula?


Best Wishes

 

[Claus Busch]

Hi Colin,

Am Thu, 28 Nov 2013 19:13:23 +0000 schrieb Colin Hayes:

BTW I find I have a couple of 14 digit numbers in my column which give
#VALUE! Errors. Is there a way to ignore these numbers when running the
REPT formula?

try:
=REPT(0,MAX(LEN(A2),13)-LEN(A2))&A2


Regards
Claus B.

 

[Colin Hayes]

Hi Colin,

Am Thu, 28 Nov 2013 19:13:23 +0000 schrieb Colin Hayes:


try:
=REPT(0,MAX(LEN(A2),13)-LEN(A2))&A2


Regards
Claus B.

Hi Claus

OK that's entirely perfect.

Thanks Claus.

^_^