UK Bank Statement to Excel: Paid In, Paid Out, Dates

To convert a UK bank statement to Excel, turn the PDF into rows, merge the Paid in and Paid out columns into one signed amount, and make sure dates are read day first, so 03/04 means 3 April. Then strip pound signs and commas, move codes such as DD into their own column, and check the closing balance.

Paid in and Paid out columns on a UK bank statement

Most UK current account statements print two money columns, usually labelled Paid in and Paid out, or Money in and Money out, followed by a Balance column. Each row has a figure in only one of the two. In Excel, the cleanest result is one signed column: Paid in as positive, Paid out as negative.

Merging the two columns

With Paid out in column C and Paid in in column D, type this in E2 and fill down:

=N(D2)-N(C2)

The N function turns a blank cell into zero and leaves a number as it is, so rows with only one figure work without errors. If the columns still hold text such as £45.00, clean them first, as described further down.

Rows with no amount

UK statements often print a brought forward or carried forward line at the top and bottom of each page, with a balance but no amount. Those rows are not transactions. Keep the first brought forward balance as your opening figure and delete the rest. Leaving them in does not change the sum, but it does add phantom rows to any count or pivot table.

The balance column

The printed balance after each row lets you check the sheet line by line. Start from the opening balance, add each signed amount, and compare with the printed figure. The method, and how to read the differences it finds, is set out in the running balance formula guide.

Why does Excel swap the day and month on UK statement dates?

Excel reads a typed or pasted date using the computer's regional settings. On a machine set to United States formats, a UK date like 03/04/2026 becomes 4 March instead of 3 April, and a date like 25/04/2026 stays as text because there is no 25th month. Mixed results in one column are the warning sign.

Spotting the problem

Sort the date column. If April and March rows interleave, or some dates are left-aligned while others are right-aligned, the column holds a mixture of swapped dates and text. A statement covering one month should show one month after sorting.

Fixing it

The safe route is to rebuild each date from its parts. If the raw text 03/04/2026 sits in A2, this formula returns 3 April 2026 on any machine:

=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))

UK statements also print dates like 03 Apr or 3 Apr 26. For those, =DATEVALUE(A2&" 2026") works on many setups, but check the results, since DATEVALUE also follows regional settings. Where the year is missing, take it from the statement period in the header, and watch for a December to January statement.

Importing instead of pasting

When you bring a CSV into Excel through Data, From Text/CSV, you can set the file origin and the column's date format to DMY before loading. That stops the swap at the door rather than repairing it later. Formatting the result as yyyy-mm-dd makes the order impossible to misread.

Pound signs, commas and overdrawn balances in UK statement amounts

UK statement amounts often arrive as text because of the pound sign and thousands separators, such as £1,250.00. Excel cannot sum text, so each amount needs the symbol and commas removed before it becomes a number. Overdrawn balances add a further twist, because some banks mark them with a letter rather than a minus sign.

Cleaning an amount

For a text amount in C2, this returns a number:

=VALUE(SUBSTITUTE(SUBSTITUTE(C2,"£",""),",",""))

If your machine uses a different decimal separator, Excel may misread the full stop. In that case use NUMBERVALUE, which takes the separators as arguments: =NUMBERVALUE(SUBSTITUTE(C2,"£",""),".",",").

Overdrawn markers

Some UK statements show an overdrawn balance with a suffix such as D or OD, or with a minus sign. A balance of 120.40 D means you owe the bank 120.40, so it should become -120.40. Check the key or notes on your own statement for the exact marker, then convert it with an IF test on the last characters before cleaning the number.

BACS, DD, SO and other UK statement payment codes

Many UK statements put a short code in front of, or beside, each description to show how the money moved. DD is a Direct Debit, SO a standing order and BACS or BGC a credit paid through the Bacs system. Your bank's statement key is the authority for its own codes, since banks vary.

What the main ones mean

Bacs, which is operated by Pay.UK, runs both Direct Debit and Bacs Direct Credit. Its site says Bacs Direct Credit pays eight in ten UK employees. So a salary line marked BACS or BGC is usually a Bacs credit, and a DD line is a collection a company took under a Direct Debit you set up.

The table lists codes commonly printed on UK statements, as a guide only.

A Direct Debit is collected by a company and a standing order is sent by you, so tag them separately when you review regular outgoings. The difference, and the codes other countries use, is set out in the guide to automatic payment codes.

Can you split the UK payment code into its own column?

Yes. When the code is the first word of the description, a formula can lift it into a Code column and leave the payee behind. That lets you filter all Direct Debits at once, total standing orders, or sort payees without the code getting in the way.

A worked example

The rows below are examples with invented payees, dates and amounts, shown after conversion.

With the raw description in B2:

Code:  =LEFT(B2, FIND(" ", B2&" ")-1)
Payee: =TRIM(MID(B2, FIND(" ", B2&" ")+1, 200))

Appending a space inside FIND stops an error when a description has only one word.

When the code is not first

Some banks print the code in its own column, at the end of the description, or as a symbol. If the code is in its own column after conversion, there is nothing to split. If it sits at the end, use RIGHT and the position of the last space instead. A sorted Code column shows quickly whether the formula caught every row: anything that is not a short code points to a description the formula misread.

How a converter decides where a description starts and ends, and why wrapped payee names can pull the code onto a second line, is explained in how a bank statement parser joins wrapped lines.

UK statements from Linux, older files and joint accounts

Some UK statement jobs fall outside the usual Windows-and-Excel path: converting on a Linux machine, working with years-old statements that use a different layout, or handling a joint account. Each one changes a step in the process rather than the method itself.

On Linux

LibreOffice Calc opens the converted file fine, and its text import lets you set the date column to DMY explicitly. The guide to converting PDF to Excel on Linux covers the tools available there.

Older layouts

A bank may have redesigned its statement in the years you need, so the column names or code style can change partway through a run. Check the first month of each year rather than assuming one layout holds throughout.

Joint accounts

A joint account produces one statement in both names, so it converts like any other and needs one balance check. If the account holders split costs, add a Paid by or Share column rather than a second copy of the rows, so the totals still tie to the statement.

Other countries in the same workbook

If you also hold accounts abroad, keep a Currency column from the start. Australian statements share the day-first date habit, Canadian ones mix date orders, and both bring their own codes and layouts, set out in the guides to Canadian bank statements and Australian bank statements in Excel.

Converting the file

FastBankConvert is getting ready to open and is built to read a statement's layout rather than one bank's template, giving you an Excel, CSV or JSON file with each field in its own column. Get started to hear when trial conversions open.