Use a Third-Party Tool
Let's be honest. I think a lot of people convert their Excel files to PDF because they don't want the recipient to be able to reuse the data in Excel. Send me a 450 page document in PDF and they figure I won't have the time or inclination to take each page from PDF to Word and back to Excel. I actually had this exact problem once and converted the whole document back to an Excel database using Able2Extract. You can test out Able2Extract 7 with its free seven-day trial.
The software is simple to use. Open the PDF file with Able2Extract. The first step is to identify which area(s) you want to import. If the document contains titles or page numbers in the header, you could choose to crop the page to ignore those sections of the document. Able2Extract offers to convert the data to Excel, Word, PowerPoint, Publisher, HTML, or AutoCad, as shown in Figure 7.
The result is shown in Figure 8. Able2Extract preserved the original font colors and numeric formatting. The process was not perfect; the font size changed from 11 point to 9 point and the right-justified headings moved back to the left. However, once I had the data in a table in Excel, it is easy enough to fix those issues.
Able2Extract also offers a pro version that will use OCR to recognize characters in PDF documents that have been created using a scanner. Download a free trial from http://www.investintech.com/.
Best Practice?
It really should not be this hard to get data out of a PDF file and back into Excel. If you have a one-page table, the PDF-to-Word-to-Excel solution will work suitably well. If you have a several-page document with many different tables or repeating headers, then going to a third-party solution such as Able2Extract makes sense.
Fig. 7
Fig. 8
Bill Jelen, a CFO contributing editor, runs MrExcel.com and is the author of 32 books about Microsoft Excel, including Charts & Graphs for Excel 2010. Post questions for future columns in the "Suggest a Topic" section of the Spreadsheet Community Center on the right.
by Amanda Funderburke