Why your CSV opens wrong in Excel

Excel is not misreading your file. It is reading it exactly the way it was designed to, which is to look at every value, decide what it probably means, and convert it. That design is fine for a shopping list and hostile to a data export. Six specific things go wrong, and each has a different fix.

The underlying issue is that CSV has no schema. On disk, 00123 and 123 are different strings, and nothing in the file says which one is a number. Excel decides, using your regional settings, and it never asks.

Symptom: every row lands in column A

Cause. Excel does not detect the delimiter when you double-click a CSV. It uses the list separator from your operating system's regional settings. In the United States and the UK that is a comma. In Germany, France, Spain, Italy, the Netherlands, Brazil and much of the rest of the world it is a semicolon, because those locales use the comma as the decimal separator and a file full of 1,50 would be unparseable otherwise.

So a comma-delimited file opened on a German Windows machine has no separator Excel recognizes, and the whole line goes into one cell.

Fix. Three options, in increasing order of how much I like them. You can use Data, then From Text/CSV, and pick the delimiter in the import dialog. You can add a sep=, line as the very first line of the file, which Excel honors and most other parsers treat as a stray row. Or you can convert the delimiter once, so the file matches the machine that has to open it, and stop thinking about it.

Fix it: change the delimiter, comma to semicolon or back →

Symptom: long IDs turn into 1.23E+15

Cause. Excel stores numbers as IEEE 754 doubles and displays at most 15 significant digits. A 16-digit credit card token, an 18-digit Snowflake ID, a long barcode: all of them are read as numbers, exceed the display width, and get shown in scientific notation.

The display is annoying. The damage is worse. Anything past the fifteenth significant digit is not just hidden, it is gone, replaced with a zero. That is why damaged files have a distinctive look: a column of long IDs where a suspicious number of values end in 0.

Fix. The column has to arrive as text, and that decision has to be made before the value is stored. Widening the column, or formatting it as Number afterward, only changes how the already-rounded value is drawn.

Fix it: the full scientific notation guide, including how to tell display from damage →

Symptom: 01234 became 1234

Cause. Same mechanism as above, different consequence. Excel read the value as a number, and numbers do not have leading zeros. Zip codes in New England, French postal codes, UK sort codes, SKUs, employee numbers, national IDs: they all lose their first characters.

This one is particularly nasty because the result still looks like a plausible value. A zip code of 2134 does not scream "corrupted" the way 1.23E+15 does, so it travels further before anyone notices.

Fix. Import the column as Text, or convert the file to a typed workbook where the column is already declared as text. Our CSV to Excel converter keeps a column as text when the values carry leading zeros, so the workbook you open already has them.

Fix it: the leading zeros guide, including how to rebuild them if they are already gone →

Symptom: 03/04/2026 is the wrong month

Cause. The file says 03/04/2026 and gives you no way to know whether that is the third of April or the fourth of March. Excel resolves the ambiguity with the short date format from your regional settings. Two people in two countries open the same file and get dates a month apart, and neither of them gets a warning.

Excel is also eager about things that are not dates at all. Gene names, part codes like MAR1, version strings like 1.2.3, and measurement ranges like 1-5 are all date-shaped enough to be converted.

Fix. Standardize on ISO 8601, meaning 2026-04-03, in the file itself. It sorts correctly as text, it is unambiguous in every locale, and Excel reads it the same way everywhere.

Fix it: the dates guide, including how to prove what the file actually says →

Symptom: names show up as é, ö or 

Cause. The file is UTF-8 and Excel read it as Windows-1252, or the reverse. A UTF-8 é is two bytes, C3 A9; read one byte at a time as Windows-1252, those become é. The  sequence at the very start of a file is a UTF-8 byte order mark being displayed rather than consumed, which usually means it is stuck to the first column name.

Fix. Either tell Excel the encoding on import (Data, From Text/CSV has a File Origin dropdown), or re-encode the file to UTF-8 with a BOM, which is the one thing Excel reliably auto-detects on a double click.

Fix it: the encoding guide, mojibake, the BOM and the replacement character →

Symptom: numbers arrived as text and will not sum

Cause. The opposite failure. A column of amounts arrives left-aligned with a little green triangle, and SUM returns zero. Three usual culprits: the values carry a thousands separator that does not match your locale (1.234,56 on a US machine), they carry a currency symbol, or they have trailing whitespace. A non-breaking space, hex A0, is the invisible favorite: it looks exactly like a space and TRIM does not remove it.

Negative numbers in accounting parentheses, (500), are a fourth case that most tools ignore entirely.

Fix. Clean the values, then type the column. Our type conversion handles the common shapes directly: 1,234.56, $500, (500) as negative five hundred, and 5% as 0.05. If you would rather do it in SQL, strip first and cast second:

SELECT TRY_CAST(
         REGEXP_REPLACE(amount, '[^0-9.\-]', '', 'g')
         AS DOUBLE
       ) AS amount_num
FROM data;

TRY_CAST rather than CAST matters: it yields NULL for the values that cannot be converted instead of failing the whole query, so you can find them and look at them.

Fix it: convert types with the currency and percentage shapes handled →

Check what is really in the file

Every symptom above is Excel showing you its interpretation. Before you fix anything, look at the file without an interpreter in the way. Open it in a viewer that shows you the raw parsed values and the type each column was given, and answer three questions.

• Does the file itself contain the leading zeros, or were they already missing when it was written? If the source is bad, fixing the import will not help.
• What delimiter is actually in there? A quick look at the first line settles it.
• Are the long IDs full length in the file? If they are, you have an import problem. If they already end in zeros, the damage happened upstream and only the source system can undo it.

That third one is the difference between a five-minute fix and a conversation with whoever produced the export. It is worth checking before you spend an hour on import settings.

Fix it: open the CSV and read the values as they are stored →