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

J

Jan Buckley

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

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

Similar Threads

converting numbers to text and prefill text field with 0's 2
Change a 1,2,3 or 4 digit number to a 6 character text string 3
Numaric to Inword in excel by VB 1
convert figures into words on Microsft Excel 4
Display Numbers as Words 3
calculating elapsed time and speed 4
How do I put a blank value in a cell not using "" in a formula 2
Currency to Text 1

Top