Forum Discussion

ArunkumarK's avatar
ArunkumarK
Icon for Helper I rankHelper I
7 years ago
Solved

Thousand Separtors

Hello Everyone, I needs to convert the number into US thousand separtors like below. How to conver the number into highlighed formats like below.   Available Number: 125000 Available Format: 1,25...
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Arun,

     

    For me it defaults to the US version when I select the field and select comma in modeling. If your default is diferent, you could perhaps force it using Format command such as 

    newdata = FORMAT(data[Column1],"#,0.")
     

     

ArunkumarK's avatar
ArunkumarK
Icon for Helper I rankHelper I
7 years ago

Nice. Format formula is working fine.

 

Thanks you so much.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

You are welcome.  Glad it worked for you.

  • ArunkumarK's avatar
    ArunkumarK
    Icon for Helper I rankHelper I
    7 years ago

    Hi,

    The idea you suggested is working but it creates another problem. When using the format function the number is converted into text. When i tried value(Format([col]),"#,0") then the thousand separator disapper. Something found  but not the output as expected.

     

    Buzz me if you have any idea.

     

    Thanks in advance.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Arun,

      when I used the value function to cast the text as a number, it did keep the commas but added decimal places.  

      newdata = value(FORMAT(data[Column1],"#,0."))  
       
      I also changed the original column to a text field and the 'newdata' still came out formatted correctly ( but with decimals).

       

      • ArunkumarK's avatar
        ArunkumarK
        Icon for Helper I rankHelper I
        7 years ago

        I assume my words make you confused. Even I get the answer same what you got but when you sum the column values it wont come because of it converted into text.

         

        E.g If a column have multiple rows with data and we formated in such a way then in the report section if you try to sum, it wont allow except count.

         

        Hope make sense.