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

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

    Re: ToNumber() function - Number Format String

    ‏2012-10-03T03:04:21Z  
    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

    Re: ToNumber() function - Number Format String

    ‏2012-10-03T06:41:20Z  
    • JasonJava
    • ‏2012-10-03T03:04:21Z
    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) )
    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

    Re: ToNumber() function - Number Format String

    ‏2012-10-03T20:55:53Z  
    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
    Thanks Laurent and the explanation. I will use that then.
  • JasonJava
    JasonJava
    19 Posts

    Re: ToNumber() function - Number Format String

    ‏2012-10-04T03:15:52Z  
    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
    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

    Re: ToNumber() function - Number Format String

    ‏2012-10-04T06:42:00Z  
    • JasonJava
    • ‏2012-10-04T03:15:52Z
    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.
    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

    Re: ToNumber() function - Number Format String

    ‏2012-10-04T14:21:29Z  
    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)
    Thanks Laurent. That is good enough, appreciate for the help.