Why your CSV opens in one column

Your file is fine and Excel is not broken. A CSV does not record which character separates its columns, so Excel guesses — and it guesses using the list separator from your Windows regional settings rather than by looking at the file. If the file was written with semicolons and your machine expects commas, or the other way round, every row lands in one cell. Fix it for one file with Data → From Text/CSV, or for good by changing the list separator. If you would rather not have the argument at all, SPLIT and Sheets work out the separator by counting what is actually in the file, and nothing is uploaded.

Why a CSV does not say what it is

"Comma-separated values" is a description of one common case, not a specification. There is no header, no declaration and no magic number: a CSV is a text file, and the only way to know which character divides the columns is to look at the characters. Every program that opens one is guessing.

Excel guesses badly on purpose, for a reason that made sense in 1995. In much of continental Europe and Latin America the comma is the decimal mark — one thousand two hundred and thirty-four and a half is written 1.234,56 — so a comma cannot also separate columns without ambiguity. Those systems use a semicolon instead, and Windows records the choice as the list separator in the regional settings. Excel reads that setting and applies it to every CSV it opens, whoever wrote the file and wherever it came from.

So the failure is not really about your file. It is about the file having been written on a machine whose list separator differs from yours, which is the normal case for anything downloaded from a website, exported by a colleague abroad, or produced by software written elsewhere.

Which separator does your file use?

Open it in Notepad, TextEdit or any plain text editor — not in Excel — and look at the first line.

That last one is worth knowing. Excel writes sep=; above the header so that the next copy of Excel reads the file correctly whatever its regional settings say — and most other software does not know the convention, so the line turns up as a stray first row called "sep=;" instead. If you have ever seen that, this is what it was.

Fixing it in Excel

Three ways, from the one that fixes this file to the one that fixes every file.

1. Import instead of opening. Data → From Text/CSV, choose the file, and Excel shows a preview with a Delimiter dropdown. Pick the right one, press Load. This is the same machinery as the old Text Import Wizard and it asks the question rather than guessing.
2. Text to Columns, after the fact. If the file is already open and stacked in column A: select the column, Data → Text to Columns → Delimited, tick the right separator, Finish. Same result, one more step, and it works on a file somebody has already sent you inside a workbook.
3. Change the list separator, once. Windows: Settings → Time & language → Language & region → Regional format → Change formats → Advanced → List separator. Set it to whatever your files actually use. This fixes every future double-click and is the right answer if you are always receiving files of one kind — but it is a machine-wide setting that also changes how Excel writes CSVs, so it moves the problem to whoever you send them to.

On a Mac, Excel takes the separator from the region set in System Settings and offers no equivalent switch, so the import route is the reliable one there.

Add the line that ends the argument

If you are the one producing the file and it keeps coming back wrong, put sep=; (or sep=,) on a line of its own above the header. Excel obeys it regardless of regional settings, and any reader that does not understand it shows one odd row rather than one ruined column — which is the better failure of the two.

The damage a spreadsheet does while you are not looking

Worth saying, because getting the columns to split is not the same as getting the data back. The moment a CSV opens in Excel, several things change quietly and none of them are announced:

• A reference like 007 becomes 7. The leading zeros are gone and there is no undo that brings them back once the file is saved.
• A postcode, part number or gene name that looks like a date becomes one. MAR1 becomes 1 March; this is well enough known in genetics that some genes were renamed to stop it happening.
• A long number — an IMEI, an order ID, a card number — becomes 1.23457E+15, and the digits past the fifteenth are actually lost.
• A value in a European decimal format read by a machine expecting commas, or the reverse, silently changes by a factor of a thousand.

None of that produces an error. It produces a file that looks fine.

Not opening it in a spreadsheet at all

If what you need is to look at the file, filter it, deduplicate it, keep some columns or split it up, a spreadsheet is a heavy tool to reach for and it is the one that damages the data. SPLIT reads the file in the tab you have open, works the separator out by counting what is on the first line rather than by consulting your regional settings, honours a sep= line if the file has one, and converts nothing: a value that went in as 007 comes out as 007. Sheets uses the same reader when you open a CSV in it.

Nothing is uploaded in either case, which matters more than it sounds for this particular kind of file — a CSV is usually an export of a customer list, a payroll run or a database table, and putting one through a website to fix a punctuation problem is a disclosure nobody intended to make.