2 Replies Latest reply: Jul 7, 2016 8:19 AM by kajbw _ RSS

    Preserving Number format (comma's) with STR function

    kajbw _

      Does anyone know a way to preserve the commas associated with a longer number display when converting to a string?

       

      Example: I have long numbers 987,123,456,789.123

                                                   123,456,780.1

                                                   987,654.23

       

      that I need to export as a single cell (separated by 2 spaces)- There can be 5 of these numbers in a single export cell

           Example: [987,123,456,789.123  123,456,780.1  987,654.23]

       

      When converting with STR(), the commas are removed

       

      Only way I could think of was to convert the original number to charachter, and build the final string with Right and Left selections and adding the commas as part of it

      Left(LongNumber, 3) + ',', + left(right(longnumber,12),3) +',' + etc.

       

      With floating number lengths- these seemed like a poor way to accomplish this. Any other ideas? I could not find any format masking for string or number values

       

      Thanks

        • Re: Preserving Number format (comma's) with STR function
          Olly Bond

          Hello Kajbw,

           

          There isn't a formatting trick in one model, unless you define the numeric field as thousands, export it as a fixed width text file, and open that up as a new report and trap it as a character field. But that's too much like hard work.

           

          You can do it though, using Len, Substr, and Str, as long as the number of decimals is consistent. Substr([String];4;3) returns positions 4,5 and 6 of a string - easier than nesting lots of Left() and Right() functions. Str([Number];decimals;length;fill character) will turn 123.45 into "0000123.45" with a length of 10 and 2 decimals and "0" as the fill. I think I'd use "x" or something non-numeric as the fill, in order to be able to use Replace() at the end when I want to clean up the output. Replace ("xx3,456.78";"x";"") is easy, but you'd want to make sure "x,987,654.32" didn't end up with a leading comma, but that can be handled using a nested Strip(Replace ([Ouput];"x,";"x");"x").

           

          The easiest way to make [Output] is just to copy and paste, making sure that you've converted the number to the right length of x's:

           

          substr([ConvertedNumberWithFill];1;3)+","+

          substr([ConvertedNumberWithFill];4;3)+","+

          substr([ConvertedNumberWithFill];7;3)+","+

          ...

           

          Hope this helps,

           

          Olly