converting numbers to text and prefill text field with 0's
 

I have a column of dollars and cents (445.92) that I need to convert to text
and prefill with 0's to a width of 9 characters and no decimal (000044592). I
wrote a formula (see below) that works except with figures like 0.08 where
there are no dollars, or 4.70 where the 2nd character to the right of the
decimal is a zero. Using the formula below, these figures come out like this:
0.08 converts to 00.0808 and 4.70 converts to 0000047.0. Can you help?
=IF(LEN(A1*100)=2,CONCATENATE("0000000",RIGHT(A1,2 )),IF(LEN(A1*100)=3,CONCATENATE("000000",LEFT(A1,1 ),RIGHT(A1,2)),IF(LEN(A1*100)=4,CONCATENATE("00000 ",LEFT(A1,2),RIGHT(A1,2)),IF(LEN(A1*100)=5,CONCATE NATE("0000",LEFT(A1,3),RIGHT(A1,2)),IF(LEN(A1*100) =6,CONCATENATE("000",LEFT(A1,4),RIGHT(A1,2)),IF(LE N(A1*100)=7,CONCATENATE("00",LEFT(A1,5),RIGHT(A1,2 )),CONCATENATE("0",LEFT(A1,6),RIGHT(A1,2))))))))
Thank you very much. Jan Buckley

=TEXT(A1*100,"000000000")

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Jan Buckley" wrote in message
...
I have a column of dollars and cents (445.92) that I need to convert to
text
and prefill with 0's to a width of 9 characters and no decimal
(000044592). I
wrote a formula (see below) that works except with figures like 0.08 where
there are no dollars, or 4.70 where the 2nd character to the right of the
decimal is a zero. Using the formula below, these figures come out like
this:
0.08 converts to 00.0808 and 4.70 converts to 0000047.0. Can you help?
=IF(LEN(A1*100)=2,CONCATENATE("0000000",RIGHT(A1,2 )),IF(LEN(A1*100)=3,CONCATENATE("000000",LEFT(A1,1 ),RIGHT(A1,2)),IF(LEN(A1*100)=4,CONCATENATE("00000 ",LEFT(A1,2),RIGHT(A1,2)),IF(LEN(A1*100)=5,CONCATE NATE("0000",LEFT(A1,3),RIGHT(A1,2)),IF(LEN(A1*100) =6,CONCATENATE("000",LEFT(A1,4),RIGHT(A1,2)),IF(LE N(A1*100)=7,CONCATENATE("00",LEFT(A1,5),RIGHT(A1,2 )),CONCATENATE("0",LEFT(A1,6),RIGHT(A1,2))))))))
Thank you very much. Jan Buckley

Assuming that a value like 1100 should be 000110000 and 0.08 should be
000000008 if so you can use

=TEXT(A1*100,"000000000")

if not I don't see the logic


Regards,

Peo Sjoblom




"Jan Buckley" wrote:

I have a column of dollars and cents (445.92) that I need to convert to text
and prefill with 0's to a width of 9 characters and no decimal (000044592). I
wrote a formula (see below) that works except with figures like 0.08 where
there are no dollars, or 4.70 where the 2nd character to the right of the
decimal is a zero. Using the formula below, these figures come out like this:
0.08 converts to 00.0808 and 4.70 converts to 0000047.0. Can you help?
=IF(LEN(A1*100)=2,CONCATENATE("0000000",RIGHT(A1,2 )),IF(LEN(A1*100)=3,CONCATENATE("000000",LEFT(A1,1 ),RIGHT(A1,2)),IF(LEN(A1*100)=4,CONCATENATE("00000 ",LEFT(A1,2),RIGHT(A1,2)),IF(LEN(A1*100)=5,CONCATE NATE("0000",LEFT(A1,3),RIGHT(A1,2)),IF(LEN(A1*100) =6,CONCATENATE("000",LEFT(A1,4),RIGHT(A1,2)),IF(LE N(A1*100)=7,CONCATENATE("00",LEFT(A1,5),RIGHT(A1,2 )),CONCATENATE("0",LEFT(A1,6),RIGHT(A1,2))))))))
Thank you very much. Jan Buckley