Stuck, Need Help (please). Trying to export unstructured text from PDF to import into Excel separating/placing text into specific columns

phbalanced

New Member
Joined
May 7, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello, I'm hoping someone can help with this work project. I have experience with formulas and pivot table and am somewhat familiar with Power Query but apparently not enough for it to help me with this work project.

I've been trying to find an Excel work-around for this project to simplify it so it doesn't involve days (maybe even weeks) of unnecessary data entry. I've used Power Query successfully a number of times, but I am completely stumped/stuck by this particular issue. And it's driving me crazy because I know there has got to be a way to do this that I simply cannot find searching the web or YouTube. Been trying to find a solution for 5 days now, and I'm about to give up and start the painful data entry process.

What I'm trying to accomplish is exporting text from a PDF and importing it into Excel place the text into specific columns so it has some structure and can be sorted, filtered, and have pivot tables created with it.

The text was originally entered/created in a 3-column format that a PDF was eventually created from. It pertain to design awards that my company has received over the years dating back to 1954. Problem with this PDF is that none of the categories are labeled (Award, Year, Organization, Project Name, City, State, etc.) to make exporting the data (then performing some clean up) to successfully import it into Excel placing it into the appropriate columns, and then there is the issue that not every award contains all of the fields I mentioned. Some has a few of the field labels and some have all of them (sometimes multiple times). And then there is the 3-column format issue. It's just a mess.

I've posted screen shots of two (2) pages of the PDF (attached) to show the difference between award entries in this PDF and then a screen shot of the Excel file (attached) how we need the text/data structured. I entered the data/text from the two (2) PDF screen shots into the Excel file so one can see how we need it laid out.

My brain is fried, and I'm completely lost at this point. I've tried every single thing I can think of and many things I didn't even know how to do until watching endless number of YouTube videos. If there is anyone in this forum who has expertise and/or a clue how to accomplish, I would be forever grateful. Heck, I would even pay someone to help me out with it at this point. Hoping there is an Excel genius in this forum who has come across this issue/problem before. Thanks in advance for any help you can provide!

Pascal
 

Attachments

  • HKS-Award-PDF-ScreenShot-1.PNG
    HKS-Award-PDF-ScreenShot-1.PNG
    24.1 KB · Views: 5
  • HKS-Award-PDF-ScreenShot-2.PNG
    HKS-Award-PDF-ScreenShot-2.PNG
    28.7 KB · Views: 5
  • HKS-Awards-Excel-ScreenShot-1.PNG
    HKS-Awards-Excel-ScreenShot-1.PNG
    12.5 KB · Views: 5

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,089
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top