AppGarden UK Bank CSV Cleaner logo AppGarden

UK Bank CSV Cleaner

Paid in and Paid out columns: turn a bank statement into one signed amount in Excel™

By Paul Kemp · Published

Many bank exports have a Paid in column and a Paid out column, each blank on rows where it does not apply. That reads well on a statement, but an accounting import often wants a single amount with money out as negative.

The conversion

One signed amount = paid in minus paid out. The danger is text. An amount that arrived as text, such as £1,250.00, will not subtract, and a formula may quietly treat it as zero.

By hand

These steps apply to Excel™ in Microsoft 365. Work on a copy.

  1. Check both columns are numbers. Select each and look for a sum in the status bar, not just a count.
  2. If they are text, remove symbols and separators first: =VALUE(SUBSTITUTE(SUBSTITUTE(C2,"£",""),",","")). Brackets need handling too, so check for them.
  3. Add a column: =N(C2)-N(D2), with Paid in in C and Paid out in D. N turns blank cells into zero.
  4. Fill down, paste as values, and name the column Amount.
  5. Prove it: the sum of Amount should equal the total of Paid in minus the total of Paid out, taken before you changed anything.
  6. Check the bank's own closing balance against the opening balance plus the sum of Amount, if your statement shows balances.

A row with both columns filled needs a look before you combine it.

Let the pane combine them

UK Bank CSV Cleaner flags Paid in and Paid out in two columns (BK-10) and offers a guided fix that previews the exact cell changes and asks you to confirm. It also offers conversion to a new sheet in the layout of an import package you choose (BK-19). The scan, explanations, conversion to a new sheet and Undo are free; most other fixes are the paid part. It edits the workbook only and never connects to a bank.

See UK Bank CSV Cleaner for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. UK Bank CSV Cleaner is not affiliated with or endorsed by Microsoft, any bank or any accounting software supplier.