Excel import: preserve number formats

Learn / Forums / General InDesign Topics / Excel import: preserve number formats

Viewing 2 reply threads
  • Author
    Posts
    • #104554

      I have a spreadsheet with some big numbers, such as 39,421,018. In Excel, I have formatted them in millions, so the value remains 39,421,018 but it displays as 39.42 (with the table header stating “millions”).

      But when the designer imported the table into InDesign, it displayed as “39,421,018.00”. Bless him, he went back to Excel and changed all the actual values to 39.42 and re-imported the table (thereby going to a lot of effort to undo my work and ruin the Excel numbers, which I needed to be correct for subsequent work).

      Is there a way he could have imported the table with the numbers as displayed in Excel? Any suggestions gratefully received.

    • #104563

      I don’t think so, but he could maybe apply a condition to everything after 39.42 to hide the rest. This could be a problem, if your value is e.g. 39.425?! Do you have a excel-file for a test?

      Kai

    • #104623
      Vinny –
      Member

      Yeah, Indesign doesn’t handle custom cell format very well… (euphemism…)

      What I would do is to forget about Excel custom cell formatting and use an Excel function to turn the number into formatted text.
      Maybe the easiest way would be to create a new sheet and to use a function to get the real values from your working sheet while turning them into formatted text.
      Then, in Indesign, tick “import options” and select that new sheet.

      I guess the “TEXT” function is the one you are looking for.
      See : https://support.office.com/en-us/article/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c

      Type
      =TEXT(YourWorkingSheet!A1;”#,##0.00 “)
      in A1 in your new sheet and copy/paste in order to duplicate your working sheet.

      If your table have always the same number of cells, it should be easy enough
      If not (e.g. you have to add/delete rows from time to time), then you’ll have to deal with empty cells.
      This would be more a call for an Excel forum…

      Please note I use a French version of Excel. So I tried to “translate” the function, assuming you have an English version, with a comma as a thousands separator, and a dot as a decimal separator. But I could not test it properly, so hopefully it will be ok.

      Hope that helps
      Vinny

      • #104624
        Vinny –
        Member

        Oh!
        There must be two spaces between the last 0 and the quote in the formula…
        This forum apparently deletes multiples spaces.

Viewing 2 reply threads
  • You must be logged in to reply to this topic.
>