Topic
6 replies Latest Post - ‏2012-10-04T14:21:29Z by JasonJava
JasonJava
JasonJava
19 Posts
ACCEPTED ANSWER

Pinned topic ToNumber() function - Number Format String

‏2012-10-01T22:49:05Z |
I need to convert various number (in character) to another format but not familiar with Number Format String. Appreciate any help .

Input format is 7 positions with 3 positions to the right of the (implied) decimal point (9999.999)
Output format - something like ZZZ9.9ZZ, minimum 1 whole number, zero supression.

1)0004200 -- > 4.2
2)0009010 -- > 9.01
3)0070000 -- > 70
4)0000060 -- > 0.06
5)2345430 -- > 2345.43
Updated on 2012-10-04T14:21:29Z at 2012-10-04T14:21:29Z by JasonJava
  • JasonJava
    JasonJava
    19 Posts
    ACCEPTED ANSWER

    Re: ToNumber() function - Number Format String

    ‏2012-10-03T03:04:21Z  in response to JasonJava
    Finally, I just use simple math and string function to get what I need.

    = IF(FIND(".", NUMBERTOTEXT(TONUMBER(In1)/1000))=1 , "0"+NUMBERTOTEXT(TONUMBER(In1)/1000), NUMBERTOTEXT(TONUMBER(In1)/1000) )
    • SystemAdmin
      SystemAdmin
      6184 Posts
      ACCEPTED ANSWER

      Re: ToNumber() function - Number Format String

      ‏2012-10-03T06:41:20Z  in response to JasonJava
      I'd rather use FROMNUMBER to do the same, the format strings are documented at the end of the "functions and expressions manual".

      The srings are in the form of {1####4.1###3}

      in this case, 1 to 4 digits for the whole part, 1 to 3 for the decimal part, period as decimal separator and no thousands separator.

      Hope this helps.

      Laurent
      • JasonJava
        JasonJava
        19 Posts
        ACCEPTED ANSWER

        Re: ToNumber() function - Number Format String

        ‏2012-10-03T20:55:53Z  in response to SystemAdmin
        Thanks Laurent and the explanation. I will use that then.
      • JasonJava
        JasonJava
        19 Posts
        ACCEPTED ANSWER

        Re: ToNumber() function - Number Format String

        ‏2012-10-04T03:15:52Z  in response to SystemAdmin
        Laurent,

        I tried your suggestion. Not sure if I did it correctly. The results are unexpected like the following in the output.txt. I want to use the way you suggested with the number format string instead of my own string manipulation method. However, I just have trouble getting the result that I want.

        1)0004200 -- > 4200
        2)0009010 -- > 9010
        3)0070000 -- >
        4)0000060 -- > 60
        5)2345430 -- >

        I attached my test zip file. Appreciate your further suggestion.
        • SystemAdmin
          SystemAdmin
          6184 Posts
          ACCEPTED ANSWER

          Re: ToNumber() function - Number Format String

          ‏2012-10-04T06:42:00Z  in response to JasonJava
          depends on what you mean by doing it correctly ;-)

          You did what I suggested, but I made a mistake.

          The correct format string is "{1####4'.'1##2}" (note the apostrophes around the decimal separator). I ALWAYS forgot this ****** detail.

          There's another mistake : your input number is not an integer but a decimal with 2 implied decimals. You need to either change this in the type tree, or divide it by 100 when passing it to the fmap.

          There remains one mystery (at least on my environment) : it only maps the first 5 numbers, and I dunno why !! I'm lacking time to look at this at the moment, so I'll leave it to you.

          Attached is the project interchange (WTX8.4.0.3)
          • JasonJava
            JasonJava
            19 Posts
            ACCEPTED ANSWER

            Re: ToNumber() function - Number Format String

            ‏2012-10-04T14:21:29Z  in response to SystemAdmin
            Thanks Laurent. That is good enough, appreciate for the help.