Skip to main content
SheetTidy

CSV Opens in One Column in Excel — Here's the Fix

Every value crammed into column A, separated by commas or semicolons? Here is why Excel does that, how to see which separator your file uses, and five ways to split it properly.

By the SheetTidy teamPublished 8 min read

You double-click a CSV file and Excel opens it, but instead of neat columns you get one long line per row in column A: Name,City,Amount or Name;City;Amount, all in a single cell. Nothing is wrong with the data itself. Excel simply used the wrong rule to decide where one value ends and the next begins.

This guide explains why that happens, how to check what your file really contains, and how to fix it without losing leading zeros or special characters along the way.

Why Excel puts everything in one column

A CSV file is plain text. It has no built-in note saying “my values are separated by commas” or “I am saved in UTF-8”. When you open one by double-clicking, Excel has to guess, and it guesses from your computer’s settings rather than from the file.

The list separator in your Windows region settings

On Windows, Excel splits a double-clicked CSV using the list separator set in your region settings. On a PC set to English (United States, United Kingdom or India), the list separator is a comma, so comma-separated files open correctly.

In many European locales, such as Germany, France, Spain, Italy and the Netherlands, the comma is the decimal separator (12,5 instead of 12.5). Those regions use a semicolon as the list separator instead. That causes problems in both directions:

  • A comma-separated CSV opened on a German or French PC lands entirely in column A.
  • A semicolon-separated CSV, exported by a European system or colleague, opens in one column on an English or Indian PC.

The file is fine in both cases. It just does not match the separator the computer expects.

The file uses tabs or pipes

Some programs export tab-separated or pipe-separated (|) data but still give the file a .csv extension. Excel does not look inside to check. If the separator is not the one it expects, everything stays in one column.

Each line is wrapped in quotes

A broken export sometimes puts quotation marks around the whole line, like "Asha,Mumbai,2500". Quotes in a CSV mean “everything inside is one value”, so Excel obeys and keeps the entire line together, even though the commas are right there.

The encoding is misread as well

The same guesswork affects characters. If a file is saved as UTF-8 without a byte order mark (BOM, a short invisible marker at the start of the file), Excel usually assumes your system’s legacy ANSI code page, such as Windows-1252, instead. Each multi-byte UTF-8 character is then read as two or three separate characters. This garbled text is called mojibake:

  • “José” becomes “José”
  • “₹” becomes “₹”
  • Hindi or Gujarati names turn into strings of accented symbols

Separator and encoding problems often appear together, so the fixes below deal with both.

Check which separator your file uses

Before fixing anything, look at the raw file:

  1. Right-click the CSV and choose Open with › Notepad.
  2. Look at the first two or three lines.
  3. Note what sits between the values: a comma, a semicolon, a wide gap (a tab) or a vertical bar (a pipe).
  4. Check whether whole lines start and end with a quotation mark.

If a line reads Asha;Mumbai;2500,50, the separator is a semicolon and the comma is a decimal mark. If it reads "Asha,Mumbai,2500", the whole line is quoted. Close Notepad without saving unless you are using Fix 4.

Fix 1: Import with From Text/CSV (best for most people)

This is the most reliable way in Microsoft 365 and Excel 2016 or later, because you choose the separator and the encoding yourself.

  1. Open a blank workbook.
  2. Go to Data › Get Data › From File › From Text/CSV (in many versions it is also shown directly as Data › From Text/CSV).
  3. Select your file and click Import.
  4. In the preview, set File Origin to 65001: Unicode (UTF-8) if any characters look wrong.
  5. Set Delimiter to Comma, Semicolon, Tab or Custom (type | for a pipe). The preview updates straight away.
  6. Click Load to import as it is, or Transform Data to adjust columns first.

One warning: Excel also detects a data type for every column, and that can damage values. Codes such as 00123 become 123, and numbers longer than 15 digits lose their last digits. To prevent it, set Data Type Detection to Do not detect data types in the preview, or click Transform Data, select each code column, choose Data Type › Text, and then click Close & Load.

The result is a table linked to the file. If you want a plain sheet, right-click the table and choose Table › Convert to Range.

Fix 2: Split column A with Text to Columns

If the file is already open with everything in column A, this is the quickest repair.

  1. Click the A column header to select the whole column.
  2. Go to Data › Text to Columns.
  3. Choose Delimited and click Next.
  4. Tick the separator your file uses (Tab, Semicolon, Comma, Space, or Other with |). Untick any others, then click Next.
  5. In the preview, click each column that holds codes, phone numbers or IDs and set Column data format to Text.
  6. Click Finish.

Text to Columns only works on one column at a time, which is fine here because all your data is in column A. It writes the split values into columns B, C and onwards, so anything already in those columns will be overwritten. Excel asks before replacing cells, but check first.

