Help in converting text(?) to numbers.

Reply #3

JADELottery — Mon, 04 Jan 2010 01:09:08 GMT

000000000011111111112222222222333333333

012345678901234567890123456789012345678

'This is some text you are working with.'

@RIGHT(XXX, 9) -- 'ing with.'

@LEFT(XXX, 9) -- 'This is s'

@MID(XXX, 9, 9) -- 'ome text '

@LENGTH(XXX) -- 39

XXX is a cell where the text is located.

@VALUE(XXX) will return a value of 0 if there are any other characters than the following: + - . 0 1 2 3 4 5 6 7 8 9

@VALUE( 3.14 ) -- 3.14

@VALUE( -0.2345 ) -- -0.2345

@VALUE( 2.01M ) -- 0 (beca... [ More ]

Reply #2

KnuckleHead — Sun, 03 Jan 2010 19:54:23 GMT

Afternoon JADELottery,

I don't yet understand how your formula works, but it's exactly what I was trying to create. I've tested it on several different amounts and it's worked on all of them. Thank you very much.

Ok, I understand the @Right and I was thinking that the @Value was gonna be needed to convert the text numbers to values (wasn't really sure though), but I didn't comprehend the @Mid or the @Length. I'm assuming that the @Mid and @Length takes out the space between the numbers and... [ More ]

Reply #1

JADELottery — Sun, 03 Jan 2010 02:17:55 GMT

Try this:

@IF(@RIGHT(C1,1)= K ,1000*@VALUE(@MID(C1,0,@LENGTH(C1)-1)),@IF(@RIGHT(C1,1)= M ,1000000*@VALUE(@MID(C1,0,@LENGTH(C1)-1)), ))

a;lsjfqegaiobv bibipgvqiv kljviviu daoibiwu bviud nbvndbin diubn ipuvniasdnviupadn bvipndivndfkbnipusfnbipsbniupnsbf

Help in converting text(?) to numbers.

KnuckleHead — Sat, 02 Jan 2010 22:39:09 GMT

Afternoon all,

While updating my files, I tend to us short hand, ie. 200 K or 2.45 M or 29 M to represent amounts. All of these entries are done manually. I'm attempting to automate this entry process and have the new amounts added automaticlly.

I've figured out the formula to identify the K or M , but the multiply section isn't working correctly. It just gives me zeros. Below is what I'm trying to get to work. (I think the original entry is entered as text and the space letter need to be... [ More ]