Excel Text to Columns turned your bank statement into a mess

Text to Columns rarely splits pasted statement text into the columns you can see on the page, and on a statement laid out with a free-text description in the middle of the row, no combination of delimiters, break positions or column formats will rescue it. This is not a settings problem you have not solved yet. It is a mismatch between what the tool does and what pasted statement text looks like, and knowing that now saves you the hour you were about to spend trying every checkbox in the wizard.

One exception is worth ruling in or out before you read any further, because it takes ten seconds. If your bank hands you a genuinely delimited download, a file with a tab or a comma sitting at every field boundary, Text to Columns is the correct tool and it will work first time. The wizard's own preview pane tells you which case you are in: tick the delimiter, scroll the preview, and if the columns stay lined up across every visible row you have a delimited file and nothing here applies to you. Everything below is about the other case, the one that brings people to this page, where the text was copied out of a statement document laid out in columns for a human reader rather than for a parser.

The symptom in that case is always the same. You paste a statement's transaction list into a sheet, run Text to Columns, and the result is a staircase: one row has the payee in column B, the next has it spread across B, C and D, and the amounts sit under a different heading on almost every line. Sorting is impossible, summing is impossible, and the running balance you wanted to check against is scattered across four columns.

What Text to Columns actually does

Text to Columns has exactly two modes, and it is worth being precise about both because the failure follows directly from their definitions.

Delimited mode looks for a character you nominate, splits the text at every occurrence, and puts each fragment in the next column to the right. It has no concept of which fragment is a date and which is an amount. It counts separators and moves right.

Fixed width mode ignores content entirely and cuts at character positions you place by hand. Every row is cut at the same positions. It has no concept of a row whose values sit slightly differently from its neighbour's.

Neither mode looks at what a value is. That is the whole story.

Why a description column defeats delimiter splitting

A bank statement row has a variable-width field in the middle of it. The date is fixed in shape. The amount and balance are fixed in shape. The description is not: it can be one word or twelve, and it changes length on every row.

Delimiter splitting only holds together when the delimiter appears at field boundaries and nowhere else. A properly formed CSV manages that by quoting any field that contains the delimiter, so the parser can tell a boundary from a character. Text copied out of a statement document carries no quoting at all, which means an occurrence of the delimiter inside a description is indistinguishable from a real boundary, and the number of columns changes from row to row:

• Space as the delimiter. A row reading TESCO STORES 4471 produces three cells. A row reading DIRECT DEBIT BRITISH GAS ENERGY produces five. The amount that followed each of them now lands in a different column, so column D holds an amount on one row and the word ENERGY on the next.
• Comma as the delimiter. Descriptions contain commas, and so do amounts with thousands separators. A balance of 12,450.00 splits into two cells, shifting everything after it. This is the same underlying failure that makes naive conversion of statement documents to CSV produce rows with more fields than the header.
• Tab as the delimiter. Tabs work when they exist at the column boundaries, and a full PDF application will sometimes reconstruct the table on copy and insert them. Most pastes, and almost all pastes out of a browser viewer, do not contain them: you get one long text run per line with spaces where the visual gaps were. Tick Tab in the wizard and look at the preview before you assume either way, because this is the one delimiter that is occasionally handed to you for free.

There is rarely a fourth option hiding in the Other box. Any single character you choose either turns up inside the description or fails to turn up at the boundaries, and on pasted statement text it is usually both at once.

Why fixed width does not rescue it either

Fixed width is the intuitive fallback, because on screen the statement's columns clearly do line up. In the source document they do line up, visually. That alignment is produced by positioning each piece of text on the page, not by padding it with spaces.

When you copy that text out, the positioning information is discarded. What lands in the clipboard is a sequence of characters with whatever spacing the extractor decided to insert, and that spacing depends on the font, the gap widths and the extractor's own guesswork. Right-aligned amount columns are the worst affected: a four-figure amount and a six-figure amount end at the same character position on the page but start at different ones, and after extraction they often do not even end at the same position.

So the break lines you place will be right for the rows you looked at while placing them and wrong for the rest. You will find this out at row 200.

What actually works

Three approaches produce a usable table. Judge each one by the same standard: at the end, can you show that the opening balance plus every movement equals the closing balance printed on the statement? That check is what catches a dropped row or a misread amount without you reading every line, and it is the check Text to Columns leaves you no way to run, because it scatters the balance column that makes the arithmetic possible.

Power Query from a PDF

