Why a PDF table comes out wrong in Excel, and how to fix it

A PDF does not contain a table. It contains words, each drawn at a position on the page, and sometimes lines drawn between them. A converter has to guess which words form a row and where one column ends and the next begins, from the positions alone. When it guesses wrongly you get one of four results: everything in one column, columns split in the wrong places, several columns merged into one cell, or text that should be numbers. Each has a cause you can see in the PDF and a fix, and a scanned PDF has no words to find at all. COLUMNS finds columns from the table’s own ruled lines where it has them and from the gaps its rows share where it does not, in the browser tab.

What a converter has to work with

Inside a PDF, a line of a table is a series of instructions: move to this point, draw this run of characters in this font; move over there, draw that run. The runs can be whole cells, single words or single letters, depending on the program that made the PDF, and nothing marks where a cell starts. A converter groups runs that share a baseline into a row, then looks for vertical bands of the page that the words of most rows fall into and nothing crosses. Those bands are the columns.

That works well on a table with clear gaps between columns. It struggles on the kinds of table that are common in practice: long descriptions that wrap onto a second line, headings that span two columns, right-aligned amounts whose left edges wander, and statements laid out with spaces rather than positions.

The four ways it goes wrong

One more that looks like a converter fault and is not: the output is empty or full of nonsense characters. If you cannot select the text in the PDF with the mouse, it is a scan, a picture of a page, and there are no words for any converter to place. It needs OCR first; OPTIC gives a scanned page a text layer. If you can select the text but it pastes as gibberish, the PDF’s fonts have no mapping from their glyphs back to letters, and OCR is again the only way round.

Before converting, look at the table

• Are there vertical lines between the columns? A table with ruled columns is the easiest case; a converter that uses the rules as column edges gets it right even when the gaps are uneven.
• Do descriptions wrap onto two lines? The second line becomes a row of its own with only one column filled. Sort by a column that should never be empty, such as date or amount, and the stray lines gather together; join them to the row above and delete them.
• Is it one table per page, with the heading repeated? Put every page on one sheet, then filter out the repeated heading rows.
• Are there account numbers or codes with leading zeros? Keep those columns as text, or 00123 becomes 123.

Converting in the browser

COLUMNS reads the PDF in the tab, so a bank statement is not sent to anyone’s server. It puts words that share a line into a row, uses the table’s own vertical rules as column edges where the page draws them, and otherwise finds the bands nearly every row leaves empty, so that a heading crossing a gap is outvoted rather than merging two columns. A table laid out with runs of spaces is split at each run of two or more spaces, with each piece placed by its share of the line. Amounts with thousands separators, currency signs, brackets for negatives and percentages are stored as numbers with a matching format, so the sheet adds up.

You choose the pages, one sheet a page or all on one sheet, and whether to keep every cell as text for codes and account numbers. It writes an .xlsx or a CSV. A page with no words is named as probably scanned. Converting and looking at the result is free; saving it is part of OBSCURA Pro, or US$1 to save from one tool for a day.

No converter reads every table right, this one included. A statement whose descriptions wrap, or a report with nested headings, will still need the tidying above. Check the column totals against the PDF before relying on the sheet.