Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting numbers to text and prefill text field with 0's | Excel Discussion (Misc queries) |