Excel import: preserve number formats
Learn / Forums / General InDesign Topics / Excel import: preserve number formats
Tagged: data, Excel, formatting, number
- This topic has 3 replies, 3 voices, and was last updated 5 years, 9 months ago by Vinny –.
-
AuthorPosts
-
-
June 27, 2018 at 2:34 am #104554Patrick NeylanMember
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.
-
June 27, 2018 at 9:26 am #104563Kai RübsamenMember
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
-
June 28, 2018 at 5:23 am #104623Vinny –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-93d29371225cType
=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
-
-
AuthorPosts
- You must be logged in to reply to this topic.