How to Fix Decimal-Comma Numbers When a CSV Opens in Excel

To fix decimal-comma numbers from a CSV in Excel, do not open the file by double-clicking — import it with Data > From Text/CSV, click Transform Data, and set the column's locale to one that uses comma decimals (like German or French) so 1.234,56 becomes the number 1234.56. The problem is a locale mismatch: much of Europe writes one thousand two hundred thirty-four and fifty-six hundredths as 1.234,56, while English Excel expects 1,234.56. Get the import right and every value lands as a real number. (Starting from scratch? Run the file through the free CSV to Excel converter first, then apply the import fix below.)

Why This Happens

Numbers are not universal — their punctuation is a local convention:

• English (US/UK): decimal point, comma thousands → 1,234.56
• Much of continental Europe: decimal comma, point (or space) thousands → 1.234,56 or 1 234,56
• Switzerland (some cantons): apostrophe thousands → 1'234.56

A CSV is just text; it carries no information about which convention it uses. When Excel opens 1.234,56 on an English-locale system, it cannot parse it as a number: the comma is not a valid decimal mark in English, so the value becomes text (left-aligned, ignored by SUM) — or worse, Excel "helpfully" reads 1.234 as one-point-two-three-four and drops the ,56 entirely. Both outcomes silently corrupt financial data.

This is distinct from the delimiter problem (semicolons vs commas separating columns), which the semicolon delimiter guide covers. European CSVs often have both issues at once: semicolon delimiters and comma decimals. Fix the delimiter first, then the numbers.

Step 1: Diagnose What You Have

Before fixing anything, look at the raw CSV in a text editor (Notepad, VS Code — not Excel). You need to answer three questions:

1. What is the decimal mark? Look at a value with cents: 1234,56 means comma decimals; 1234.56 means point decimals.
2. What is the thousands separator? 1.234,56 (point thousands) vs 1234,56 (no thousands separator) vs 1 234,56 (space thousands). This determines which fix is safe.
3. Is the data consistent? Scan several rows. A file that mixes 1.234,56 and 1234.56 needs row-by-row attention — no bulk fix is safe.

Also check whether currency symbols are embedded in the values (€1.234,56), since those must be stripped before numeric conversion. The currency formatting guide covers symbol cleanup.

Step 2: The Correct Fix — Import with the Right Locale

The clean, reliable method is to tell Excel which locale the CSV came from during import:

Power Query method (recommended):

1. Data > From Text/CSV, select the file.
2. In the preview, click Transform Data (not Load).
3. Select the affected numeric column(s).
4. Go to Transform > Data Type > Using Locale.
5. Set Data Type to Decimal Number and Locale to a comma-decimal locale matching your file — German (Germany) or French (France) work for most European CSVs.
6. Click OK, then Home > Close & Load.

Power Query now interprets 1.234,56 correctly as 1234.56 and loads real numbers into Excel. Bonus: the query is saved, so next month's CSV from the same source refreshes with one click.

Text Import Wizard method (older Excel):

1. Data > From Text/CSV, select the file, click Import.
2. In the preview dialog, click Transform Data to open Power Query (same as above), or use the legacy wizard via Data > Get Data > Legacy Wizards > From Text if enabled.
3. The legacy wizard's Advanced button lets you set the decimal and thousands separators directly: decimal ,, thousands ..

Either route beats every find-replace hack because the conversion happens during parsing, when Excel still knows which characters are separators and which are decimals.

Step 3: Fix a File That Is Already Open Wrong

If someone already double-clicked the CSV and saved the damage, the values are sitting in Excel as text (or wrong numbers). Options:

NUMBERVALUE formula (surgical, safe):

=NUMBERVALUE(A2, ",", ".")

NUMBERVALUE takes the decimal separator and thousands separator as arguments: here, comma decimal and point thousands. It converts 1.234,56 text to the number 1234.56 regardless of your system locale. Fill down, then paste-as-values over the original column.

For space-separated thousands (1 234,56), strip the spaces first:

=NUMBERVALUE(SUBSTITUTE(A2," ",""), ",", ".")

Text to Columns with locale trick:

1. Select the column, Data > Text to Columns.
2. Choose Delimited, click Next twice (select no delimiters).
3. In step 3, click Advanced, set Decimal separator to , and Thousands separator to ..
4. Set column format to General, click Finish.

This converts the whole column in place — no helper column needed.

Step 4: The Find-Replace Method (and Its Traps)

Find-replace can work but the order of operations is critical. Get it wrong and you destroy the data:

**Safe sequence for 1.234,56 → 1234.56:**

1. First replace the thousands separator with nothing: find ., replace with ` (empty). 1.234,56 → 1234,56`.
2. Then replace the decimal comma with a point: find ,, replace with .. 1234,56 → 1234.56.
3. Convert text to numbers: select the column, and use Text to Columns (General) or multiply by 1 in a helper column.

Why the order matters: if you replace the comma first, 1.234,56 becomes 1.234.56 — two decimal points, unparseable garbage. Thousands first, decimals second. Always.

