Copy PDF Table to Excel: Paste Fixes That Work

To copy a PDF table to Excel when everything lands in one column, paste the rows, select that column, and run Text to Columns on the Data tab. Choose Delimited if the pieces are separated by tabs or spaces, or Fixed width if you can see aligned gaps. Then set each column's type.

Why does a pasted PDF table land in one column?

A pasted PDF table lands in one column because the copied text has no tab characters between the cells, so Excel sees each row as one line of text. Excel splits pasted data into columns at tabs. A PDF viewer rarely puts tabs there, since the PDF never stored a table.

What the PDF actually holds

A PDF stores words at positions on the page. It has no concept of a cell. When you drag across a table and copy, the viewer reads the words in some order and writes them out as plain text. Where it sees a large gap between two words, it may add spaces, a tab or nothing at all. What you get depends on the viewer's guess.

What the paste looks like

Here is an example of four statement rows after a plain paste into column A, with the cells shown as they appear. The figures are made up.

Every row is a single string. A sum over the amounts is impossible, and a sort will order the rows as text.

Three typical outcomes

1. One column of text with spaces. The most common result, as in the example above.
2. One column with the numbers split off. A viewer sometimes keeps the amounts apart by tabs but not the descriptions.
3. Each cell on its own line. Some viewers copy by column, so all the dates come first, then all the descriptions, then all the amounts. The rows no longer line up as rows.

The third is the worst case. Check the first three rows against the PDF before you spend time on a fix.

Text to Columns for a pasted PDF table

Text to Columns splits one column of text into several, using either a delimiter or fixed widths. For a pasted PDF table, Fixed width is usually better than Delimited, because descriptions contain spaces, and a space delimiter would cut a description into pieces.

When to choose Delimited

Choose Delimited when each row has the same number of pieces and a character that separates them. A tab, a semicolon or a pipe works. A space works only when no field contains a space, which rules out most descriptions.

When to choose Fixed width

Choose Fixed width when the columns line up vertically, as they do when the text is shown in aligned columns. In the example above, the date takes the first five characters and the amount sits in a block at the end. If the lines are different lengths because of long descriptions, Fixed width is unreliable at the right-hand end. In that case, split off the dates from the left and the numbers from the right, and keep the middle as the description.

The step list

These steps use Fixed width and assume the pasted data is in column A. Menu names come from current desktop versions of Excel and may differ slightly in older ones.

1. Paste the copied rows into column A, starting at A2, and leave the first row for headings.
2. Select the whole pasted range in column A.
3. On the Data tab, choose Text to Columns.
4. In step 1 of the wizard, choose Fixed width and select Next.
5. In step 2, click in the preview to place a break line where each column starts. Click a line and drag to move it, or double-click to remove it. For the example, put breaks after the date, and before the first amount.
6. Select Next. In step 3, click each column in the preview and set its type. Pick Text for a column of reference numbers whose leading zeros you want to keep. Pick Date, with the order your dates use, for the date column. Leave the amounts on General.
7. Set the Destination cell to B2, so the original column A stays untouched as your backup, then select Finish.

Before and after

After step 7, the four rows from earlier look like this. The descriptions are in their own column, and the amounts can be summed.

The break lines in the wizard put the date and the two money columns apart from the free text between them. If the last two numbers did not split, the cause is usually a different width between the amount and the balance on some rows. Use Delimited with Space for the last two columns after splitting the description off.

Check the numbers afterward

Look at the alignment of the amounts. Numbers align to the right by default. Amounts that sit on the left are text, and they will not sum. Select the column, and use Text to Columns again with Delimited and no delimiter selected, finishing on General, which turns text that looks like a number into a number.

Paste Special and the Text Import Wizard for PDF copies

Paste Special with Text, or the Text Import Wizard, controls how Excel reads the copied text, but neither one can invent separators that the viewer did not write. They help when the copied text contains tabs or uses a pattern the wizard can pick up, and they stop Excel from applying its own guesses to your values.

Paste Special, Text

