Welcome Guest
Log In | Register )
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
2.52
PrintE-mailLink
KnuckleHead's avatar - box

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

    JADELottery's avatar - MeAtWork 03.PNG
    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.
    Use at your own risk.

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

      KnuckleHead's avatar - box

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

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

        JADELottery's avatar - MeAtWork 03.PNG
        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.
        Use at your own risk.

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