Current Excel for Windows can import from a PDF through the Get Data menu, and Power Query will find tables on the pages and let you pick which ones to load. On a statement whose pages contain real, selectable text laid out as a proper table, this works well and is repeatable month to month.

It has two hard limits. It has nothing to work with on a scanned or photographed statement, because the connector reads a text layer and a scanned page does not carry one, so recognition has to happen upstream in a different tool. And availability differs across platforms and licence types, so check what your own Get Data menu offers rather than assuming from a screenshot you saw elsewhere.

Even when it loads, expect to do work in the query editor: statements interleave headers, page footers, carried-forward lines and sometimes check images between the transaction rows, and all of those arrive as rows you have to filter out.

Anchor from both ends instead of splitting

If you are doing this by formula on text you already have in a sheet, stop trying to split left to right. Parse inward from both ends, because the fields at the ends are the predictable ones.

1. 1

   Take the date from the left

   The first token of each row is the transaction date. Its shape is fixed, so a fixed-length extraction from the start of the string is safe.
2. 2

   Take the balance from the right

   The last numeric token on the line is usually the running balance, and working backwards from the end of the string is more dependable than working forwards because the fields at that end have fixed shapes. Read a page of rows before you commit to it: some layouts print a CR or DR marker, a footnote asterisk or a branch code after the balance, and where they do you strip that trailing marker first.
3. 3

   Take the amount as the second token from the right

   The amount sits immediately before the balance on most layouts. Confirm this on a handful of rows before applying it to the sheet.
4. 4

   Treat everything left over as the description

   Whatever remains between the date and the amount is the description, spaces and all. You are not splitting it, you are keeping it whole, which is the correct outcome.
5. 5

   Check the arithmetic on every row

   Add a column computing the previous row's balance plus the current amount, and compare it to the current balance. Any row where those disagree is a row your parsing got wrong.

The newer text functions in Microsoft 365 make the right-hand extraction much easier than the older combinations of FIND, LEN and MID, though they are not present in every version. Whichever functions you have, the principle stands: anchor on the fields whose position you can trust, and let the variable-width field be whatever is left.

This method is real work, and it is worth doing once for a single statement. Doing it monthly for a portfolio of clients is where the true cost of manual statement data entry stops being theoretical.

Use a converter that understands statement structure

This is the approach that clears the standard set above without you building the check yourself. Power Query and a formula column can both be made to run the same reconciliation, and the steps above show you how; the difference is that you have to construct it, maintain it, and reconstruct it the month the bank changes its layout.

A tool built for statements does not look for delimiters at all. It identifies the table by position on the page and by what each column contains, so the description stays in one field regardless of whether it holds one word or twelve, and the amount stays a number regardless of the thousands separator inside it. Then it does the thing no spreadsheet trick does: it uses the running balance as a verification pass rather than discarding it, checking every row against the one before so a misread digit or a dropped line surfaces immediately instead of during reconciliation.

That is the whole difference. Text to Columns hands you a table and no way to know whether it is right. A statement-aware conversion hands you a table with the balance chain already checked against the figures the bank printed, and that is the strongest automatic check available on extracted statement data. It catches the error class that matters most, a wrong digit in an amount, because a misread amount breaks the chain arithmetically and cannot hide. It does not verify dates or descriptions, and two errors that offset one another exactly can survive it, so a glance at the largest amount and a handful of descriptions is still a minute well spent. What it removes is the class of error you would otherwise meet for the first time at reconciliation.

Verify before you use the result

Whichever route you take, run the same two checks. They take a minute and they catch the errors that otherwise surface during reconciliation.

Stated plainly: the opening balance plus the sum of your amount column must equal the closing balance printed on the statement, and your row count must equal the number of transactions the statement lists. If the sum is off, something has moved. A row was dropped, an amount landed in the wrong column, a debit picked up the sign of a credit, or the opening figure you started from was the previous period's closing balance rather than this period's opening one. Confirm you took the opening and closing figures off the right lines before you start hunting through the rows, because that mistake accounts for a surprising share of the sums that refuse to tie. If the row count is off but the sum matches, you have probably duplicated and dropped in equal measure, which is worse.

The largest amount on the statement is the single most useful spot check, because it is the value most likely to contain a thousands separator, and thousands separators are precisely what delimiter splitting mangles. If your choice of output format matters downstream, the comparison of statement export formats covers which one your accounting software will actually accept.

Text to Columns is a good tool for data that carries real delimiters at real boundaries. Text pasted out of a statement document does not, and recognising which of the two you are holding is the difference between an afternoon of wizard settings and a table whose totals you can stand behind.