This post may contain affiliate excel formula tips and tricks pdf. PDF files are pretty much the norm for distributing reports these days. They provide a nice easy way to collate documents from different sources for distribution. However, once a document is in a PDF format getting that information back into a usable form is a nightmare.
If we try to copy and paste a data table from PDF into Excel it just doesn’t format as expected. In my experience the pasted data will show as one of the following:A list of valuesA continuous text string for each lineA pictureI would love to offer you the perfect solution to get the PDF data table into Excel, however I don’t believe there is one.
If the paste displays as a picture, then as far as I know, you will need to resort to third-party software which contains OCR. If the paste is either a list of values or a continuous text string, then there are some possible workarounds.
One of these should work, providing reasonable results and saving you time. However, Word is actually better at dealing with PDF tables. Or maybe, which is more likely, you have a table which requires a bit of tweaking. It may not be perfect, but it’s still closer than what you had before.
The VBA code below which will cycle through the selected data and recreate a table layout. We will need to change the following line so that it is equal to the number of columns in the source table, else the data will be in the wrong columns. We now have a table in Excel which probably just requires a bit of tweaking.
It may not be perfect, but it’s still closer than what we had before. Selective copying from the PDFWe can increase the chance of our data formatting correctly by being selective about which parts of the PDF to copy. We don’t have to select the whole table in the PDF file. Excel, then converting the other columns to Excel.
Alt whilst selecting the data in the PDF table. I have even used Adobe’s own PDF to Excel converter, and even that requires a lot of manual adjustments. Therefore, I don’t believe that a perfect solution exists for this problem. But, hopefully one of these workarounds has provided a reasonable solution and has saved you a lot of re-keying time.
You can use the following to either learn from or just to copy and paste into your own code. The columns may contain different data types, but generally the different lines for data or transactions are listed vertically.
This is the range of cells which contains the data to be used within the VLOOKUP function. For example, in the code below A1:C9 is the table_array. I have a pdf address file that is of the following format.