Comma or semicolon: the CSV separator and regional settings explained

You double-click a CSV file and, instead of neat columns, everything is crammed into column A: Product;Price;Stock. Or the other way round: you save a sheet as CSV, send it to someone, and it arrives with semicolons when their system expected commas. The file isn’t broken. It’s the regional settings on each computer.

Here you’ll learn where the problem comes from, which separator Excel uses when saving and opening, and how to import any CSV without breaking columns, decimals or accented characters.

Why a CSV can use commas or semicolons

CSV stands for “comma-separated values”. The format was born in countries where the comma isn’t used for decimals. In much of Europe and Latin America, however, one and a half is written 1,5. If the field separator were also a comma, a program couldn’t tell whether 1,5 is one number or two separate fields.

That’s why Windows and macOS have a setting called the list separator. In regions with a decimal comma it’s usually the semicolon; in regions with a decimal point, it’s the comma. Excel takes this setting from the system both when saving and when opening CSV files.

Not even all Spanish-speaking countries agree: a CSV created on a computer set up for Mexico may open badly on one set up for Spain.

Which separator Excel uses when saving a CSV

When you choose File › Save As › CSV (Comma delimited), Excel doesn’t necessarily write commas. It writes your system’s list separator. If Windows is set to use semicolons, the file will contain semicolons even though the format name says “comma”.

To check or change this setting in Windows:

1. Open Control Panel and go to Clock and Region › Region.
2. Click Additional settings.
3. On the Numbers tab, look at the List separator field.
4. Change it if you need to, click OK and reopen Excel.

Keep in mind that this change affects the whole system, including how you type formulas: with a semicolon as list separator, Excel expects =IF(A2>5;"Yes";"No") instead of commas.

On a Mac, Excel also follows the system region (System Settings › General › Language & Region). LibreOffice Calc is more flexible: when saving as CSV, tick Edit filter settings and it lets you choose the delimiter.

How to open a CSV that shows up in a single column

When you double-click, Excel opens the CSV assuming it uses your list separator. If the file uses a different one, everything stays in column A. You have three fixes.

Option 1: Text to Columns

1. Select column A.
2. Go to Data › Text to Columns.
3. Choose Delimited and click Next.
4. Tick the file’s real separator (Comma or Semicolon) and untick the others.
5. In the last step, the Advanced button lets you specify the decimal and thousands separators used in the file. Click Finish.

Option 2: import from Text/CSV

This is the most reliable method in Excel 2016, 2019, 2021 and 365. Instead of double-clicking, open a blank workbook and go to Data › Get Data › From File › From Text/CSV. In the preview window you can pick the Delimiter and the File Origin (the encoding). If everything looks right, click Load. If numbers or dates use another regional format, click Transform Data and, on the affected column, use Change Type › Using Locale.

Option 3: the sep= line

A trick only Excel understands: open the CSV in Notepad and add sep=; (or sep=,) as the first line. Save it and double-click: Excel will use that separator. The downside is that other programs will read that line as data, and in some cases it interferes with UTF-8 encoding detection. Use it for viewing, not for files headed to a database.

The other problem: decimals and thousands

Even when the columns come out right, numbers can arrive as text. Imagine you receive a CSV from a system set up for Spain or France with these amounts:

If your Excel uses a decimal point, it will see those values as text (left-aligned) or, worse, read 89,90 as 8,990. The NUMBERVALUE function (Excel 2013 and later) converts the text once you tell it which decimal and thousands separators it uses:

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

The second argument is the decimal separator in the original text and the third is the thousands separator. If your version doesn’t have it, you can remove the dots and replace the comma with a point:

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

Fill the formula down, check the results and, if you want to keep only the values, use Copy and Paste Special › Values.

Accents and strange characters: the encoding

The separator isn’t the only thing that changes between computers. If you open a CSV and see Café instead of “Café”, the file is in UTF-8 and Excel read it with a different encoding. When saving, choose CSV UTF-8 (Comma delimited), available in Excel 2019, 2021 and 365, instead of the classic CSV. When importing, use option 2 and select 65001: Unicode (UTF-8) under File Origin.

Tips for sending a CSV that works on any computer

• Ask what the destination expects. Many e-commerce platforms, banks and email programs require commas and a decimal point. Others accept both.
• Open the CSV in a text editor before sending it. That way you see the real separator, not what Excel interprets.
• Avoid thousands separators in numeric columns. 1234.5 imports better than 1,234.50.
• Use year-month-day dates (2025-03-04). 03/04 can be read as March 4 or April 3 depending on the country.
• Remember that a CSV stores a single sheet and loses formatting, formulas and colours. Always keep the original xlsx.

If you just need to turn a sheet into a CSV without opening Excel, try our free Excel to CSV converter: upload your xlsx, xls or ods file and download the CSV. Then open it in a text editor to confirm the separator is the one the destination program needs. And if someone sends you a CSV and you want to work on it as a spreadsheet, you can convert the CSV to Excel.

Conclusion

Comma or semicolon doesn’t depend on the file but on the regional settings of the computer that creates it and the one that opens it. Excel uses the system list separator when saving and opening, which is why the same CSV looks perfect on one computer and jumbled on another. If you import through Data › From Text/CSV, check decimals with NUMBERVALUE and save in UTF-8, you’ll avoid almost every problem.

Frequently asked questions