CreativePro Forum

Return to Member Forum

• Author
  Posts
• • March 21, 2024 at 3:06 am #14401213
    Elisabeth Straumoey

    Member

    I need to keep numbers formatted as numbers (dynamic and updateable :-)) in Excel, and place tables with these numbers in InDesign. InDesign however translates white space in Excel numbers to “Ê”. I.e. “1 000 000” in Excel becomes “1Ê000Ê000” in InDesign. :-(

    Is there a way to avoid this, without having to define the Excel numbers as text (my old workaround for this problem)?

    The Excel file/spreadsheet is linked to InDesign so that the client can make updates in Excel that will be reflected in InDesign. If I perform a find/change operation in InDesign to get rid of the “Ê”s, they all come back as soon as the spreadsheet is updated.

    Thank you beforehand for feedback on this. :-)

    Regards, Elisabeth

  • March 21, 2024 at 3:36 am #14401216
    Steve Davis

    Participant

    Have you tried exporting to UTF16?

  • March 21, 2024 at 4:33 am #14401217
    Elisabeth Straumoey

    Member

    I didn’t know that was an option. :-) I only see UTF8 on my Excel for Mac here. Does this mean I need to use Data Merge to get the table into InDesign?

    Seems a clunky workflow and I don’t see how it would work in this case. These are annual report type tables and I need to place them as they are, not start naming columns / changing things to adhere to Data Merge limitations? Data Merge dislikes empty fields/cells in the first table row, correct?

    When I do try placing the CSV file (UTF8) I get the error message “The data source file you selected either has no records or is not a supported file format. Please fix the file, select a file that contains records, or select a supported file type.” :-/

  • March 21, 2024 at 4:37 am #14401218
    Steve Davis

    Participant

    I’m on Mac as well.

  • March 21, 2024 at 5:00 am #14401219
    Elisabeth Straumoey

    Member

    Apologies, UTF is all new to me, of course you are right, UTF16 was available to me.

    When I place this UTF16/CSV file all I get is a seemingly empty box with overset text. The story editor tells me the data is there, but it is spread really wide apart (I have to do a find/change operation on words I know should be there to be able to see anything at all). If UTF16 is a solution I must be doing something wrong.

    The client will be working in Excel.

    If I can get the placing of CSV right somehow (I don’t know how :-/), will changes in the CSV file reflect automatically in InDesign as they would with an Excel file?

  • March 21, 2024 at 6:00 am #14401220
    David Blatner

    Keymaster

    This is not normal, when importing Excel docs. You might check your settings in Import Options (which you can enable in the Place dialog box).
    I’m guessing perhaps the white space character is not a simple space, but instead some kind of special character that Excel or the client is using. If you choose Type > Show Hidden Characters (in InDesign), do you see some kind of special character in there, along with the Ê? If so, see this article:

    Free Guide to InDesign Special Characters

  • March 21, 2024 at 6:23 am #14401221
    Elisabeth Straumoey

    Member

    Thank you for your reply. I also suspect an Excel function of separating the numbers into threes contains some sort of special character that translates poorly upon import into InDesign.

    I don’t see anything that might amend this in the Import Options, unformatted tabbed text etc produces the same result.

    There are no hidden characters along with the Ê.

    Beforehand thanks again for trying to solve this!

    • March 21, 2024 at 9:02 am #14401230
      David Blatner

      Keymaster

      Interesting challenge. If the spaces are “hard coded” in the data, then it may be that you need to do a search/replace in Excel, or find/change in InDesign, to get rid of the spaces. InDesign has no way of knowing that “1 000 000” is “one million” I think.

  • March 21, 2024 at 3:30 pm #14401258
    Dhafir Photo

    Participant

    Try to do one of these:
    – Select the letter Ê and see what it’s refer in Glyph panel.
    – Copy the letter Ê and paste it in Find field of GREP or Text tab to see what its code.
    – If you need to separate 1000000 to 1 000 000, use this regex in GREP tab in Find/Change:
    in Find: \d{1,3}(?=(\d{3})+)
    in Change: $0~s

  • March 22, 2024 at 1:41 am #14401260
    Elisabeth Straumoey

    Member

    Thank you! The problem is the Excel number spaces that turn into Ê in InDesign, every time the linked Excel-file is updated. Easier with a regular find/change of Ê into non-breaking space in InDesign than the GREP suggestion above, but this remains a workaround. I suppose I’ll save the query in the find/change palette and hope for a limited number of revisions in Excel. :-)

• Author
  Posts