Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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,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 very much. Jan Buckley |
#2
|
|||
|
|||
=TEXT(A1*100,"000000000")
-- Kind Regards, Niek Otten Microsoft MVP - Excel "Jan Buckley" wrote in message ... 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,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 very much. Jan Buckley |
#3
|
|||
|
|||
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,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 very much. Jan Buckley |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|