Bank statements

Why decimal commas break a converted bank statement, and how to repair it (EU)

OCR output can mix 1.234,56 and 1,234.56. Learn to spot decimal and thousands separators going wrong and repair them in a sheet, with euro examples.

Editorial illustration for Why decimal commas break a converted bank statement, and how to repair it (EU)

When a scanned statement is converted to a sheet, amounts often arrive as text with the wrong separators. In many European layouts a comma is the decimal mark and a full stop separates thousands, while the sheet you open it in may expect the opposite. One wrong assumption turns €1.234,56 into 1.23 or into text that will not add up.

How can I tell what you have?

Type =SUM() over the amounts column. If the result is zero or far too small, the cells are text. Another sign is that the numbers sit on the left of the cell, as text does, instead of the right. Click one and read the formula bar to see exactly what characters were recognised.

Three examples of what can arrive

As printedAs recognisedProblem
€1.234,561.234,56 (text)Sheet cannot read it as a number
€1.234,561.23456 or 1.23Separators read as decimal points
€87,4087,40 (text)Comma not read as a decimal

Repair step by step

First check your sheet's locale. In Excel it follows your system's regional settings; in Google Sheets, open File, Settings and look at the locale. If the locale uses a decimal point, text such as 1.234,56 must be converted. Assuming the text sits in A2, a formula that works in a point-decimal locale is:

  • =VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",","."))
  • The inner SUBSTITUTE removes the thousands full stop, so 1.234,56 becomes 1234,56.
  • The outer SUBSTITUTE turns the comma into a decimal point, giving 1234.56.
  • VALUE then turns the text into a true number, 1234.56.
  • In a locale that already uses a decimal comma, skip the formula, and instead replace only the stray full stops that sit between thousands.

Copy the formula down, then use Paste special as values to keep the numbers and delete the originals.

Do I also need to look for misread digits?

Yes. Separators are only half of it. Recognition software confuses 1 and 7, 5 and 6, 0 and 8, and sometimes inserts a space inside a number. After you repair the format, check the numbers. Compare the total of debits with the printed total, and the closing balance with the opening balance plus movements. Say opening is €2,310.40, money in is €1,900.00 and money out is €2,046.15; closing should read 2,310.40 + 1,900.00 - 2,046.15 = €2,164.25.

What if a column is a mix of text and numbers?

Some cells will convert on their own and others stay as text, because the recognition tool treated them differently. Filter the column for text, or use ISTEXT(A2) in a helper column, then run the repair only on the cells that need it. Running it on a real number can damage it.

Dates deserve the same care as amounts. A file might show 03/04/2025 meaning 3 April, and a sheet set to a month-first locale will read it as 4 March. Test with a date whose day is above twelve, such as 27/04/2025, because it can only be read one way, and if it fails to convert you know the locale is wrong.

If you convert many pages, keep one sheet per statement month rather than one giant list. Mistakes are easier to find in a small block, and your checks of opening and closing balances are clean for each period.

Before you rely on the file

  1. Make sure every amount is a true number that adds up.
  2. Check the dates read the way you expect, day before month.
  3. Compare totals and balances with the printed statement.
  4. Save the original scan next to the cleaned file.

Rules differ between banks, so use this only as a record-keeping method.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports