Import Decimal Commas in Excel Without Changing the Amounts

Identify three separate separator settings

In this file, semicolons separate columns, commas separate whole amounts from fractional amounts, and periods group thousands. Each character has a different job. Choosing Semicolon in the CSV import preview creates the right columns, but it does not by itself explain how to interpret the amount text.

Confirm the source convention using an export specification or an unambiguous example. The string 1,234 alone is ambiguous across locales: it could represent a fractional value or a grouped integer. Do not resolve that ambiguity solely from the settings on the computer receiving the file.

Import the amount as text before conversion

Start a fresh import of the original file and select Semicolon as the field delimiter. Keep amount as Text initially so you can review exactly what the exporter supplied. If an automatic type step already misinterpreted it, remove that step and return to the unconverted values.

Check all three sample strings, including the negative adjustment. If the initial import has already turned 25,75 into a different numeric value, a later formula cannot reliably infer its original punctuation. Return to the original text instead of correcting a value whose interpretation is uncertain.

Convert with explicit formula arguments

With order_id in column A and amount text in B, place numeric_amount in C1. Enter the first formula in C2 and fill down through C4. Use the semicolon variant if that is the argument separator required by your Excel installation. The characters inside quotes still describe the source numeric syntax.

This example deliberately contains no empty amount fields. If your file has blanks, decide whether they mean missing or zero and add a blank-preserving condition where needed; NUMBERVALUE of an empty string returns zero.

=NUMBERVALUE(B2,",",".")

Semicolon argument-separator version:
=NUMBERVALUE(B2;",";".")

Expected C2:C4, shown with period decimals:
1234.50
25.75
-10.25

=SUM(C2:C4)
Expected numeric total: 1250.00

Use a source locale for repeatable imports

For a recurring Power Query import, select the amount text column, open Change Type > Using Locale, and choose Decimal Number plus a locale matching the source convention. German (Germany) matches the example format. Confirm the preview values before loading the table.

Keep the conversion step with the query so future refreshes apply the same interpretation. If the supplier later changes its formatting convention, review the step before accepting the refreshed results. The source locale is a parsing choice; your worksheet can display the resulting numbers using a different local format.

Check magnitude before trusting a total

Verify that the largest amount is 1234.50, the smaller positive amount is 25.75, and the adjustment is -10.25. Their total is 1250.00. A quick magnitude check catches errors that preserve a numeric type while changing the decimal meaning.

Replacing every comma with a period would turn 1.234,50 into 1.234.50, which does not match the intended numeric syntax. Replacing punctuation across the raw file can also alter descriptions or field boundaries. Convert the already parsed amount column under its known convention.

Related help: Check why SUM ignores numbers stored as text

Separate display preferences from source cleanup

Excel can use system separators or custom display separators in its options. Those settings are useful for presentation, but a visible decimal character is not evidence that imported text was interpreted correctly. Check a numeric value and an independent expected amount after any change.

Column Harbor can inspect supported UTF-8 CSV and flag selected import risks. It does not apply a locale, convert amounts, or calculate workbook formulas. Keep the original strings with the verified converted values when preparing a file for another team.