Custom Excel Number Formats for linked table in InDesign
|
- This topic has 4 replies, 3 voices, and was last updated 6 years, 12 months ago by
.
-
Posts
-
-
Hi.
I am having some problems translating a custom Excel number format into InDesign via a linked table. The Excel table is linked into my InDesign document using the excellent approach explained here :) https://creativepro.com/easier-import-of-complex-excel-spreadsheets.php.
Here is what I am trying to do:
1. Excel cell contains value that is in the hundreds of thousands (i.e. 107,500).
2. I created a custom number format to show the value in 1000s with one decimal place, shows a ‘k’ to the end, and shows negative numbers within brackets (i.e. 107,500 properly displays as 107.5k in Excel). The Excel number format I created is #,##0.0,k;(#,##0.0,k).
3. When linked into InDesign, the cell reads as 107,500k.
I have succeeded in doing what I have described above but without the decimal place (i.e. the custom number format #,##0,k; (#,##0,k) properly translates 107,500 to 107k in both Excel and InDesign).It’s possible / likely ;) that I have made an error in the custom number format that still appears to display properly in Excel, but is revealed when linking into an InDesign table.
I am using Excel for Mac 15.24 and InDesign CS6. Any guidance would be greatly appreciated. Thanks!
-
I don’t know about number formats in Excel, but if they turn out to be lost in translation, you can always import the numbers as they are and do one or more replacements in InDesign. For example, to change your numbers, use this GREP find/replace:
Find what: \d{1,3}\.[1-9]\K00
Change to: kThe GREP expression reads ‘Match two zeros when they’re preceded by (\K) 1-3 digits followed by a dot followed by 1-9’.
P.
-
-
-
-
- You must be logged in to reply to this topic.
 |