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

[Jan Buckley]

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,CONCATENATE("0000",LEFT(A1,3),RIGHT(A1,2)),IF(LEN(A1*100)=6,CONCATENATE("000",LEFT(A1,4),RIGHT(A1,2)),IF(LEN(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

 

[Niek Otten]

=TEXT(A1*100,"000000000")

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Peo Sjoblom]

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