# CSV Opens Wrong in Excel — Garbled Characters, One Column, Lost Zeros

Canonical: https://commareports.com/fix/csv-opens-wrong-in-excel
Published: 2026-10-12

> Excel shows é instead of é, crams every field into column A, strips leading zeros from ZIP codes, or turns IDs into 1.23E+15. Each symptom has one cause and a fix that works without retyping the data.

# The CSV opens wrong in Excel

The file is fine. Open it in a text editor and every character, comma and
zero is where it should be. Excel is the one rewriting it — on the way in,
before you see anything — and each symptom maps to one of its defaults.

| What you see                                   | What Excel did                                       |
| ---------------------------------------------- | ---------------------------------------------------- |
| `é`, `ü`, `’`, `ðŸ‘` instead of `é ü ' 👍` | Read UTF-8 as the Windows code page (no BOM)         |
| Every field crammed into column A              | Split on `;` because your locale's list separator is |
| `02139` became `2139`                          | Typed the ZIP code as a number                       |
| `4111111111111111` became `4.11111E+15`        | Typed the ID as a number — 15-digit precision        |
| `3-4` became `4-Mar`                           | Matched a date pattern                               |

Google Sheets reads UTF-8 and detects the delimiter, so if the first two
look fine there, the file itself is good. (Sheets guesses number types too,
so the last three can show up there as well.)

## Garbled characters: the missing BOM

A CSV has no header that says what encoding it is in. Excel on Windows
guesses, and without a hint it guesses the legacy system code page
(Windows-1252 in English locales). UTF-8 encodes `é` as two bytes, so it
comes out as the two Windows-1252 characters `é`.

The hint Excel looks for is a byte-order mark — the three bytes `EF BB BF`
at the start of the file.

- **Opening someone else's file:** Data → From Text/CSV, set _File Origin_
  to **65001: Unicode (UTF-8)**, Load.
- **Producing the file:** write the BOM. In Excel that's _Save As → CSV
  UTF-8 (Comma delimited)_. In Python, `encoding="utf-8-sig"`. In pandas,
  `df.to_csv(path, encoding="utf-8-sig")`. In JavaScript, prepend `""`
  to the string before you build the Blob.

Only add the BOM to files a person will open. Some scripts read it as part
of the first header name (`id` instead of `id`).

## Everything in one column: the list separator

A double-clicked CSV is split on the **list separator** from Windows
regional settings, not on the character the file actually uses. Where the
decimal separator is a comma — Germany, France, Spain, Italy, Brazil and
most of Europe and Latin America — the list separator is a semicolon, so a
comma-separated file never splits.

Three fixes, from least to most invasive:

1. **Import instead of opening.** Data → From Text/CSV, pick _Comma_ as the
   delimiter.
2. **Tell Excel in the file.** Add `sep=,` as the very first line. Excel
   honours it and hides the line; other tools will treat it as data, so use
   it only for files meant for Excel.
3. **Write semicolons.** If the audience is all in one locale, produce the
   file with `;` and the double-click just works.

## Leading zeros, long IDs and surprise dates: type guessing

Excel converts every cell that _looks_ like a number or a date the moment
the file opens, and saving afterwards makes the damage permanent. ZIP
codes lose their zeros, 16-digit IDs keep only 15 significant digits, and
`3-4` becomes 4 March.

A plain CSV has no way to say "this column is text". The fix is on the
opening side: **Data → From Text/CSV → Transform Data**, select the
columns, _Data Type → Text_, then _Close & Load_. Teach whoever receives the
file to do the same, or don't send them a CSV.

The `="02139"` trick (writing each cell as a formula) does keep the zeros,
but every other program then sees a formula instead of a value. It's a
workaround for one viewer, not a fix.

## Making the CSV in the first place

If the data started life as JSON — an API response, an export, an agent's
output — the [JSON to CSV converter](/tools/json-to-csv) writes the BOM by
default, has a semicolon delimiter option for European Excel, flattens
nested objects into columns, and can escape cells starting with `=`, `+`,
`-` or `@` so a spreadsheet doesn't run them as formulas. Going the other
way, [CSV to JSON](/tools/csv-to-json) keeps leading zeros and long IDs as
strings.

## Or don't send a file at all

Every symptom on this page is the recipient's spreadsheet reinterpreting
your data. If the point is for someone to _read_ the numbers, publish them
as a table instead: paste the CSV into [CSV to HTML
table](/tools/csv-to-html-table) and share it. The link renders identically
on every machine and every locale, leading zeros and all, and reviewers can
select a cell and comment on it rather than replying "row 14 looks wrong".
Commenting needs a free sign-in; reading doesn't.

**[Share a table that opens the same for everyone →](https://commareports.com/)**

### Related

- [JSON to CSV](/tools/json-to-csv)
- [CSV to JSON](/tools/csv-to-json)
- [CSV to HTML table](/tools/csv-to-html-table)
- [Excel “Save as Web Page” breaks when sent](/fix/excel-save-as-web-page-broken)
- [Excel to HTML table](/tools/excel-to-html-table)