Excel changes decimal separator when importing data

Question

Hello, I'm having a problem with decimal separators in Excel, that I can't seem to figure out. 24 hours ago I had no problems, but my system (win 10) got installed clean and since then, the problem came up.

The problem is this: my system is configured to use a dot as a decimal separator. Everything works fine, if I do formulas in Excel that use decimals, or that generate decimals, a dot is used. If I type in a formula with a comma (=1,4+1,6), I get an error message which is fine. Everything works fine with the decimal separators.

Until I go to import data from a CSV file. The CSV file has decimals with a dot as the separator. Should work just fine, but it doesn't. Excel imports the decimals as text, and when I correct the type in the query window, I get a "We couldn't convert to number" message. Strangely enough, the error shows the value it couldn't convert.... which is precisely a value that has a dot as a decimal separator.

The data in question is this:

If I switch the CSV file to use a comma as a separator, it works just fine. Doesn't even need me to correct the column type, Excel gets it as a decimal on its own. I don't change anything in the regional settings... I just switch the CSV file and everything goes fine, against all configured settings for the separator.

My regional settings are configured for dot as a separator, Excel is doing all it's operations with a dot as a decimal separator... but refuses to recognize the dot as a separator when importing CSV data.

I went into File - Options - Advanced to check the separator. Initially it was set to use system separators... and had a comma in the window. I have no idea why. So I unchecked and switched to a dot. Nothing. Problem wasn't fixed, Excel still looks for a comma as a separator when importing data.

Any ideas?

Windows 10 Pro

Version 21H2

OS Compiled 19044.2130

Experience Windows Feature Experience Pack 120.2212.4180.0

Microsoft® Excel® for Microsoft 365 MSO (Version 2210 Build 16.0.15726.20070) 64-bit

Answer 1

Dear Mario_133,

Good day! Thank you for posting in Microsoft forum.

I see that your Excel is not respecting the decimal settings of your system regional settings. To narrow down the issue, can you check the following instructions and share the outcome with us?

1. Check the registry for Excel in your computer. Follow the steps in the thread below and locate your registry key/value pairs.

How to set up a custom Decimal Sepator to all Excel Users in the company without changing Regional Settings (microsoft.com)

Note: Replace 14.0 with 16.0 in the address as HKEY_CURRENT_USER\Software\Microsoft\Office*16.0\Excel\Options*

It will be helpful if you can capture a screenshot of the registry window.

2. Reinstall Excel to ensure it is clean installation. Kindly follow the steps below:

Download and install or reinstall Microsoft 365 or Office 2021 on a PC or Mac

Sincerely,

Ryan | Microsoft Community Moderator

Answer 2

Please read this article:
Power Query - How to import a CSV file that does not match your locale - Microsoft Community

Andreas.