Adding recognised Zeros

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.
 
C

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.
 
C

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.
 
C

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
 
C

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.
 
C

Colin Hayes

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.

Hi Claus

OK that's entirely perfect.

Thanks Claus.

^_^
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top