Skip to content
Riluko

Guide

CSV Opens in One Column in Excel: Commas vs Semicolons

You open a CSV file and every row sits in column A. The data is usually fine: Excel and the file disagree about the separator.

Why a CSV opens in one column

A CSV (“comma-separated values”) file is plain text. Something has to mark where one value ends and the next begins, and files do not all use the same character. Some use a comma, some a semicolon, some a tab.

Microsoft describes .csv files as ones in which the comma “typically” separates fields, and notes that when Excel saves a workbook as .csv the default list separator is a comma, which can be changed in Windows Region settings (Import or export text (.txt or .csv) files). That page describes the saving side and the import dialog. It does not spell out what happens when you open a file whose separator differs, but support guides and user reports commonly describe exactly that: Excel may not split the line into columns, and the whole line stays in one cell. Treat this as the usual explanation rather than a guarantee, and use the import preview below to confirm.

This is common with files made on a computer with different regional settings from yours. In many regions the comma is the decimal mark (4,50), so spreadsheets there often write semicolons between fields instead. Your own settings and the file’s origin decide what you see, so the same file can open correctly on one computer and in one column on another.

The same data with a comma separator
Product,Price,Stock
Notebook A5,"4,50",120
Pen set,"12,90",35
…and with a semicolon separator (decimal commas stay unquoted)
Product;Price;Stock
Notebook A5;4,50;120
Pen set;12,90;35

If your Excel is set to expect semicolons and receives the first file, you may see everything in column A, and the same can happen the other way round with the second file.

Double-clicking versus importing

When Excel opens a .csv directly, it uses its current default settings to decide how to read each column. Microsoft’s page says exactly that: Excel “uses the current default data format settings to interpret how to import each column of data”. Opening this way gives you no preview and no choice of separator.

Importing the file instead lets you choose the separator and check the preview before anything loads. That is why it is the dependable route.

Import the file and choose the separator (Windows)

  1. Open a blank workbook. On the Data tab, in Get & Transform Data, choose From Text/CSV.
  2. Select the file and choose Import.
  3. In the preview, open the Delimiter list and choose the character the file uses. The preview updates immediately, so you can see whether the columns look right.
  4. Choose Load to put the data in a new worksheet, or Transform Data if you want to adjust it first.

Source: Microsoft’s import guide. Menu names can differ a little between Excel versions. Microsoft’s page lists Microsoft 365 and Excel 2016 or later, and has no separate instructions for Mac, so this guide does not describe Mac steps.

Prefer the older wizard? Microsoft notes you can rename the file from .csv to .txt before opening it. That starts the Text Import Wizard, where step 2 lets you choose the delimiter.

Changing the separator Excel expects

You can change the list separator in Windows. Microsoft documents the route for Windows 11 as Region Settings → Administrative language settings → Formats tab → Additional settings → Numbers tab → List separator, and the path differs on Windows 10 and older versions. Microsoft also warns that this is a global change that affects every program on the computer.

To change it for Excel only, Microsoft describes clearing File → Options → Advanced → Editing options → Use system separators and setting the decimal and thousands separators yourself; it notes that setting a comma as the decimal separator makes Excel use a semicolon as the list separator. Because this also changes how numbers are read and written, use it only if you understand the effect (same Microsoft page).

Which separator should the file use?

  • Comma is the usual CSV. Use it when the people and programs that will open the file expect commas, or when you are not sure.
  • Semicolon is common where the comma is the decimal mark. If numbers in your file look like 4,50, semicolons keep columns and decimals apart.
  • Tab works well when values contain both commas and semicolons, and for copy-and-paste. Microsoft describes tab-delimited files as .txt files. Riluko saves them as .tsv; if a double-click does not split the columns, use the import steps above.

A value that contains the separator is wrapped in quotes so it stays in one cell, as in "4,50" in the comma file above. That works in every separator, so you do not need to change your data.

Fix it with Riluko

The CSV Cleaner reads and writes the file in your browser; nothing is uploaded. It gives you two separate controls:

  1. Separator used in your file (in the file summary). “Automatic” guesses from the file and shows what it found. If the preview shows one column, or too many, choose the right one and the file is read again from the start.
  2. Clean the data if you need to (repeated rows, spacing, split or merge columns).
  3. Separator in the downloaded file (in Download options). Choose Semicolon if your Excel expects semicolons, Comma if it expects commas, or Tab for a .tsv file.
  4. Download the file and open it in Excel.

Changing the file’s separator after you have cleaned the data starts again from the original file, so Riluko asks first and then clears your applied steps. Your original file is never modified.

Try it on a semicolon file

A made-up product list with decimal commas. Nothing leaves your browser.

Open the semicolon sampleUse my own file

Check the result in Excel

  • Each field is in its own column, with the header names across row 1.
  • Text that contained commas (for example Ruled, 80 sheets) is still in a single cell.
  • Decimal values look the way you expect for your region.
  • Identifiers that need leading zeros are still correct. See How to preserve leading zeros in Excel and CSV files.

If it still does not look right

  • Everything is still in one column. The separator you chose does not match the file. Open the file in a plain text editor to see which character sits between the values, then choose that one.
  • Far too many columns. Text values may contain the separator without quotes in the source. Try another separator, or check whether the source file is damaged.
  • Numbers or dates changed. Excel interprets values when it loads them. Importing lets you set a column to Text (Microsoft: keep leading zeros and large numbers).
  • Accented letters look wrong. Keep “Add a UTF-8 marker for Excel” ticked in Riluko’s download options. If the letters are still wrong, the file may use another text encoding; see Fix garbled characters in a CSV file.

Frequently asked questions

Is a semicolon-separated file still a CSV?

In everyday use, yes. Many programs write semicolons and still call the file CSV. Formally the “C” stands for comma, which is why some tools are strict about it.

Will changing the separator fix every CSV problem?

No. It only changes where columns are split. Leading zeros, long numbers, dates and character encoding are separate issues.

Does Riluko upload my file to choose the separator?

No. The file is read and written in your browser. See the privacy page.

Will my Excel behave the same as the one in this guide?

Not necessarily. Menu names and defaults vary by version and by regional settings, which is exactly why files can behave differently on different computers. Use the preview to confirm.