I have a pdf which needs to be converted into Excel eventually, but in its current format, when I pasted to Excel it was all over the place. No real pattern which I could easily write a macro to align info into the right cells.



Anyway, also converted it to Word, but the format is not all that great there either.

Looking for any suggestions. I can't attach the pdf nor the full converted Word document because of the size limit, so I cut the Word down a little bit. It was 1.43mb and 152 pages.

Attached Files

TEST2.docx (194.1 KB, 11 views)

Without knowing how the data appeared in the PDF and, more importantly, how they should appear in Excel, it's impossible to craft a solution. You should be able to print a few representative pages of the existing PDF to another PDF so you can attach it here.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

A key thing to keep in mind is that formatting in converted documents ranges from sketchy to awful. Your Word document has next-page section breaks at the bottom of every page. This breaks up the table into multiple tables.

Unless this is something that has to be done on a regular basis, you may be time and money ahead simply paying someone to key it into Excel.

How about this and as far as the format in Excel, it should be no different than how it shows in the pdf. It appears though, pages 1-6 are missing the header, but from 7 down the file is pretty consistent.

Attached Files

deleted_TEST2.pdf (204.9 KB, 15 views)

There appear to be numerous headings in the data (e.g. FC, FC Title, CG, CG Title, BC, BC Title, etc.), plus heading rows on various tables, for example:
FAC, FAC Title, RPA Type, UMA
Mil Dep, CAT CODE, UMA, UMO, UM 3, UM 4, CATCODE, LONG NAME
What's supposed to happened with those?

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks Paul. This is for somebody at work, so let me consult with them and get back to you. I'll attach an Excel sample file.

Hi Paul,

I messed around with this again today, but still can't seem to find a proper solution.

Here is an example Excel file with the preferred output.

Basically, I'm just interested in the yellow highlighted rows with the data in-between the yellow rows which is 7 columns worth of data.

I don't need anything in red, green, or even the gray headers.

As for the headings in the data, not interested in any headers. In the yellow rows, I'm even only concerned with the first two data elements nor am I concerned with alignment within the cells.

All that format type stuff is secondary. The main task is just to get the data positioned in columns and rows.

Attached Files

Data.xlsx (8.6 KB, 10 views)

What are your chances of accessing the original data the PDF was created from? With the document conversion you're now getting, wrapped text gets split into separate cells for the wrapped lines and, in some cases, there's a plethora of pointless columns to deal with - and even the column counts differ from row to row.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes, we are trying to get a hold of the source data versus this pdf. Thanks again for your time.