17 Replies Latest reply on Sep 15, 2011 3:17 AM by Jaime de Vos

    Rounding prices

    Jaime de Vos Level 1

      First posted this in the regular InDesign forum, but after some deliberation and guidance from other posters there, think this subforum is more suited for the question.

       

      I'm currently working on a price list for our sales organisation, and they've given me prices that are formatted as shown below in the examples. But now they want to change it.

       

      What  I need to do is find two decimal numbers and round up the  price, or when the decimal numbers are ",00", then I merely need to  remove the ",00". I'm breaking my head over this and scripting makes my head spin.

       

      Is there a script that can help  me out with this, because I really shudder at the thought of having the  redo all those prices.

       

      Example:

      € 10.762,50

      € 11.812,50

       

      Need to look like this;

       

      € 10.763

      € 11.813


      Alternatively, numbers that are formatted like this,

       

      € 10.300,00

       

      Need to look like this;

       

      € 10.300

       

      I hope I explained my problem in a clear manner. If I haven't, please don't hesitate to let me know!

      Thanks in advance, everyone.

        • 1. Re: Rounding prices
          Manan Joshi Level 4

          You could use the following script

           

          var inputNo = "€ 11.812,50"

          var PIndex = inputNo.indexOf(".")

          var roundOffNo = inputNo.substring(0, PIndex + 1) + Math.round(inputNo.replace(",", ".").substring(PIndex + 1))

           

          where "inputNo" is your original number and "roundOffNo" is the result.

           

          Hope this solves your problem

           

          Manan Joshi

           

          - Efficient InDesign Solutions -

          MetaDesign Solutions

          http://metadesignsolutions.com/services/indesign-development.php

          • 2. Re: Rounding prices
            Laubender Adobe Community Professional & MVP

            Manan,

            I wonder what will happen to a input like: € 999.999,50 ?
            We would get € 1000.000 instead of € 1.000.000 .

             

            So I would suggest a function that first strips out every dot and after rounding adds the sufficient number of dots like that:

             

            function _roundPrice(foundString){
                var _Array = new Array();
                //Strip every dot from the string:
                var _string = foundString.replace(/\./g,"").replace(/,/g,".");
                //Strip the € sign from the array and round the number:
                var _newString = Math.round(_string.replace(/€\s/,"")).toString();
                
                //Push every single character to the array:
                for(var n=0;n<_newString.length;n++){
                    _Array.push(_newString.charAt(n));
                    };
                
                //Insert dots every 3 characters from end:
                for(var n=_Array.length-4;n>=0;n=n-3){
                    _Array[n] = _Array[n]+".";
                    };
                
                //Add the € sign plus a blank:
                return "€ "+_Array.join("");
                };
            

             

             

            Now, as we have that function we need a method to search/replace the prices in the document.
            If the prices strictly are formatted with a character style one can do it with the following code (given the applied character style is named "Price"):

             

            var _d=app.activeDocument;
            //Reset the Text search input field:
            app.changeTextPreferences = app.findTextPreferences = null;
            
            //Find all text applied with the character style named "Price":
            app.findTextPreferences.appliedCharacterStyle = "Price";
            //Built array of the found texts:
            var _foundText = _d.findText();
            
            //IMPORTANT: Loop from back to forth:
            for(var n=_foundText.length-1;n>=0;n--){
                //Set the contents of the found texts to the returned strings of the function _roundPrice:
                _foundText[n].contents =  _roundPrice(_foundText[n].contents);
                };
            //Reset the Text search input field:
            app.changeTextPreferences = app.findTextPreferences = null;
            

             

            BUT SEE THIS WARNING: if a single white space around the price is erroneously formatted with "Price" the function will not work right.

             

            Another method could be a GREP search, though I'm not sure exactly what the right expression will be to get all possible instances of the prices like:

            € 1.000.000.000,56 (multiple dots)

            € 197,56 (no dots at all)


            One expression would be:
            €\\s+\\d+\\.?\\d+\\.?\\d+,?\\d+
            which neglects cases without dots and is limited to maximum two dots:

             

            var _d=app.activeDocument;
            app.changeGrepPreferences = app.findGrepPreferences = null;
            app.findGrepPreferences.findWhat = "€\\s+\\d+\\.?\\d+\\.?\\d+,?\\d+";
            var _foundText = _d.findGrep();
            
            for(var n=_foundText.length-1;n>=0;n--){
                _foundText[n].contents =  _roundPrice(_foundText[n].contents);
                };
            app.changeGrepPreferences = app.findGrepPreferences = null;
            

             

            So, maybe it's up to the original poster to come up with a better expression or doing two search/replace scripts with a slightly altered GREP.

             

            Uwe

            • 3. Re: Rounding prices
              Manan Joshi Level 4

              Laubender,

               

              My code had one glicth, also i had written it on the suppostion that the input no will have only one dot and one comma the question also intends this i think.

               

              So my corrected code would be

               

              var inputNo = "999.999,50"

              var PIndex = inputNo.indexOf(".")

              var Cindex = inputNo.indexOf(",")

              var len = inputNo.substring(PIndex + 1, Cindex).length;

              var lenAfterRounding = String(Math.round(inputNo.replace(",", ".").substring(PIndex + 1))).length;

              var roundOffNo;

              if(lenAfterRounding > len)

              roundOffNo = inputNo.substring(0, PIndex ) * 1 + 1;

              else

              roundOffNo = inputNo.substring(0, PIndex + 1) + Math.round(inputNo.replace(",", ".").substring(PIndex + 1))

               

              In this snippet i have handled the situation when the input no is something like 999.999,50 where rounding off would also effect the number before the decimal.

              I have added 1 to the number before decimal in case the rounding of the number after decimal causes a carry over.

               

               

              Manan Joshi

               

              - Efficient InDesign Solutions -

              MetaDesign Solutions

              http://metadesignsolutions.com/services/indesign-development.php

              • 4. Re: Rounding prices
                Laubender Adobe Community Professional & MVP

                Manan,

                thank you for your new code.
                However, if I take your example of:

                 

                var inputNo = "999.999,50";

                 

                I will get a result by:

                 

                $.writeln(roundOffNo);
                

                 

                in the console of the ESTK of:

                 

                1000

                 

                and not of

                 

                1.000.000
                

                 

                Everything goes right for numbers in the range of 0,00 to 999,49.

                 

                 

                Uwe

                • 5. Re: Rounding prices
                  Manan Joshi Level 4

                  Ok, i thought the user was using a custom number format in which we could have only a single point and comma. I was not aware of the numbering system using . and , together. I suppose . here is used to seperate thousands and , as a decimal point. If this is the case then my code won't work as pointed out by you correctly.

                   

                  I apolozise for the misunderstanding.

                   

                  Manan Joshi

                  www.metadesignsolutions.com

                  • 6. Re: Rounding prices
                    Jaime de Vos Level 1

                    Thanks everyone, for the massive amounts of response.

                     

                    I'm going to give all those scripts a go and let you know the results.

                    • 7. Re: Rounding prices
                      Jaime de Vos Level 1

                      function _roundrechtslijnend(foundString){
                          var _Array = new Array();
                          //Strip every dot from the string:
                          var _string = foundString.replace(/\./g,"").replace(/,/g,".");
                          //Strip the € sign from the array and round the number:
                          var _newString = Math.round(_string.replace(/€\s/,"")).toString();
                         
                          //Push every single character to the array:
                          for(var n=0;n<_newString.length;n++){
                              _Array.push(_newString.charAt(n));
                              };
                         
                          //Insert dots every 3 characters from end:
                          for(var n=_Array.length-4;n>=0;n=n-3){
                              _Array[n] = _Array[n]+".";
                              };
                         
                          //Add the € sign plus a blank:
                          return "€ "+_Array.join("");
                          };

                      var _d=app.activeDocument;
                      //Reset the Text search input field:
                      app.changeTextPreferences = app.findTextPreferences = null;

                      //Find all text applied with the character style named "rechtslijnend":
                      app.findTextPreferences.appliedCharacterStyle = "rechtslijnend";
                      //Built array of the found texts:
                      var _foundText = _d.findText();

                      //IMPORTANT: Loop from back to forth:
                      for(var n=_foundText.length-1;n>=0;n--){
                          //Set the contents of the found texts to the returned strings of the function _roundrechtslijnend:
                          _foundText[n].contents =  _roundrechtslijnend(_foundText[n].contents);
                          };
                      //Reset the Text search input field:
                      app.changeTextPreferences = app.findTextPreferences = null;

                       

                       

                      This script works. Infact, in my tests it even adds Euro signs to prices that did not have any yet! The only thing that might cause a problem for the sales people, is that it rounds both up and down. I think that they, them being sales guys, might opt for the round up only version.

                       

                      Can that be specified with? (going out on a limb here);

                       

                      Math.roundUP
                      
                      • 8. Re: Rounding prices
                        Peter Kahrel Adobe Community Professional & MVP

                        This function rounds up numbers with decimal commas and dots for thousands separators:

                         


                        function roundPrice (num)
                            {
                            // Remove thousand separators
                            num = num.replace (/\./g, "");


                            // Replace decimal comma with a dot
                            num = num.replace (/,(?=\d\d$)/, ".");

                            // Round the number up to the nearest integer
                            num = Math.round (Number (num));


                            // Restore thousand separators
                            num = String(num).replace(/(\d)(?=(\d\d\d)+$)/g, "$1.");
                            return num;
                            }

                         

                        Uwe -- I think this grep finds numbers following Euro symbols:

                         

                        (?<=€\s)[.\d]+,\d\d

                         

                        Your expression allows some flexibility in the number of spaces between the Euro symbol and the first digit, but on the other hand, you could say that a finished text should have just one space there. And with the lookbehind you match just the number, which makes processing the money amounts a bit easier.

                         

                        Peter

                        • 9. Re: Rounding prices
                          Jaime de Vos Level 1

                          Not a single double space in the document, thanks to the wonders of Grep.


                          Thanks, I will try this out.

                          • 10. Re: Rounding prices
                            Laubender Adobe Community Professional & MVP

                            @Peter:

                            Thank you for commenting!
                            And, of course, your roundPrice() function.

                             

                            @Jaime:
                            it could have been another white space character like an En Dash, a Tab or something:
                            \s does find them all.

                             

                            So try it out and let us know how it works…

                             

                            Uwe

                            • 11. Re: Rounding prices
                              Jaime de Vos Level 1

                              Thank you, Peter.

                               

                              Lau, Peter;

                               

                              Struggling to combine both scripts. It now looks like this;

                               

                              function roundPrice (num)
                                  {
                                  // Remove thousand separators
                                  num = num.replace (/\./g, "");
                              
                              
                                  // Replace decimal comma with a dot
                                  num = num.replace (/,(?=\d\d$)/, ".");
                              
                                  // Round the number up to the nearest integer
                                  num = Math.round (Number (num));
                              
                                  // Restore thousand separators
                                  num = String(num).replace(/(\d)(?=(\d\d\d)+$)/g, "$1.");
                                  return num;
                                  }
                              
                              var _d=app.activeDocument;
                              //Reset the Text search input field:
                              app.changeTextPreferences = app.findTextPreferences = null;
                               
                              //Find all text applied with the character style named "Price":
                              app.findTextPreferences.appliedCharacterStyle = "Price";
                              //Built array of the found texts:
                              var _foundText = _d.findText();
                               
                              //IMPORTANT: Loop from back to forth:
                              for(var n=_foundText.length-1;n>=0;n--){
                                  //Set the contents of the found texts to the returned strings of the function _roundPrice:
                                  _foundText[n].contents =  _roundPrice(_foundText[n].contents);
                                  };
                              //Reset the Text search input field:
                              app.changeTextPreferences = app.findTextPreferences = null;
                              

                               

                               

                              Yet it generates the following error. I'm confident that your scripting works and that the error lies with my implementation.

                               

                              (fictional prices)

                              screen.png

                              • 12. Re: Rounding prices
                                Peter Kahrel Adobe Community Professional & MVP

                                You need to change two things:

                                 

                                1. The function's name is roundPrice but you call it as _roundPrice.
                                2. Use a GREP search, not a Text search:

                                 

                                app.findGrepPreferences = null;
                                app.findGrepPreferences.findWhat = "(?<=€\\s)[.\\d]+,\\d\\d";

                                 

                                and change other occurrences of findText with findGrep

                                 

                                Peter

                                • 13. Re: Rounding prices
                                  Laubender Adobe Community Professional & MVP

                                  Peter wrote:

                                  2. Use a GREP search, not a Text search:

                                   

                                  Hm, in that case he will not find instances where the "€ " is missing…

                                  He'd better search a second time for the applied character style using a GREP and adding the € plus the blank to the found strings where needed.


                                  Uwe

                                  • 14. Re: Rounding prices
                                    Peter Kahrel Adobe Community Professional & MVP

                                    You're quite right. I hadn't realised that the prices are in a character style.

                                     

                                    Peter

                                    • 15. Re: Rounding prices
                                      [Jongware] Most Valuable Participant

                                      Jaime de Vos wrote:

                                       

                                      [..]  The only thing that might cause a problem for the sales people, is that it rounds both up and down. I think that they, them being sales guys, might opt for the round up only version.

                                       

                                      Can that be specified with? (going out on a limb here);

                                       

                                      Math.roundUP
                                       
                                      

                                       

                                      Only FYI ('cause you seem to be enjoying this newly discovered "scripting"  ):

                                       

                                      number ceil (x: number)
                                      Rounds the number up to the nearest integer.

                                      ParameterTypeDescription
                                      xnumberA number.

                                       

                                      ExampleMath.ceil(x)

                                       

                                       

                                      number floor (x: number)
                                      Rounds a number down to the nearest integer.

                                      ParameterTypeDescription
                                      xnumberA number.

                                       

                                      ExampleMath.floor(x)

                                       

                                       

                                      Warning:

                                      ceil will always round up, even for the tiniest fraction above a whole number!

                                       

                                      x = 1.000000001;
                                      alert (Math.ceil(x));
                                       
                                      

                                      • 16. Re: Rounding prices
                                        Peter Kahrel Adobe Community Professional & MVP

                                        And Math.round(n) rounds up from .5 (3.5 > 4) down up to .5 (3.49 > 3).

                                        • 17. Re: Rounding prices
                                          Jaime de Vos Level 1

                                          Will take some time for me to digest all the new input. Need to wrap my head around the concept some more. Compare it to following a discussion that's partly spoken in a different language. But I'm confident I can condense the proposed changes into a working script.

                                           

                                          Thanks again.