Why a CSV file opens in one column in Excel — and how to fix it

A CSV opens in one column when Excel expects a different separator from the one the file uses. Where the comma is the decimal mark, Excel splits on semicolons, so a comma-separated file stays in column A. Import it with Data → From Text/CSV and choose the comma, or save the file with the separator your Excel expects.

Why the separator differs

“CSV” means comma-separated values, but there is no single rule. Excel follows your computer’s regional settings. Where numbers are written 1.234,56 — much of continental Europe and South America — the comma is needed for decimals, so the list separator is a semicolon. A file made with commas in the US or UK then looks like one long field per row to that Excel, and the reverse happens too.

Fix 1: import instead of double-clicking

1. Open a blank workbook.
2. Go to Data → From Text/CSV and pick the file.
3. In the preview, set Delimiter to Comma (or Semicolon, Tab — whatever the file uses).
4. Set File origin to 65001: Unicode (UTF-8) if accents look wrong.
5. Click Transform Data if you need to set column types (see below), otherwise Load.

This leaves the file unchanged and does not touch your system settings.

Fix 2: Text to Columns on data already open

1. Select column A.
2. Data → Text to Columns → Delimited.
3. Tick the right separator and finish.

This is quick, but it can split badly if values contain the separator inside quotes, and Excel may already have changed some values when it opened the file.

Fix 3: produce the file with the right separator

If you generate the file, give it the separator your readers’ Excel expects. The JSON to CSV converter offers comma, semicolon or tab, with semicolon labelled for European locales. Tab-separated files are a good neutral choice for data passed between countries, because tabs almost never appear inside values.

A small trick for files you only open yourself: adding a first line that reads sep=, tells Excel which separator to use. Other programs will treat that line as data, so do not use it for files shared with other tools.

Other reasons columns go wrong

• Values containing the separator must be wrapped in double quotes: "Smith, John". Without quotes, the row splits in the wrong place and later columns shift.
• Line breaks inside a value must also be quoted, or one record becomes two rows.
• Text pasted from a PDF or web page may use runs of spaces instead of separators. Clean it first with the Text Cleaner, or extract tables straight into a workbook with the PDF Table Extractor, which writes an Excel file and avoids separators altogether.

Opening CSVs in Google Sheets or Numbers

Google Sheets’ File → Import detects the separator automatically and lets you override it, so a file that fails in Excel often opens correctly there. Apple Numbers also detects common separators. If a colleague’s file opens fine for them and not for you, the difference is almost always the regional setting, not the file.

Should you change your regional settings?

You can change Windows’ list separator, or turn off “Use system separators” in Excel’s advanced options, but that affects every file and every formula you type. It is better to import each file correctly than to change how your whole system reads numbers.

Mistakes to avoid

• Find-and-replacing all commas with semicolons — it also breaks quoted text and decimals.
• Saving over the original after Excel has reformatted it.
• Sending a semicolon file to a system that expects commas, or the reverse.

Checklist

• Which separator does the file actually use? Open it in a text editor to see.
• Imported through Data → From Text/CSV with that separator?
• Values containing separators or line breaks quoted?

Once the columns are right, Excel may still change the values themselves — see how to stop Excel changing numbers to dates.