PDF to Excel
October 19, 2017 - by Bill Jelen
How to get data from a PDF file in to Excel. The dirty secret? Word.
Note
Katie Sullivan is a Project Manager on the Microsoft Word team. For today's tip, a benefit of Word.
While Excel fans sometimes tease that Word and PowerPoint are freeware apps that come on the Excel DVD, there are times when Microsoft Word offers a feature that Excel does not. In those cases, it makes sense to copy your data from Excel, paste to Word, do the command, then copy back to Excel. Here are some examples of techniques that are better handled in Word than in Excel.
Technique 5
Extract Data from a PDF
Say that someone has an Excel workbook and saves that workbook as a PDF. They send it to you. This is annoying, and clearly they don't want you to reuse the data. If you open the PDF in Acrobat Reader, copy the data, and paste to Excel, it will unwind into a single column. But here is the secret: Paste that data to Word first. The rows and columns will paste properly. You can then copy from Word and paste back into Excel. (If you are stuck in a pre-2013 version of Office, I recommend Able2Extract: http://mrx.cl/pdftoxl.)
Here is the original data on the left and how it looks when you paste directly from PDF to Excel on the right. You can see that the data “unwinds,” with B1:C1 going to A2:A3 and so on.
Paste that same data to Word (below left), then copy from Word and paste to Excel (below right). The data stays in the original order. You can unapply Word Wrap and adjust the column widths to get back to the original data.
Watch Video
- Exporting Excel data as a PDF
- Today's trick is from Katie Sullivan on the Word team
- There are a few cases where Microsoft Word can do things better than Excel
- One of those is pasting data from PDF files
- Paste to Word
- Copy the data from Word
- Paste to Excel
- In the outtake, a way in Acrobat to Copy with Formatting and it will paste half-correctly to Excel
Video Transcript
Learn Excel from MrExcel podcast, episode 2052 - PDF to Excel!
Hey, welcome back to the MrExcel netcast, I'll be podcasting this entire book, click the “i” in the top-right hand corner to get to the playlist!
Today, we're talking about those evil people who have data in Excel, but they don't send you the data in Excel, they send it as a PDF. So File, Export, create a PDF, want to Open file after publishing, call it Podcast2052, click Publish, alright, there is our data in the PDF. Now I got this PDF, but I want to create a Pivot table or to create a chart or do something, but I can't do that from the PDF, so I select the data in the PDF, Ctrl+C to copy.
This annoys me so much, why can't the Excel team take their own data, they created that PDF, why can't they paste correctly, and have it pasted into a table instead of down a column? Alright, Word, Ctrl+V, look, it pastes correctly, select the data in Word, Ctrl+C, switch from Word back to Excel, Ctrl+V to paste, and it round-trips correctly. Why can Word do this and not Excel? Well this tip, and a lot of other tips, are in the book, click the “i” in the top-right hand corner to buy the book.
Alright, episode recap: Started out exporting Excel data as a PDF, today's trick is from Katie Sullivan on the Word team. One of those things where Word is better than Excel is accepting pasted data from PDF files. Scrape the data from the PDF file, Ctrl+C to copy, go to Word, paste to Word, it'll paste correctly, copy the data from Word, go back to Excel, paste to Excel. Yet another thing that Microsoft Word does better then Excel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel!
This is an outtake, it's such a weak, weak trick. If you need to go directly from the PDF to Excel, and you have full Adobe Acrobat instead of just Acrobat Reader, you can right-click and Copy With Formatting. Then, when you come to Excel and paste, Ctrl+V, but look, it's not as ju- urrrrrgh!
Download File
Download the sample file here: Podcast2052.xlsm
Title Photo: Milosz_Karski / Pixabay
