CreativePro Forum

Return to Member Forum

• Author
  Posts
• • April 9, 2017 at 7:57 am #93618
    Anonymous

    Inactive

    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!

  • April 12, 2017 at 1:13 am #93670
    Peter Kahrel

    Participant

    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: k

    The GREP expression reads ‘Match two zeros when they’re preceded by (\K) 1-3 digits followed by a dot followed by 1-9’.

    P.

  • April 12, 2017 at 5:10 am #93674
    Anonymous

    Inactive

    Hi Peter.

    That is a good idea. I have limited experience with GREP so thanks for posting the code. I will give this a try.

    Daan.

  • April 18, 2017 at 5:34 pm #93747
    Le Vanc Hagl

    Member

    “\d{1,3}\.[1-9]\K00” is regex?

  • April 19, 2017 at 12:57 am #93748
    Peter Kahrel

    Participant

    Yes.

• Author
  Posts