CSV shows all data in one column? Wrong delimiter fix

You open a CSV and instead of neat columns, column A holds whole lines such as name;email;city. Nothing is broken in the file. Excel just split it on a different character than the one the file uses.

column A: name;email;city column A: Ana;[email protected];Lisbon

Why it happens

"CSV" is a loose convention. The letters stand for comma-separated values, but plenty of software writes semicolons, tabs or pipes instead. Excel on Windows doesn't guess: it uses the list separator from your regional settings. On a US-English system that is a comma. In many European and Latin American regions it is a semicolon, because the comma is already the decimal mark (3,14).

So a semicolon file opened on a comma system lands in one column, and a comma file opened on a semicolon system does the same. The file and the machine disagree.

Fix it in Excel

Split the column you already have:

1. Select column A.
2. Go to Data ▸ Text to Columns.
3. Choose Delimited, click Next.
4. Tick the separator your file uses (Semicolon, Tab, Comma, or "Other" with a |) and check the preview.
5. Finish.

Better, avoid the problem on the way in: use Data ▸ From Text/CSV. The preview has a Delimiter dropdown, so you choose the separator yourself.

A trick that works for files you control: put sep=; alone on the first line. Excel reads it as an instruction and uses that character. The catch is that every other program will treat that line as data, so don't use it on files that go anywhere besides Excel.

Fix it in Google Sheets

Use File ▸ Import ▸ Upload and set Separator type to the right character (or leave it on "Detect automatically"). For data already pasted into one column, select it and use Data ▸ Split text to columns.

Check for separators inside the data

If your text fields contain the separator, such as an address 12 Main St, Apt 4 in a comma file, the field must be wrapped in double quotes. If it isn't, that row will have extra columns. See the article on unclosed quotes and wrong field counts.

How to tell which separator your file uses

Open the file in Notepad and look at the first line. If words are divided by ;, it's a semicolon file. A visible gap that looks like a wide space is usually a tab. A vertical bar is a pipe. Once you know, the fix in Excel or Sheets is a matter of picking the matching option.

If you receive CSV files regularly from the same source, ask them to switch to a comma, or note the separator in your own process. Fixing it by hand each time is a waste, and the mismatch tends to come back whenever a new person or computer with different regional settings opens the file.

← Back to the CSV Fixer tool