PDF to Excel All Data in One Column? How to Fix It

You convert a PDF to Excel, open the file, and every row of the table is crammed into a single cell in column A — or B — with the values separated by spaces. Sorting, totals and filters are all useless. This is the most common complaint about PDF to Excel conversion, from free tools and paid ones alike, and it is fixable in a couple of minutes once you know why it happens.

Why does PDF to Excel put all data in one column?

A PDF has no cells. It stores each piece of text with a position on the page, and the “table” is only lines and spacing. A converter that cannot work out where one column ends and the next begins puts each line of text into one cell. You then split those lines into columns yourself in Excel.

• Text to Columns: the quickest fix, built into every version of Excel.
• TEXTSPLIT: a formula that splits each row live, in Excel for Microsoft 365 and Excel 2024.
• Power Query’s From PDF import: reads table structure directly from digital PDFs.
• Google Sheets: Data, then Split text to columns.

To be upfront: Slate PDF’s PDF to Excel works exactly this way on purpose. It gives you one sheet per page, a line number in column A and each line of text in column B, in the right reading order. It does not guess at column boundaries, because a wrong guess silently shifts numbers into the wrong column — which is worse than a tidy line you split yourself.

Fix 1: Split the column with Text to Columns

1. 1Select the column that holds the crammed rows (column B in a Slate PDF export).
2. 2Make sure there are enough empty columns to the right, or Excel will overwrite them.
3. 3Go to Data, then Text to Columns, choose Delimited and click Next.
4. 4Tick Space as the delimiter and tick “Treat consecutive delimiters as one”. Click Next.
5. 5Set the column data format to General so numbers become real numbers, then click Finish.

If the values were separated by commas, semicolons or tabs instead of spaces, tick that delimiter instead. The preview at the bottom of the wizard shows the split before you commit, so check that each value lands in its own column.

When values contain spaces

Splitting on spaces breaks “New York” into two cells and pushes everything after it one column to the right. Three ways out: if only one column has multi-word text, split everything and rejoin the pieces with =C2&" "&D2 on the rows that need it; if the multi-word text is always the first column and the rest are numbers, split it and rebuild the description from the leftover pieces before deleting them; or, for a native PDF, use Power Query (Fix 3), which reads the original column positions. Fixed width in the Text to Columns wizard only helps when the export kept the original spacing, which line-based exports usually do not.

Fix 2: Use TEXTSPLIT for a live split

In Excel for Microsoft 365 or Excel 2024, put =TEXTSPLIT(B2," ") in an empty cell and the row spills across into separate columns. Fill it down for every row. Because it is a formula, you can wrap it to clean as you go — for example =TEXTSPLIT(TRIM(B2)," ") removes stray spaces first. When you are happy, copy the result and Paste Special, Values to turn it into plain data.

Fix 3: Import with Power Query instead

If the PDF was produced digitally (exported from accounting software, a bank or a report generator), Excel can often read its tables directly: Data, Get Data, From File, From PDF. The Navigator lists the tables it detected on each page; pick one, then Load or Transform to clean it first. This is available in Excel for Microsoft 365 on Windows; on a Mac, check whether your Get Data menu offers it. It does not work on scanned PDFs, and complex tables with merged cells can still come out ragged — but when it works, it is the cleanest route.

Fix 4: Google Sheets

Open the exported workbook in Google Sheets, select the column, and choose Data, then Split text to columns. Pick Space (or a custom separator) from the separator menu that appears. Sheets also offers =SPLIT(B2," ") as a formula equivalent.

Then fix numbers stored as text

Even after splitting, numbers often arrive as text: green triangles in the cell corners, values aligned left, and SUM returning 0. PDF text is just characters, so anything that is not an unambiguous number to Excel stays text.

Scanned PDF? Add text before converting

If the export comes out empty — every sheet says “(empty page)” — the PDF is a scan, and there was no text to export. Run OCR PDF first to add a text layer, then convert the OCR’d file. Expect to correct a few digits by hand; OCR confuses 1 with l and 0 with O more often than you would like, which is another reason to check the total.

For a deeper look at what makes a PDF table convert cleanly in the first place, see how to convert PDF to Excel and keep the table structure. If you only need the data in a plain format for another program, exporting PDF tables to CSV is often simpler.