7 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Change decimal separator

    Igor Colruyt

      Hi,

       

      I have a report with "," as decimal separator for invoice amounts but I want to have a "." as decimal separator without changing my regional settings...is there formula to change this?

        • Change decimal separator
          Data Kruncher

          Welcome to the forum Igor.

           

          I would first recommend defining your numeric field as a Character type field instead of numeric, and then building a new calculated field in the Table. Give the new field this formula:

           

          [SIZE=2]Val(Replace(Strip(CharNum,"."),",","."))[/SIZE][/CODE]

           

          This will first remove any decimal points (should your number be presented similar to 3.123,45) and then it will replace the comma with a decimal point, and finally convert the whole string to a numeric value.

           

          HTH,

          Kruncher

            • Change decimal separator
              Igor Colruyt

              Thank you for your time but when inserting the formula it's putting back the "," instead of "."

              Is there a way to work around this?

                • Change decimal separator
                  Olly Bond

                  Hello Igor,

                   

                  That might be because the Val() is turning it back to a number, where your regional settings will override it and show the "," as a separator.

                   

                  Try without the val() or experiment with the str() function to leave it as a string.

                   

                  Best wishes,

                   

                  Olly

                    • Change decimal separator
                      Igor Colruyt

                      Hi Olly,

                      I've tried your suggestions but I'm afraid I will have to keep the formatting excel.

                      Rgds,

                       

                      Igor

                        • Change decimal separator
                          Olly Bond

                          Hello Igor,

                           

                          I think that Excel and Monarch will both want to use the same Regional Settings stored in Windows for displaying numbers, so this looks like it might be a bit tricky.

                           

                          Let me get it right - you have a numerical field of data in Monarch showing as 123.456,78, and you want to show it as 123,456.78, but still with it being treated as a number? And you can't alter your regional settings in Windows to achieve it?

                           

                          Best wishes,

                           

                          Olly

                            • Change decimal separator
                              Igor Colruyt

                              Hi Olly,

                              I can change my regional settings but since I'm using 3 different ERP's I'm afraid this will cause a chain-reaction of other problems...

                                • Change decimal separator
                                  Olly Bond

                                  Hello Igor,

                                   

                                  I don't see a way around the Windows Regional Settings issue but perhaps the data could be collected into a common format before being exported to the ERPs. Most ERPs would accept CSV input, but of course the servers for these will want "," or "." depending on their configuration.

                                   

                                  Will the ERPs import from MDB or another database format?

                                   

                                  Best wishes,

                                   

                                  Olly