CSV Opens in One Column
Why everything lands in column A, and how to get proper columns.
The Short Answer
A CSV separates columns with a character, usually a comma or a semicolon. When you double-click a CSV, Excel splits it using the list separator from your computer's regional settings. If the file uses a different one, nothing gets split and each row lands in column A.
Either tell Excel which separator to use while importing, or convert the file to the separator your Excel expects.
Convert the separator →Why It Happens
Microsoft's page on importing and exporting text and CSV files explains that when you open a .csv file, Excel uses the current default data format settings. The default list separator is a comma, and it can be changed in the Windows region settings.
In many European regions the comma is the decimal mark (3,5), so the list separator is a semicolon. A file made on one of those computers opens in one column on a computer set to commas, and the other way round.
| The file uses | Your Excel expects | Result |
|---|---|---|
| Semicolons ; | Commas , | All in column A |
| Commas , | Semicolons ; | All in column A |
Fix 1: Convert the File
- Open the CSV Fixer and pick the CSV. It detects the separator by checking which one gives the same number of columns on every row, and shows a preview.
- Under Separate columns with, choose the one your Excel expects: comma in most English-language settings, semicolon where the decimal mark is a comma.
- Click Download fixed CSV and double-click the new file.
Quoted values are respected, so a semicolon inside an address stays part of that address when the file is converted to commas. If the file will be opened on computers with different settings, click Download .xlsx instead: an Excel workbook has no separator, so it opens in columns everywhere.
Fix 2: Import It in Excel
- Go to Data → Get Data → From File → From Text/CSV and pick the file.
- In the preview window, pick the right separator in the Delimiter drop-down. The preview updates.
- Click Load.
The Text/CSV connector offers comma, semicolon, tab, colon, equals sign, space and custom separators.
Fix 3: Split the Column You Already Have
If the data is already open in column A, use Text to Columns: select column A, go to Data → Text to Columns, choose Delimited, tick the separator the file uses, and click Finish.
Fix 4: Change the Separator Excel Expects
On Windows, the list separator lives in the region settings: search for Region settings, open the additional settings for formats, and change List separator. Microsoft warns that this is a global change: every program on the computer then uses the new character. Only do it if you always work with the other kind of file.
Things That Trip People Up
- The sep= line. A first line such as
sep=;makes Excel use that separator whatever your settings. Microsoft doesn't document it, and every other program sees it as a data row. The CSV Fixer can add it, and reads it if your file already has one. - Decimal commas. If a semicolon file contains numbers like 3,5, converting it to commas keeps 3,5 as written (quoted, so it stays in one column). Excel on a computer that uses a decimal point may then not read it as the number 3.5, so check those columns.
- Leading zeros vanish on import. Once the columns split, Excel converts 02134 to 2134. See Excel removes leading zeros.
- Accents look wrong too. That's an encoding problem, not a separator one. See weird characters like é.
Platform Notes
Excel for Windows has all four fixes. Excel for Mac has Data → Get Data → From Text/CSV in Microsoft 365 and Excel 2024 for Mac, and the CSV Fixer's converted file or .xlsx works on any computer.
Frequently Asked Questions
Why does my CSV open in one column in Excel?
The file uses a different separator from the one Excel expects. When you open a CSV directly, Excel uses its default settings, including the list separator from your Windows region settings. A file separated by semicolons opened on a computer set to commas, or the other way round, ends up with each whole row in column A.
How do I open a CSV in Excel with the right columns?
Import it instead of double-clicking: Data, Get Data, From File, From Text/CSV, then pick the right separator in the Delimiter drop-down before loading. Or convert the file to the separator your Excel expects, or to an .xlsx, which has no separator at all.
How do I split a column that is already in Excel?
Select column A, go to Data, Text to Columns, choose Delimited, tick the separator your file uses (for example Semicolon), and click Finish. Watch out for values with leading zeros: they may be converted to numbers on the way.
Why do European CSV files use semicolons?
Many European regions write decimals with a comma, as in 3,5. A comma can't then also separate columns without quoting every number, so those regional settings use a semicolon as the list separator. Microsoft notes that setting the decimal separator to a comma makes Excel use a semicolon when saving CSV files.
What is sep= at the top of a CSV?
A first line such as sep=; tells Excel which separator the file uses, regardless of regional settings. It is an Excel-only convention that Microsoft doesn't document, and other programs show the line as an extra data row, so only use it for files meant for Excel.
Should I change my Windows list separator?
Only if you always work with files that use the other separator. Microsoft warns that changing it is a global change: every program on the computer then uses the new character as its list separator.