How do I convert dollars and cents to text, and prefill the cell .
 

I need to convert a column that contains dollars and cents (559.42) to text
and prefil the text field to 9 characters (000055942). The numbers are
varying lengths. I wrote a formula that works except when the number ends in
a 0, (.70), or the cents begin with 0 (.05). Can you help? Here's the rather
unweildy formula I wrote:

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

Try:

=TEXT(A1*100,"000000000")

Tim C

"Jan Buckley" wrote in message
...
I need to convert a column that contains dollars and cents (559.42) to text
and prefil the text field to 9 characters (000055942). The numbers are
varying lengths. I wrote a formula that works except when the number ends
in
a 0, (.70), or the cents begin with 0 (.05). Can you help? Here's the
rather
unweildy formula I wrote:

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