Skip to main content
A
abhijeett89122812
Inspiring
May 28, 2018
Answered

Apply thousand seprator to numbers in single column

  • May 28, 2018
  • 4 replies
  • 4025 views

I have a table in file in which there is column in which there are about 70 numbers .ie in 70 cells

Is there any script to apply thousand separator to those numbers

eg 12500 to 12,500 or 12.500

thanks

    Correct answer Peter Kahrel

    You could do it with three Grep queries:

    Find what: (\d)\Z

    Change to: $1~%

    Find what: (\d\d)(?=(\d\d\d)+\b)

    Change to: $1,

    Find what: ~%\Z

    Change to: <Leave blank>

    But running three queries is a drag, so this script runs them:

    app.findGrepPreferences = app.changeGrepPreferences = null;
    function change (f, r) {
       app.findGrepPreferences.findWhat = f;
       app.changeGrepPreferences.changeTo = r;
       app.documents[0].changeGrep();
    }
    change ('(\\d)\\Z', '$1~%');
    change ('(\\d\\d)(?=(\\d\\d\\d)+\\b)', '$1,');
    change ('~%\\Z', '');

    The first query adds a sixth-space at the end of every cell. I forget now why that's necessary (it's been a while), but it is. Then the second query inserts the thousands separators (here, a comma; to change that to a dot, replace $1, in line 10 with a $1.). Finally, the third query removes those cell-final sixth-spaces.

    This one doesn't add separators in 4-digit numbers. To do that, in line 10 change '(\\d\\d)(?=(\\d\\d\\d)+\\b)' to '(\\d)(?=(\\d\\d\\d)+\\b)',

    P.

    4 replies

    A
    alrin0000
    Participant
    June 3, 2024

    There's no need to run three GREP expressions; one is sufficient. In the Find input, enter:

    (\d)(\d{3})(?!\d)

    , and in the Replace input, enter:

    $1\,$2

    Because this GREP logic ensures that the value to the right is not a digit, you can understand it as working from right to left.

     

    Therefore, this method can be executed repeatedly without errors.

      A
      axels47776853
      Participant
      December 30, 2024

      thank you very much alrin0000.  works perfect!!

      Peter Kahrel
      Community Expert
      Peter KahrelCommunity ExpertCorrect answer
      Community Expert
      May 28, 2018

      You could do it with three Grep queries:

      Find what: (\d)\Z

      Change to: $1~%

      Find what: (\d\d)(?=(\d\d\d)+\b)

      Change to: $1,

      Find what: ~%\Z

      Change to: <Leave blank>

      But running three queries is a drag, so this script runs them:

      app.findGrepPreferences = app.changeGrepPreferences = null;
      function change (f, r) {
         app.findGrepPreferences.findWhat = f;
         app.changeGrepPreferences.changeTo = r;
         app.documents[0].changeGrep();
      }
      change ('(\\d)\\Z', '$1~%');
      change ('(\\d\\d)(?=(\\d\\d\\d)+\\b)', '$1,');
      change ('~%\\Z', '');

      The first query adds a sixth-space at the end of every cell. I forget now why that's necessary (it's been a while), but it is. Then the second query inserts the thousands separators (here, a comma; to change that to a dot, replace $1, in line 10 with a $1.). Finally, the third query removes those cell-final sixth-spaces.

      This one doesn't add separators in 4-digit numbers. To do that, in line 10 change '(\\d\\d)(?=(\\d\\d\\d)+\\b)' to '(\\d)(?=(\\d\\d\\d)+\\b)',

      P.

        L
        Community Expert
        LaubenderCommunity Expert
        Community Expert
        May 28, 2018

        abhijeett89122812  wrote

        I have a table in file in which there is column in which there are about 70 numbers .ie in 70 cells

        Is there any script to apply thousand separator to those numbers

        eg 12500 to 12,500 or 12.500

        thanks

        What does "or" mean?

        Do you have two different languages in the same document?

        Regards,
        Uwe

        Bevi Chagnon - PubCom.com
        Bevi Chagnon - PubCom.com
        Legend
        May 28, 2018

        I'm not a GREP expert (I hope our list experts will chime in), but I think you can set a GREP style to automatically drop the period/comma into the number strings.

        Here's another quick workaround:

        • Copy the table into Excel or another spreadsheet.
        • Set the column/row formatting to show commas/periods and no decimal places.
        • Copy the table back into InDesign.

        Should take less than a minute to do this.

        You might have to save it from Excel as an .XLSX file, and then place the XLSX into InDesign in order to preserve the table structure.

        |&nbsp;&nbsp;&nbsp;&nbsp;Bevi Chagnon &nbsp;&nbsp;|&nbsp;&nbsp;Designer, Trainer, &amp; Technologist for Accessible Documents ||&nbsp;&nbsp;&nbsp;&nbsp;PubCom |&nbsp;&nbsp;&nbsp;&nbsp;Classes &amp; Books for Accessible InDesign, PDFs &amp; MS Office |
          Terms & ConditionsAccessibility statement
          Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices