How to Fix CSV Delimiter and List Separator Issues in Excel
Question details
The user needs to fix an issue where exported CSV data is compressed into a single column due to conflicting regional list separators.

- Product
- Microsoft Excel
- Device & OS
- Windows
- Scenario
- Exporting CSV files between regions (e.g., South Africa and the UK) for import into applications like Xero.
- Observed behavior
- When opening or importing the CSV file, all data appears clumped together in column A instead of being separated into distinct columns.
Before changing your system settings, open the problematic CSV file in a basic text editor like Notepad to verify whether the data is currently separated by commas or semicolons.
Change the Windows Regional List Separator
Update your system's default list separator to ensure Excel saves CSV files with the correct delimiter expected by your importing application.
Excel relies on your Windows regional settings to determine how to separate lists. If your region defaults to semicolons but your target application expects commas, you must adjust this system setting.
Open the Windows Control Panel, navigate to 'Clock and Region', and click on 'Region'.
In the Region dialog box under the Formats tab, click the 'Additional settings...' button at the bottom.
Locate the 'List separator' field. Delete the existing character (usually a semicolon) and type a comma (,).
Click 'Apply' and then 'OK'. Restart Excel and save your CSV file again to apply the new comma delimiter.

Export as CSV UTF-8 and Verify in a Text Editor
Manually ensure the file is saved with the correct encoding and use a text editor to confirm the delimiter without Excel reinterpreting it.
Use Power Query for Recurring Imports
If you frequently import CSV files with varying delimiters, use Power Query to manually specify the delimiter during the import process.
Seamlessly Manage CSV Files with WPS Spreadsheet
WPS Office provides a highly compatible and user-friendly spreadsheet tool that makes handling CSV files, delimiters, and data formatting incredibly simple.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document.
- 2. Import CSV Data: Go to the Data tab and select 'Import Data' to bring in your CSV file.
- 3. Set the Delimiter: During import, the wizard allows you to manually check 'Comma' or 'Semicolon' to ensure data separates correctly into columns.
- 4. Save and Export: Once formatted, click Menu > Save As and select 'CSV' to export your file seamlessly with the proper formatting.

Frequently Asked Questions
Why is all my CSV data appearing in a single column?
This happens when the application opening the CSV file expects a different delimiter (like a comma) than the one used to save the file (like a semicolon). Excel relies on your computer's regional settings to determine this default delimiter.
Will changing my Windows list separator affect other programs?
Yes, the Windows list separator is a system-wide setting. Changing it may affect how other programs display lists and export data. If you only want to change it temporarily, you can revert the setting back to a semicolon after exporting your CSV.
How can I fix a CSV file without changing my computer's regional settings?
You can open the CSV file in a basic text editor like Notepad, use the 'Find and Replace' feature to replace all semicolons with commas, and save the file. Alternatively, you can use the 'Text to Columns' feature under the Data tab in Excel to split the data manually.
Does Xero only accept comma-delimited CSV files?
Most standard web applications and accounting tools, including Xero, are optimized for UK or US data formatting, which expects commas as the default list separator in CSV (Comma-Separated Values) files.




