Converting PDF into EXCEL

susie112

New Member
Joined
Aug 14, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Hello!
I know the very basics of Excel. Even so, I know Excel is very capable of doing so many things so I hope there is a solution to my issue.

I have many pages of client data - names, addresses, DL#s that needs to be entered into an Excel database. The current layout of the data on
the paper copies is LN, FM (space, tab or invisible column) Address - physical address in top, city, state, zip below (space, tab or invisible column)
Lic#.

When I scan the sheets to my computer, they are saved as PDF. After I edit out the excess lines and info I don't need, I then export the data into Excel.
I'm good there. But when I open Excel to look at my spreadsheet, the data is not clean. Some cells have several rows of data merged into one rather than
having a row for each name, address & dl#. First, I un-merge all cells which scatters the addresses and DL#s into different columns. Once I get each data set
into its corresponding column (ex: all address lined up) I know I can use Text to Columns to spread out the data then use Copy+Paste Transpose to move it all back.
That takes a while, almost as long as typing out each client.

Is there a formula or macro or other way of changing all of the information so that a name, address and DL# are all on the same row but in separate columns?
The columns are separated into Last Name - First Name - Address - city - State - zip - DL#.

If you know of a better way, I would greatly appreciate it. Currently, it takes me over 2 hours to export then clean 75 names. The total project has about 20,000 names.
There HAS TO BE an easier way so I'm turning to the Excel Pro's for ideas. I appreciate any help that is offered.

Uploaded images - excel1 and excel2 are how the PDFs look. Excel data is how the data is exported.
 

Attachments

  • excel1.JPG
    excel1.JPG
    37.1 KB · Views: 12
  • excel2.JPG
    excel2.JPG
    44.9 KB · Views: 12
  • exceldata.JPG
    exceldata.JPG
    56.7 KB · Views: 9

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.

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
413
Office Version
  1. 2019
Platform
  1. Windows
my suggestion would be to start recording a macro when you start do all the changes and then stop recording. See how that macro works the next time.
 

ManiacB

Board Regular
Joined
Aug 11, 2020
Messages
56
Office Version
  1. 365
Platform
  1. Windows
I recommend you try a better scanning process. You are correcting too many mistakes at the end of the process.
 

susie112

New Member
Joined
Aug 14, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
Unfortunately, my work scanner only has a PDF option. Thanks for replying.
 

susie112

New Member
Joined
Aug 14, 2020
Messages
3
Office Version
  1. 2010
Platform
  1. Windows
my suggestion would be to start recording a macro when you start do all the changes and then stop recording. See how that macro works the next time.
I know of macros but no clue how to create one. I'll see if there is a how-to in this forum.
Thanks for replying.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,354
Messages
5,601,139
Members
414,430
Latest member
jtdinh205

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