Welcome Guest
You last visited December 9, 2016, 4:15 am
All times shown are
Eastern Time (GMT-5:00)

Help in converting text(?) to numbers.

Topic closed. 3 replies. Last post 7 years ago by JADELottery.

 Page 1 of 1

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: January 2, 2010, 5:39 pm - IP Logged

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 seperated so that the numbers can become values and multipled properly.)

C1 contains 200 K

C4 contains the formula: @IF(@RIGHT(C1,1)="K",C1*1000,@IF(@RIGHT(C1,1)="M",C1*1000+C1*1000000," "))     [please note that Lotus uses the "@" sign in formulas instead of the "=" sign that Excel uses]

The results in C4 should be "\$200,000.00", instead it displays "\$0.00".

D1 contains 2.45 M - result in D4 should be \$2,450,000.00

E1 contains 29 M - result in E4 should be \$29,000,000.00

All results in row 4 are the same using the above formula.

Would someone please explain what I'm doing wrong so that the amounts come up correctly?

Thank you for any assistance ahead of time.

The only DUMB question is the one question you DID NOT ask...

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3675 Posts
Offline
 Posted: January 2, 2010, 9:17 pm - IP Logged

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

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.

Order is a Subset of Chaos
Knowledge is Beyond Belief
Wisdom is Not Censored
Douglas Paul Smallish
Jehocifer

United States
Member #73037
April 3, 2009
147 Posts
Offline
 Posted: January 3, 2010, 2:54 pm - IP Logged

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 the letter so that the numbers become multiplyable again?

I see now that I've got to do a lot more reading and understanding in the Help files....

Thank you very much again.

The only DUMB question is the one question you DID NOT ask...

The Quantum Master
West Concord, MN
United States
Member #21
December 7, 2001
3675 Posts
Offline
 Posted: January 3, 2010, 8:09 pm - IP Logged

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 (because it has the M character)

Presented 'AS IS' and for Entertainment Purposes Only.
Any gain or loss is your responsibility.