4,50 or 4.50? Fixing decimal commas and points in Excel
Short answer
Excel reads numbers with your computer's decimal separator. Where it's a point, 4,50 stays text; where it's a comma, 1.500 becomes one thousand five hundred. Convert text with =NUMBERVALUE(A2, ",", ".") (decimal comma, dot grouping) or =NUMBERVALUE(A2, ".", ",") — or import the CSV with the right locale. The free CSV fixer can rewrite a whole file's decimals in your browser.
Why is 1.500 one and a half in the US, and fifteen hundred in Germany?
The decimal separator and the thousands separator swap roles between regions. 1,234.56 in the US and UK is 1.234,56 in Germany and 1 234,56 in France or Poland. Excel doesn't look at a value and guess which convention it follows — it applies your region's. So the same text means different numbers on different computers, and a number with “the wrong” separator is treated as text.
| Text | Decimal-point Excel (US, UK) | Decimal-comma Excel (DE, PL, FR…) |
|---|---|---|
4.50 | 4.5 | text, or a date |
4,50 | text | 4.5 |
1.500 | 1.5 | 1500 |
1,500 | 1500 | 1.5 |
1.234,56 | text | 1234.56 |
Check your decimal setting
- Windows: Excel uses the Windows region unless you change it: File › Options › Advanced › Use system separators.
- Mac: Excel follows the Mac's region and number format (System Settings › General › Language & Region).
- Quick test: type
=1/2in a cell — Excel shows0.5or0,5.
Fix numbers that are already in the sheet
- NUMBERVALUE converts text in any format, whatever your region:
=NUMBERVALUE(A2, ",", ".")reads1.234,56;=NUMBERVALUE(A2, ".", ",")reads1,234.56. - Find and Replace for a column with only plain decimals: replace
,with.(or the other way), then convert the column (how). Don't do this on grouped numbers like1.234,56— remove the grouping first. - Be careful with values Excel already converted: if
1.500became 1500 but meant 1.5, the original is lost — re-import the file instead.
Open the CSV with the right locale
Import instead of double-clicking: Data › From Text/CSV, then Transform Data. Right-click the number column's header, choose Change Type › Using Locale…, then pick the number's type (Decimal Number) and the locale it was written in (e.g. German (Germany) for 1.234,56). Excel converts each value using that locale, whatever your own region. Semicolon-separated files from decimal-comma regions also need the right separator — see CSV opens in one column.
Why a scanned amount may stay text
QuickScan reads the table's own format from its amounts: a page full of 12,50 and 1.234,56 is a decimal-comma document, so its Excel file stores those as numbers. A value like 1.500 on its own is ambiguous — one and a half, or fifteen hundred — so unless the rest of the table settles it, QuickScan keeps it as text rather than guess. Check those cells against the paper and convert them with NUMBERVALUE. QuickScan also corrects separators that text recognition misreads (a 0,00 read as 0.00 on a decimal-comma page), using the same evidence.
On a computer? Point your iPhone's camera at the code to open QuickScan in the App Store.
3 free exports, then a one-time unlock. No subscription.
Questions
Why does Excel treat 4,50 as text?
Your Excel uses a decimal point (it follows your computer's region), so 4,50 isn't a number to it. Convert with =NUMBERVALUE(A2, ",", "."), Find and Replace, or import the file with the right locale.
Why did 1.500 become 1500?
In a decimal-comma region the dot groups thousands, so 1.500 means one thousand five hundred. If the source meant one and a half, import the file with a locale that uses a decimal point.
How do I change Excel's decimal separator?
Windows: File › Options › Advanced, untick 'Use system separators' and set the decimal and thousands separators. This affects how you type and see numbers in Excel; it doesn't rewrite text that's already in cells.
Which countries use a decimal comma?
Most of continental Europe (Germany, France, Italy, Spain, Poland, the Netherlands…), much of South America, and others. The US, UK, Ireland, Australia, Japan and China use a decimal point.