Copy the table, right-click the target cell, and choose Paste Special, then Text (or Unformatted Text, depending on your version). This strips fonts and colours from the copied data. It is useful when the plain paste brought a pile of hidden formatting along, or when the paste turned some rows into links. It does not change where the cells split.

The Text Import Wizard

Paste the text into a plain text file first, save it as .txt, and open it in Excel. In recent Excel versions, the older wizard may be hidden, so look for it under Data, Get Data, Legacy Wizards if it is available, or use From Text/CSV, which previews the split and lets you change the delimiter. The result is the same kind of split as Text to Columns, with the advantage that you set each column's type before any value is changed.

Protect values from Excel's guesses

Excel converts some text on paste. A reference such as 3-4 can turn into a date, and a code with a leading zero loses the zero. If you paste identifiers, set the destination column to Text before you paste. For bank amounts, the more common problem is a minus sign printed after the number (220.00-), which Excel often reads as text. A formula such as =IF(RIGHT(C2,1)="-",-VALUE(LEFT(C2,LEN(C2)-1)),VALUE(C2)) turns it into a signed number.

If numbers refuse to sum after any of these fixes, the guide to turning a PDF into Excel with four methods compared covers the routes that avoid the problem.

Which PDF viewers copy tables with tabs?

The viewer matters because each one decides how to write the gaps between words. Some insert tabs between table cells, some insert spaces, and some copy a whole column before the next. Behaviour also changes between versions, so test on your own file instead of relying on a list.

A fifteen-second test

You can find out which behaviour you have without a theory:

1. Copy three rows from the table in your viewer.
2. Paste them into a blank Excel sheet.
3. If the cells split across columns, your viewer writes tabs. If they land in one column, it does not.
4. Try again from a second viewer, for example a web browser's built-in PDF reader against a desktop reader.

Different viewers can give different results for the same file, and a quick test of two or three is cheaper than a long fix.

What else changes the result

• Selection method. Dragging across rows with the mouse selects by reading order. Some viewers have a separate rectangle selection tool that selects an area, and this sometimes keeps columns apart.
• Table type. A table with printed lines tends to copy more cleanly than one with none, for the reasons covered in how table detection tells ruled tables from whitespace tables.
• Protected files. Some PDFs block copying altogether, and the paste is empty or garbled. If copying is disabled, a scanned file or a protected file needs a different route.

If the paste is garbled text

When the pasted text contains odd symbols or letters in the wrong order, the PDF uses an embedded font without a proper text map. The words look right on screen and copy as nonsense. No paste option repairs this, and a converter that reads the same text gets the same nonsense. Optical character recognition (OCR), which reads the page as a picture, is the usual way round it.

Copying multi-page PDF tables by hand

Copying a table that runs over many pages by hand wastes time once it passes a few pages, because every page needs its own copy, its own paste and its own clean-up of headers and page numbers. The work scales with pages, and the errors scale with it.

How to do a short table well

For two or three pages, copy one page at a time and paste each below the last. Paste at the first empty row each time. Then delete the repeated header rows and any page number lines, and check the final row count against the PDF. For a longer table, converting only the pages that hold the table saves the copying altogether.

When to stop copying

Stop when any of these is true:

• You have more than about five pages to do.
• The same layout will arrive again next month.
• You are fixing the same split on every page.
• The numbers have to be right, such as for a reconciliation, and you cannot verify them quickly.

A manual process also has no audit trail. If a number looks wrong a month later, you cannot tell whether the PDF said so or a paste did.

What to use instead

A converter takes all the pages in one run, which leaves one clean-up instead of one per page. If you only need a few values, such as a total or a date, pulling single fields from a PDF is lighter than moving the whole grid. If you write a little code, a Python script that reads the PDF and writes an Excel file turns a repeated chore into one command.

For bank statements, FastBankConvert is built to read a statement's pages and return dates, descriptions, amounts and balances in their own columns. It is not open yet, and the get-started page takes your email for when trial conversions open.