Why this happens
Most of continental Europe writes one thousand two hundred and thirty-four euros fifty-six as 1.234,56, France and the Nordic countries often as 1 234,56, and English-language settings as 1,234.56. A CSV stores whichever version the bank produced, as text, and the program that opens it decides what the characters mean based on your computer’s regional settings.
On a computer that uses a dot for decimals, Excel can’t read 1.234,56 or 950,00 as numbers, so it keeps them as text. SUM, AVERAGE and pivot tables skip text without any error, which is why the total comes out at 0 or too low instead of failing loudly. Worse is a value that happens to fit the local format: an amount written without decimals, such as 2.500, means two thousand five hundred in a German export but is read as two and a half, and looks like a perfectly normal number.
The same mismatch breaks imports. Accounting software and databases that expect dot decimals reject 950,00 as an invalid amount or read it differently from what the bank meant. And because the comma is already taken by decimals, European banks often separate columns with semicolons, so the file opens as a single column when you double-click it.
Fix it by hand
In Excel, with Text to Columns
- 1
Select the amount column and choose Data > Text to Columns.
- 2
Choose Delimited, click Next, clear every delimiter, and click Next again.
- 3
In step 3, click Advanced and set the decimal separator to a comma and the thousands separator to a dot, or to a space if that is what the file uses. These settings describe the file, not your computer.
- 4
Click OK, then Finish. The column becomes real, right-aligned numbers that SUM can add. Repeat for each amount column.
Cells Excel already read the wrong way, such as 2.500, are numbers now and Text to Columns won’t touch them. For those, start from the original CSV and import it through Data > From Text/CSV, using Change Type > Using Locale in Power Query with a European locale.
In Excel, with a formula
- 1
In a helper column, use =NUMBERVALUE(C2,",",".") to read the text in C2 with a comma as the decimal separator and a dot as the group separator.
- 2
Fill it down, then copy the helper column and paste it over the original as values.
NUMBERVALUE is available in Excel 2013 and later.
In Google Sheets
- 1
Before importing, open File > Settings and set Locale to a country that writes numbers the way the file does, such as Germany.
- 2
Import with File > Import, choose semicolon as the separator if the file uses one, and keep the conversion of text to numbers switched on.
- 3
Sheets now stores real numbers. If you switch the locale back afterwards, spot-check a few amounts against the statement.
Fix it with SpreadsheetFix
SpreadsheetFix’s Normalize Numbers rule reads the separators in each cell and rewrites the value as a plain dot-decimal number. When a cell has a dot followed by three digits and also a comma, as in 1.234,56, the dots are treated as thousands separators and the comma as the decimal point. A single comma with no dot, as in 950,00 or -4,90, is read as the decimal point. Spaces between digit groups are removed, and currency symbols such as € and £ are stripped, so “€8.410,20” becomes 8410.20.
Semicolon-separated files are detected when they load, and the cleaned file is written with commas between columns and dots in the numbers, which is what an English-language Excel and most databases and accounting imports expect.
The totals check reads amounts in either style, so it compares each amount column’s total before and after conversion. If the conversion read a value differently from the way the totals check reads it, that column shows as off, with the difference. Normalize Numbers is a Pro rule; the file is converted in your browser and isn’t uploaded.
No file to hand?
The same cleaner is on the Convert Comma Decimals page.
Before and after
date;description;amount;balance 2024-03-01;Stripe payout;1.234,56;€8.410,20 2024-03-04;Office rent;-950,00;€7.460,20 2024-03-05;Bank fee;-4,90;€7.455,30
date,description,amount,balance 2024-03-01,Stripe payout,1234.56,8410.20 2024-03-04,Office rent,-950.00,7460.20 2024-03-05,Bank fee,-4.90,7455.30
Semicolon-separated input, comma-separated output. The amount and balance totals match before and after.
What it won’t catch
- A comma followed by exactly three digits, as in 1,234, is read as a US-style thousands separator and becomes 1234. In a European file that could mean one point two three four, so check three-decimal values such as exchange rates or weights in the preview.
- A value like 2.500 with no comma is left as it is, and dot-decimal software reads it as two and a half. If your file writes whole thousands this way, check those cells.
- The rule applies to every column unless you pick columns, so phone numbers lose their spaces and other numeric codes can change. Use the column picker to limit it to your amount columns.
- Currency codes written as letters aren’t removed, so a cell such as 12,50 EUR is left unchanged.
Questions
Why does Excel SUM return 0 for my numbers?
The cells are text, not numbers. With European formatting such as 1.234,56 in an English-language Excel, the values can’t be read as numbers, and SUM skips text without an error. Convert the column with Text to Columns or NUMBERVALUE, or convert the file before opening it.
How do I open a semicolon-separated CSV in Excel?
Use Data > From Text/CSV and choose Semicolon as the delimiter in the preview window, rather than double-clicking the file.
Should I change my computer’s regional settings instead?
You can, but it changes how every program on the computer reads and writes numbers and dates, including the CSVs you send to other people. Converting the file is usually the smaller change.
Is Normalize Numbers free?
It is part of Pro. Free users can still fix encoding, dates, spacing and duplicates on files up to 5,000 rows.