Bank CSV amounts will not add up: £ signs, commas and text numbers
You select the Amount column of a bank CSV and the status bar shows a count, not a sum. The numbers are text. A bank file is full of the things that cause it: a £ sign, a thousands comma, brackets for a negative, a space before the minus.
How to spot it
- Amounts sit on the left of the cell, not the right.
- Select the column: a count appears, but no sum.
=SUM(C2:C100)returns 0, or leaves out some rows.- A number looks fine but a cell that looks blank is a space, which counts as text.
Check by hand
Use Edit › Find and replace with Also search within formulas off to remove £ and thousands commas, then change the column to Format › Number. Brackets such as (25.00) need turning into -25.00 by hand, and any comma-as-decimal value needs a decision, because 1,234 could be one thousand or one and a bit.
What the add-on does
It flags amounts stored as text (BK-09): symbols, separators, brackets and a spaced minus. The preview lists the exact cells before anything is written, columns that are text by nature (a sort code, an id) are skipped, and a comma used as a decimal is a guided fix that asks you first.
A second shape has its own finding. Where some amounts are text whole pounds (£1) beside amounts with pence (£0.00), BK-08 offers to turn them into numbers shown with two decimals. It acts on text cells only and never on a real number.
Stray spaces, including in cells that look blank, are BK-20, and that fix is free to apply. The scan and every explanation are free, other fixes are Pro, and Undo is free in every tier.
Check the file before you trust it
UK Bank CSV Cleaner scans a UK bank CSV, or a sheet you are about to upload, and explains every finding. The full scan, every explanation and Undo are free, and your data stays in your Google™ account. It edits the spreadsheet only and never connects to your bank or to an accounting package.
About UK Bank CSV Cleaner — not yet on the Google Workspace™ Marketplace.
Google, Google Sheets™ and Google Workspace™ are trademarks of Google LLC. Not affiliated with, endorsed by or sponsored by Barclays, HSBC, Lloyds Banking Group, Halifax, Bank of Scotland, Starling Bank, Monzo, Xero Limited, Intuit Inc. or The Sage Group plc. Barclays, HSBC, Lloyds, Halifax, Bank of Scotland, Starling, Monzo, Xero, QuickBooks and Sage are trademarks of their respective owners and are used only to describe compatible file formats. UK Bank CSV Cleaner is not affiliated with or endorsed by Google.