A PDF does not store a table, only text placed at positions on a page, so copying it into Excel gives you one cell per line. You can rebuild the columns with Text to Columns using spaces or fixed width, or with a few text formulas. It works for simple statements and breaks easily for long ones, so always check the totals.
Why does everything land in one column?
When you copy from a PDF, the viewer hands over lines of text separated only by spaces. Excel puts each line into column A. The gaps you see between date, narration and amount are spaces, and the narration itself contains spaces too, so there is no single separator that splits every line correctly.
How does Text to Columns try to fix it?
- Paste into column A and select it.
- Choose Data > Text to Columns and pick Delimited, then tick Space and Treat consecutive delimiters as one.
- The date will separate cleanly, but the narration breaks into as many cells as it has words.
- Alternatively pick Fixed width and click in the preview to place break lines between columns. This works only when the text lines up like a typewriter, which PDF text often does not.
Both ways are fragile. A better approach is to leave the pasted line whole and pull each piece out with a formula.
Which formulas rebuild the columns?
Say a pasted line reads 03-Apr-2026 UPI SWIGGY BANGALORE 450.00 41,550.00 in A2. The date is a fixed 11 characters, the balance is the last word and the amount is the word before it.
| Column | Formula | Result for the sample |
|---|---|---|
| B Balance | =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",30)),30)) | 41,550.00 |
| C Remainder | =TRIM(LEFT(A2,LEN(A2)-LEN(B2))) | The line without the balance |
| D Amount | =TRIM(RIGHT(SUBSTITUTE(C2," ",REPT(" ",30)),30)) | 450.00 |
| E Date | =LEFT(A2,11) | 03-Apr-2026 |
| F Narration | =TRIM(MID(C2,12,LEN(C2)-11-LEN(D2))) | UPI SWIGGY BANGALORE |
Then turn the amounts into numbers with =VALUE(SUBSTITUTE(D2,",","")) and the date with =DATEVALUE(E2). Fill each formula down the list. Excel 365 users can also try TEXTSPLIT, but the formulas above work in every version.
Where does this method break?
- A narration that wraps onto a second line leaves a line with no amount. Join it to the row above by hand.
- Page headers, footers and the repeated column titles appear in the middle of the list. Filter them out.
- Statements with separate Debit and Credit columns paste as a single amount, so you cannot tell which it is. Compare each balance with the one before: if it went up, the amount was a credit.
- Statements that mark Dr or Cr after the amount push the balance one word along.
- Dates written in a different pattern break the fixed 11-character assumption.
Because a single pasted amount does not say whether it is a debit or a credit, let the balance decide. Say the balance before a row is 41,550.00 and after it is 40,350.00. The balance fell by 1,200.00, so the 1,200.00 was a debit. If a later row moves the balance from 40,350.00 to 45,350.00, the 5,000.00 was a credit. In a helper column, =IF(ThisBal<PrevBal,-Amount,Amount) gives a signed amount once both are real numbers.
Run the formulas on three or four rows first and compare them with the PDF before filling down the whole list. A formula that is wrong on one row is wrong on every row, and fixing it on a small sample costs seconds rather than minutes.
Is there a quicker way?
If your Excel has Data > Get Data > From File > From PDF, it reads tables directly and is usually less fragile than pasting. A CSV or Excel version of the statement, if your bank provides one, is better than either. Treat pasting as the last resort for a short PDF, and read every row once.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

