20 Replies Latest reply on Aug 9, 2013 2:06 PM by MW Design

    Cut & Paste Poorly Formatted Data from Excel into Indesign: Major Problems

    John Terpening

      I have A LOT of data to bring into my Indesign template. Here's the problem:

       

      The data tables are formatted every which way you can imagine—some cells text, some cells numbers, some numbers with no decimals, some numbers showing 1 decimal, etc.

      The problem cells are the ones that are set to show zero decimals in Excel, but ARE showing a decimal in excel...gets pasted into InDesign as a zero, though we want the decimal to display.

       

      That probably makes no sense, so here's an example:

       

      Excel Table:

       

      Airlines (Directly)275528.1
      Corporate Travel Dept.7113.6
      Travel Guide(s)7114.5
      2012Personal Recommendation (Friends/Rel) 20
      2011Friends/Relatives15
      2012National/State/City Travel Office 4
      2011Nat'l Govt. Tourist Ofc.1
      2011State/City Travel Ofc.1
      2012Tour Operator/Travel Club 12
      2011Tour Company5
      2012Online Travel Agency (OTA) 31
      2012Travel Agency Office 18
      2011Travel Agency30
      2012Other 4
      2011TV/Radio0
      2011Personal Computer48
      2011In-flight Info. Systems1
      2011Newspapers/Magazines1

       

      The "0" by "TV/Radio" displays as 0.4 in Excel and I need it to display that was in the InDesign table.  The problem is that it's set as a number with zero decimals in Excel, but it's showing the number (not rounding to zero decimals, though set to display zero decimals)!

       

      I know what you are saying...just change the cells with 0s to 'numbers, show 1 decimal'. However, I have dozens and dozens of files with hundreds and hundreds of tables with countless "0s" that would need fixing.

       

      I tried changing the tables to text, saving as different types of files, setting all the cells to general, et al, but I just can't get the cells to paste in exactly as they are displayed, irrespective of the excel settings.

       

      I went to Edit->Preferences->Clipboard Handling and tried both settings—Text only and the other one that maintains the information/styling.

       

      Any ideas? Without creating a bunch of macros to fix all the excel files, I have no other ideas...hoping an easy solution is just escaping me.