How to Pull Tables Off PDFs and Put Them in Excel

When you need to use Microsoft Excel to manipulate and analyze a table of data that originates in a PDF document, your options for migrating the information from one document type to another may look limited. Before you resign yourself to retyping the data, however, experiment with options that rely on the clipboard or other applications to transfer information one table, or one column, at a time. You may find your content migrates successfully in one or a few passes.

In Adobe Acrobat Standard and Adobe Acrobat Professional, you can use the Save As Other command to create a new file in a different file format, including formats compatible with both Microsoft Word and Microsoft Excel. The Save As dialog box includes a drop-down menu from which you can select file types, including "Tables in Excel Spreadsheet," which yields an XML file format compatible with Excel. The File menu's Export submenu includes "Export Multiple Files," which provides you with an interface through which you can add multiple files for XML conversion to a location you specify.

If your table consists of a single column of information, you can use standard PDF selection tools to marquee around the text so you can copy it to the clipboard. Once you click in a cell in a Microsoft Excel worksheet, you can paste the text into your document. Excel places each row of information into an individual worksheet cell. If the contents of your table exceed Excel's default column width, click on the column header to select all the cells, switch to the Home tab's Cells group, click on the "Format" item to open its drop-down menu and choose "AutoFit Column Width" from the Cell Size category.

If you copy and paste from multicolumn PDF text into Microsoft Excel, all your content winds up in one column of cells, with spaces between the material that originated in different tabular columns. Excel's Convert Text to Columns wizard can simplify the task of breaking up this type of text into multiple contiguous worksheet columns, provided that your text includes some delimiting character that Excel can interpret as a boundary. Tab characters turn into spaces when you paste from PDFs into Excel, so the wizard's support for tabs as delimiters won't help you in this instance. If your text contains no spaces except for the one that shows where the PDF column break occurred, however, the wizard can break it up for you.

To launch Microsoft Excel's Convert Text to Columns wizard, switch to the Data tab of the Excel ribbon, locate its Data Tools group and click on the "Text to Columns" item. Opt for delimited output, select a delimiter character and preview your data conversion. If the results match your expectations, click on the "Next" button to specify data formats for individual data columns and finish breaking up your pasted import. The wizard can place your delimited text into new columns next to the text you pasted or overwrite the pasted text with the first column of the converted data. For text that includes columns with fixed numbers of characters, the Fixed Width option enables you to create and position column breaks.

If you have access to Adobe Acrobat Standard or Adobe Acrobat Professional, you can use the software's Select tool in column mode so your clipboard captures only one column of tabular text at a time. Activate the tool, press and hold the "Alt" key to enter column mode under Windows or the "Cmd" key to trigger it on a Mac. The cursor displays a dotted-lined box superimposed over the standard Select tool's I-beam shape, indicating that it will select one text column at a time when you marquee around PDF content. You can paste this material into a single Microsoft Excel column of cells and repeat the process for each PDF column in your data table.

Adobe Acrobat Standard or Adobe Acrobat Professional's Select tool can transfer a table's worth of data in one pass. Press and hold the "Alt" or "Cmd" key, select your table in Acrobat and right-click on the selected area. From the menu that opens at your cursor, choose "Open Table in Spreadsheet" to instigate a behind-the-scenes conversion into a CSV file that opens seamlessly in Microsoft Excel. The result can't capture any formulas that produced the tabular data, but it does give you worksheet access to the data from your PDF.