Adding recognised Zeros

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

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.
 
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.
Click to expand...

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)
Click to expand...

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


Regards
Claus B.
 
Hi Colin,

Am Thu, 28 Nov 2013 18:26:45 +0100 schrieb Claus Busch:
=REPT(0,13-LEN(A2))&A2
Click to expand...

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


Regards
Claus B.
 
try:
=REPT(0,13-LEN(A2))&A2
Click to expand...

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
 
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?
Click to expand...

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


Regards
Claus B.
 
Claus Busch said:
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.
Click to expand...

Hi Claus

OK that's entirely perfect.

Thanks Claus.

^_^
 
Back
Top