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.

Leading zeros disappeared

Excel reads 007, 01234 or a phone number starting 07 as a number, and a number has no leading zeros, so it shows 7, 1234 and 7700…. The zeros are not hidden by formatting; they are gone from the cell. If you save the file as CSV again, they are gone from the file too.

• Import it and mark the column as text. Data → From Text/CSV, choose the file, then Transform Data rather than Load. In the editor, select the column, set Data Type to Text (replace the current conversion when asked), then Close & Load. The older Text Import Wizard does the same on its third step: select the column and choose Text.
• Or switch the conversion off. Recent versions of Excel for Microsoft 365 have File → Options → Data → Automatic data conversion (under Advanced rather than Data in some builds; on a Mac, Excel → Preferences → Edit). Untick Remove leading zeros and convert to a number, and Keep first 15 digits of long numbers for the IMEI and card-number problem. There is also a box to be warned when a CSV is about to be converted, which is worth ticking.
• Do not fix it by retyping the zeros. Formatting the column as 00000 puts zeros back on screen for a five-digit postcode, but the cell still holds 1234, and a lookup against 01234 elsewhere will miss.

Numbers turned into dates

A part code like MAR1 or SEPT2, a size like 1-2 or 3/4, and a score like 2-1 all look like dates to Excel, and become 1 March, 1 February and so on, stored as date serial numbers. Unlike the zeros this cannot be undone by reformatting, because the original text is no longer in the cell.

• Import with the column as text, exactly as above. It is the only route that stops every kind of date conversion.
• The automatic conversion option helps with some of them. Convert continuous letters and numbers to a date covers MAR1. It does not cover 1-2 or 3/4, which are still read as dates unless the column comes in as text.
• If the file has already been saved from Excel, go back to the file you were sent. A CSV that has been through Excel once may have lost the values for good.

We checked the two tools here against a semicolon file with a sep=; line holding 007, MAR1, 01234, a 15-digit IMEI, a 19-digit reference, 1-2, 3/4 and 1.234,56. SPLIT kept every value exactly and wrote them back out unchanged. Sheets kept them too, and saved every digit of the 19-digit reference back to the file. That check found two faults in Sheets first: it showed the sep=; line as a row of data, and it guessed the separator from the first five lines rather than the header, so a European file with decimal commas in its data could still come in as the wrong columns. It now reads a CSV by the same rule as every other tool on the site.

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.