How to Fix Incorrect Decimal Separators When Opening CSV Files in Excel
Question details
Excel misinterprets decimal points and thousands separators when importing CSV files due to conflicting regional settings.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing or opening CSV files containing numerical data where the decimal and thousands separator formatting does not match the system's default settings.
- Observed behavior
- Numbers are formatted incorrectly after import, often swapping decimal points with thousands separators or converting numeric values into incorrect magnitudes during the data conversion process.
Before importing, open your CSV file in Notepad or a plain text editor to verify the exact characters used for decimal points and thousands separators in the raw data.
Import CSV using Get Data (Power Query)
Use Excel's Get Data feature to explicitly define the data type and locale settings during the import process, ensuring accurate conversion regardless of system settings.
Power Query is the most reliable method for importing CSV data because it allows you to specify the source file's exact regional formatting (locale) without altering your computer's global settings.
Open Excel, navigate to the Data tab, click on Get Data, select From File, and then click From Text/CSV.
Locate and select the problematic CSV file on your computer, then click Import.
In the preview window that appears, verify the delimiter. Instead of clicking Load, click Transform Data to open the Power Query Editor.
Right-click the column header of your numerical data, select Change Type, and then choose Using Locale.
In the dialog box, set the Data Type to Decimal Number and choose the Locale (e.g., English (United States) for dot decimals or German for comma decimals) that matches your raw CSV file. Click OK, then click Close & Load.
Specify Separators in the Text to Columns Wizard
Adjust the separator settings manually using the Text to Columns advanced options if the data is already pasted into Excel or opened directly.
Modify Excel's Global Advanced Options
Override your computer's default regional settings within Excel itself to universally change how decimal and thousands separators are handled.
Use WPS Spreadsheet for Hassle-Free CSV Imports
WPS Spreadsheet features an intuitive text import wizard that automatically recognizes different delimiters and allows you to easily format decimal and thousands separators without altering your system's regional settings.
- 1. Launch WPS Spreadsheet: Open WPS Office and create a new Spreadsheet document.
- 2. Import Your Data: Navigate to the Data tab on the ribbon and click on Import Data, then choose your target CSV file.
- 3. Configure the Import Wizard: Select 'Delimited' in the Text Import Wizard and choose the delimiter that separates your data.
- 4. Set Advanced Separator Rules: In the Data Preview step, click 'Advanced' to explicitly define the correct decimal and thousands separators based on your file's format, then click Finish.
Frequently Asked Questions
Why does Excel change my decimal points to commas when opening a CSV?
This happens when the CSV file's decimal formatting differs from your computer's regional settings. For example, the file might use a period as a decimal, but your operating system is configured to expect a comma, causing Excel to interpret the data incorrectly.
Does changing Excel's system separators affect other programs on my computer?
No. If you uncheck 'Use system separators' and change the values inside Excel Options > Advanced, the modifications only apply to Excel. However, if you change regional settings in the Windows Control Panel, it will affect all programs.
How do I stop Excel from dropping leading zeros in CSV files?
During the import process using Get Data or the Text to Columns wizard, highlight the column containing numbers with leading zeros and change the Column Data Format to 'Text' before completing the import.
Can I fix separators after the data is already imported incorrectly?
It is difficult to fix reliably because Excel might have already permanently converted the numbers into text strings or dates. It is highly recommended to re-import the original CSV using the correct separator settings via Power Query.
