DEV Community

Cover image for Why Excel Turns Your IDs into 9.01E+09 — and How to Import a CSV Without Losing Data
Thedaybreak0117
Thedaybreak0117

Posted on AI-assisted

Why Excel Turns Your IDs into 9.01E+09 — and How to Import a CSV Without Losing Data

Disclosure: This is the English edition of an explainer I originally made in Japanese. It was translated with AI assistance and then checked and edited by hand. Every example below uses dummy data. The scientific-notation value shown for the phone-number example was calculated locally to match Excel's display.

Open a CSV the usual way — double-click — and Excel quietly rewrites some of your data before you touch a single cell:

  • A member ID like 00123 comes in as 123. The leading zeros are gone.
  • A phone number like 09012345678 shows up as 9.01E+09.
  • Postal codes get the same treatment as member IDs: if they look numeric, they are treated as numbers.

Nothing in the file was broken. The damage happens at import, and it gets worse: if you save the file in that state, the converted values are what gets written back. In that saved file, the original text is no longer there to recover. And for codes longer than 15 digits, it isn't just display — digits beyond the 15th are lost.

This post focuses on preventing that conversion when you import a CSV into Excel.

Why it happens

A CSV field carries no type information. The file is plain text, and it doesn't say whether 00123 is a quantity or a label.

When Excel opens a CSV directly, it guesses the type of each cell. A string of digits parses cleanly as a number, so Excel converts it — and as a number, 00123 and 123 are the same value, so the zeros drop. The phone number becomes the number 9012345678, its leading zero gone — and that is the value Excel displays as 9.01E+09. The scientific notation is only the display; the loss happened at conversion, when the text became a number.

The real problem is a category error: phone numbers, postal codes, member IDs, and product codes are identifiers, not quantities. You never add two phone numbers together. They only look like numbers; they behave like text. The import step doesn't know that — unless you tell it.

The fix: control the type at import

Don't open the CSV by double-clicking. Import it instead:

  1. On the Data tab, choose From Text/CSV and select your file.
  2. In the preview, click Transform Data to open the Power Query Editor. Select the ID / phone / code columns and set their data type to Text. If Excel asks whether to replace the current conversion or add a new step, choose Replace current — the automatic conversion has already turned 00123 into the number 123, and converting that number to text afterwards would give you "123", not your zeros back.
  3. Click Close & Load. The values come in exactly as written — leading zeros intact, no scientific notation.

That's the whole fix. The numbers were never the problem; the uncontrolled type conversion was. If a column is an identifier, import it as Text, and check the result before you save — once a converted file has been saved over the original, the mangled values are locked in.

Watch it happen (29 seconds)

I made a short video of the phone-number case — the 9.01E+09 symptom and the same import fix:

https://youtube.com/shorts/_sxF_dg4tQI


This post is part of a small series checking what spreadsheets do to files you receive from other people — and how to open them without losing what's inside.

Top comments (0)