Text to Columns cannot repair the encoding. If characters are already garbled, close the file and use Fix 1 or Fix 3 instead.

Fix 3: Use the legacy Text Import Wizard

Some people prefer the older wizard because it lets you set the format of every column in one place.

  1. Go to File › Options › Data.
  2. Under Show legacy data import wizards, tick From Text (Legacy) and click OK.
  3. Go to Data › Get Data › Legacy Wizards › From Text (Legacy) and select the file.
  4. In step 1, choose Delimited and set File origin to 65001 : Unicode (UTF-8).
  5. In step 2, tick the right delimiter.
  6. In step 3, select each column and choose Text, General or Date, then click Finish.

Unlike Fix 1, this does not create a linked table, so you get a plain sheet straight away.

Fix 4: Add a sep= line to the file

If you or your colleagues will keep double-clicking the same file, you can tell Excel which separator to use inside the file itself.

  1. Open the CSV in Notepad.
  2. Add a new first line containing only sep=, for a comma file, or sep=; for a semicolon file.
  3. Save the file and double-click it again.

Excel reads that hint and splits the columns, whatever your region settings say. There are caveats:

  • The sep= line is not part of your data. Other programs, such as Google Sheets, databases or import scripts, may show it as an extra row or reject the file.
  • With this line in place, Excel can misread UTF-8 characters, so check names and symbols carefully.

Treat it as a quick fix for files you open yourself, not for files you send to other systems.

Fix 5: Change the Windows list separator (use with caution)

You can change the separator Excel expects for every CSV:

  1. Open Control Panel › Clock and Region › Region.
  2. Click Additional settings.
  3. On the Numbers tab, change List separator to , or ;, then click OK.

This affects every program on your computer, not just Excel. In some locales it also changes the separator Excel uses between formula arguments, so =SUM(A1;B1) may suddenly need to be =SUM(A1,B1). It can also change how Excel saves CSVs, which may break files other people rely on. It is generally not recommended unless you know you will always work with one kind of file.

Troubleshooting

The columns split, but codes lost their zeros. Excel converted them to numbers during import. Re-import with Fix 1 or Fix 3 and set those columns to Text. If you only have the damaged sheet, the keep leading zeros tool can pad codes back to a fixed length.

Some rows split into too many columns. A value contains the separator itself, such as an address with commas, and was not wrapped in quotes when the file was exported. Ask for a fresh export, or open the file in Notepad and add quotes around the affected values.

Characters are still garbled after import. The file may be in an encoding other than UTF-8 or Windows-1252, such as UTF-16. Open it in Notepad, choose File › Save As, set Encoding to UTF-8, then import again.

Everything is in one column even with the right delimiter. Look for quotes around whole lines. Use Find and Replace (Ctrl+H) in Notepad to remove the quotes at the start and end of each line, or ask for a corrected export.

How to prevent it next time

When you create CSV files for other people:

  • Use the separator your audience expects. Comma for most English-language users and systems, semicolon for colleagues in many European countries.
  • In Excel, choose File › Save As and pick CSV UTF-8 (Comma delimited). This adds a byte order mark, so Excel recognises the encoding and shows ₹, accents and Indian scripts correctly when the file is opened.
  • If the file goes to another system rather than a person, check its import documentation for the required separator and encoding.

Open a CSV correctly without Excel’s guesswork

If you would rather skip import dialogs altogether, the CSV to Excel converter reads the file the way it was actually written.

  • It detects the separator from the file itself: comma, semicolon, tab or pipe. A semicolon file opens correctly on an English PC, and a comma file opens correctly anywhere.
  • It detects the encoding: UTF-8, UTF-8 with BOM, or Windows-1252. Names in Hindi, Gujarati, French or Spanish come through intact.
  • It keeps text exactly as written. Codes such as 00123, grouped amounts such as 1,00,000 and values such as ₹ 2500 stay as they are. Only plain numbers become real numbers, through the Store numbers as numbers option, which you can turn off.
  • It produces a proper .xlsx file that opens in the right columns on any computer, with no import steps for the people you share it with.

A few related tools help with the rest of the job:

  • If a sheet already has values merged into one column, the split column tool separates them by comma, semicolon or any other character.
  • If codes have already lost their zeros, restore leading zeros to a fixed length.
  • When you need to send a CSV back out, the Excel to CSV converter writes UTF-8 with a BOM, so Excel shows ₹, accents, Hindi and Gujarati correctly, and lets you choose a comma, semicolon or tab separator to suit the person receiving it.

Everything runs in your browser. Your files are never uploaded and never leave your device.

Tools used in this guide

Free, and your file never leaves your device.