When find-replace is unsafe: if the file has no thousands separators (1234,56), step 1 is harmless but pointless — skip to step 2. If the file mixes formats (some rows 1.234,56, others 1234.56), find-replace will corrupt the point-decimal rows. If any column contains text with commas (addresses, descriptions), a global replace mangles it — restrict the replace to the numeric columns only.

Honest advice: find-replace is the quick fix people reach for and the number-one source of decimal-comma data corruption. Prefer the locale import (Step 2) or NUMBERVALUE (Step 3).

Comparison: Which Method to Use

When the Fix Breaks: Troubleshooting

Values still left-aligned (text) after the fix. The conversion did not take — usually because a hidden character (non-breaking space as thousands separator, common in French CSVs) survived. Use =CLEAN(TRIM(A2)) in a helper column first, then convert.

SUM returns 0 on the "fixed" column. The cells are still text. Excel sums text as zero with no warning — the most dangerous symptom. Select the column and check the status bar: if it shows no average/sum when you select numbers, they are text. Run Text to Columns (General) over them.

Some rows converted, others did not. Mixed formats in the source file — typically a file assembled from two systems. Filter for the unconverted rows, inspect them in the raw CSV, and handle each format group separately.

Currency symbols block conversion. €1.234,56 will not convert until the € is gone. Strip symbols with find-replace on the column (symbols only, not punctuation), then convert. Or handle it in Power Query: replace values, then set type with locale.

Negative numbers in parentheses. Some European exports write negatives as (1.234,56). Excel's locale import may not recognize this. Convert with =IF(LEFT(A2,1)="(", -NUMBERVALUE(MID(A2,2,LEN(A2)-2),",","."), NUMBERVALUE(A2,",",".")).

Dates in the same file are also wrong. European CSVs often pair comma decimals with day-month-year dates (15.03.2024). Fix dates separately with the locale-aware date import — do not let a number fix touch date columns.

The file uses semicolons AND comma decimals. Totally normal for European exports. Import with delimiter set to semicolon first (see the semicolon delimiter guide linked above), then apply the decimal-comma locale fix to numeric columns. Two settings, one import.

FAQ

Why does Excel not just detect the format automatically?

A CSV contains no locale metadata — 1.234,56 is genuinely ambiguous without knowing the source country. Excel guesses based on your system locale, which is wrong whenever the file comes from a different convention. Explicit locale import removes the guesswork.

Will changing my Windows region settings fix it?

It would, but do not. Changing your system locale to German fixes the CSV and breaks everything else that expects English formatting. Fix the import, not the operating system.

Can I prevent this at the source?

Sometimes. If you control the export, choose point-decimal formatting or export as .xlsx instead of .csv. If the CSV comes from a vendor or bank, you are stuck with their format — build the Power Query once and reuse it.

Does this affect formulas that reference the cells?

Yes — a formula referencing text-that-looks-like-a-number either errors or computes wrong. Always verify the column is truly numeric (status bar shows a sum) before building formulas on it.

My file has comma decimals but point thousands — is that possible?

It happens with data assembled from multiple sources, but it is rare and suspicious. Treat it as mixed-format data: verify each row group against the raw source before converting, because no single locale setting handles both conventions at once.

Why do my formulas show #VALUE! after conversion?

The conversion produced text in some cells — often where a stray character (a space, a symbol, a letter) hid among the numbers. NUMBERVALUE and Text to Columns both fail loudly on those cells instead of silently. Find the offending characters with LEN() comparisons or by filtering for left-aligned cells, clean them, and convert again.

Preventing the Problem in Shared and Recurring Workflows

Fixing decimal commas once is a chore; fixing them every month is a process failure. If these CSVs arrive regularly, build the fix into the workflow:

1. Save the Power Query. The locale import from Step 2 is stored in the workbook. Next month, drop the new CSV in the same folder with the same filename (or point the query at the new file via Data > Queries & Connections > Edit) and hit Refresh. The decimal-comma handling replays automatically.
2. Document the source convention. Add a README sheet to the workbook noting "Source: German vendor export — comma decimals, point thousands, semicolon-delimited." Six months from now, nobody will remember, and the note prevents a colleague from "fixing" the import settings.
3. Validate on arrival. Add a check cell with =COUNT(range)-COUNTIF(range,">-1E308") style logic, or simpler: a conditional-format rule highlighting text values in numeric columns. If next month's file changes format, the check screams before the bad numbers reach any report.
4. Push back upstream when you can. If the CSV comes from a vendor portal with export options, look for a locale or "English number format" setting. One settings change at the source permanently deletes a monthly cleanup step. Banks and large platforms often have it buried in export preferences.

The broader lesson applies to every CSV quirk: the cheapest fix is at the source, the second cheapest is an automated import, and the most expensive is manual repair after the damage is in the workbook.

60-Second Checklist

• Raw CSV inspected in a text editor: decimal mark and thousands separator identified.
• Imported via Data > From Text/CSV with Transform Data, not double-clicked.
• Numeric columns converted with the correct locale (comma decimal).
• Status bar shows a sum when numeric cells are selected (proof they are numbers).
• Find-replace, if used, ran thousands-first then decimals, on numeric columns only.
• Dates and currency symbols handled as separate steps.
• Power Query saved for next month's file from the same source.