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,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

=TEXT(A1*100,"000000000")

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Jan Buckley" <JanBuckley@discussions.microsoft.com> wrote in message
news:79E3D4DC-0399-4B4A-B76C-3F0A610699B5@microsoft.com...
>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

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,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