Big Bucks From Big Signs Pdf To Excel
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.
Is there Any Option to Get Data from a PDF into Excel Sheet? I'm sure you can relate to one of these questions. I hear them all the time on email, from the people I meet, even sometimes I get into a situation where I need to convert a PDF file into an Excel worksheet or into a Word document. It’s a big time saver, there is no doubt about. Free PDF to Excel Converter is a free and easy-to-use PDF converter software to batch convert PDF document to Excel files. Just add PDF files to the list, select output format and directory, and click 'Convert Now!' Button to start converting. Free PDF to Excel Converter can convert to Excel document from encrypted PDF document.
PDFs are not born equal
The pasted information will be displayed in Excel differently based on how the PDF was created. In my experience the pasted data will show as one of the following:
- A list of values
- A continuous text string for each line
- A picture
I 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.
Here is our example PDF file:
Copy the PDF table into Word first
Excel is a software program which is designed to work with tables, whilst Word is designed to work with text. However, Word is actually better at dealing with PDF tables.
- Copy the table from the PDF document
- Paste the table into Word
- Copy the table from Word
- Paste the table into Excel
You may now have a perfect data table in Excel. 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.
Pasted list: Use VBA to format the list
If the paste into Excel is just a list of values in one column we can turn to VBA for a bit of help.
The VBA code below which will cycle through the selected data and recreate a table layout.
Big Bucks From Big Signs Pdf To Excellence
- Copy the table from the PDF document
- Paste the table into Excel
- Select all the pasted cells
- Run the Macro below
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.
Pasted strings: Use Excel functionality to split the string
If the paste into Excel is a continuous text string for each line then you can use Excel’s built-in functionality to separate the string into columns.
Data -> Text to columns
Select “Delimited” from the Convert Text to Column Wizard Step 1 window
Click Next
Select “Space” from the Convert Text to Columns Wizard Step 2 window
Click Next
Click Finish
If there are spaces between words in the data this will unfortunately separate each of those words into a different cell. 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.
Pasted Strings: Use VBA to split the string
Rather than using the Excel functionality we could use VBA code to achieve the same effect.
- Copy the table from the PDF document
- Paste the table into Excel
- Select all the pasted cells
- Run the Macro below
Selective copying from the PDF
We 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. For example if the first column in the table is a description column (maybe with spaces between the words), then we will get better results by selecting the first column, converting to Excel, then converting the other columns to Excel.
To achieve this selective copying press Ctrl+Alt whilst selecting the data in the PDF table.
A perfect solution?
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.
Related Posts
- 3 ways to change or insert images based cell values
From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell value. Or you may think if this a formula which changes a picture, rather…
- Cell and Range VBA properties and actions
Manipulating ranges and cells is one of the most common actions in VBA. You can use the following to either learn from or just to copy and paste into your own code. Referencing ranges & cells from the worksheet 'Reference…
- Sorting left to right in Excel
Most of the data we encounter in Excel, is in the form of a list. The columns may contain different data types, but generally the different lines for data or transactions are listed vertically. Sorting data top to bottom Sorting…
- Automatically expand the VLOOKUP data range
The 2nd argument in the VLOOKUP function is the table_array. 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. =VLOOKUP('O Redding',A1:C9,3,FALSE) When…
Want to Learn VBA & Macros?
If you want to automate Excel and save time, my recommended resouces for learning VBA Macros are:Big Bucks From Big Signs Pdf To Excellent
Courses:
The Complete Excel VBA Course for BeginnersRelated Articles
- 1 Copy From the Internet and Paste Into Excel
- 2 Extract Text From a PDF to Word
- 3 Import PDF Files Into Word
- 4 Open a Pagemaker Document in Microsoft Word
As software has grown more sophisticated, there is more opportunity for users to mash up content between different programs. The Excel spreadsheet program, for example, used to handle only text and numbers, but can now display graphs, photos, videos and other types of content. You may want to import a PDF into Excel, but the process is not always straightforward. Here are some strategies for moving content from a PDF file into an Excel spreadsheet.
Copying PDF as an Image
You cannot copy a PDF to Excel directly, as Excel does not have built-in support for the PDF file format. However, you can display PDF content as an image in Excel. To do so, navigate within your PDF file until the content you want to copy into Excel appears on your computer screen. Press the Print Screen key on your PC keyboard to copy the image into your clipboard. If you're using a Mac, the same screen image can be captured if you press Command+Ctrl+Shift+3. Switch to Excel, click in a cell where you want the content to appear, and paste the image into your spreadsheet. (Use Ctrl+V to paste the image; you can undo the paste with Ctrl+Z).
If you want to edit the image beforehand, the easiest option is to paste the image into an image-editing program like Irfanview or Paint to crop the borders of the image or alter it in other ways. You can also use the Screenshot option in Word, which is available on the Insert toolbar. When you're done, simply copy the edited image and paste it into Excel.
Copying PDF Text
Not all PDF files allow the copying of text, but many do. The best way to find out is to simply try to copy the text you want and see if it works. Rather than pasting the desired text directly into Excel, you're better off pasting it into an editing program like Notepad or Word. Use these programs to adjust the formatting and appearance of the text, or to clean up any errant characters. When you have the text the way you want it, copy and paste into your spreadsheet.
If the PDF text cannot be copied, keep in mind that you can treat it like an image and paste it into Excel that way. The text will not be editable, however.
Copying a Table from a PDF
Transferring tabular data from a PDF to a spreadsheet is the trickiest process of all because there are so many variables involved in how the table and text are formatted in the PDF and how the final information might appear in Excel. Try these approaches to see which one works the best:
- Start with a direct attempt to copy and paste, just as you would with text. With luck, the data will arrange itself in Excel into neat columns and rows, just the way you hoped it would. A small pull-down menu with Paste Options may open in Excel as you paste your content. Use it to try different options for achieving the results you want.
- Alternatively, copy and paste the data table into Word, or another word processing program. Then copy/paste the table from Word into Excel. Try several variations of this approach. Word's Paste Special command allows you to paste data in several different formats, such as Unformatted Text or HTML Format. Each option will display differently in Word and in Excel, so use the one that gives the best result.
Tip
After pasting data into Word, the Convert Text to Table option is activated under the list of Table commands. You can use this to clean up your data.
References (3)
Resources (3)
About the Author
David Sarokin is a well-known Internet specialist with publications in a wide variety of topics for small businesses, from the best uses of information technology to the steps for incorporating your business. As a small business owner himself, he brings an experienced perspective to all his work. He is the author of Missed Information (MIT Press, 2016), detailing how our social systems like health care, finance and government can be improved with better quality information, and is working on a new book on the future of corporations.
Photo Credits
- Ablestock.com/AbleStock.com/Getty Images
