← All notes

Spreadsheets

How to keep leading zeros in a spreadsheet

Keep identifiers intact when a spreadsheet wants to treat them as numbers.

The identifier 00124 and the number 124 can refer to different things. A spreadsheet may interpret the first as a number and remove its leading zeros.

Choose the column type before importing

If a column holds identifiers rather than quantities, import it as text. For a CSV in Excel, use the text/CSV import workflow and set that column to Text before loading it. Available controls vary by Excel version.

For manual entry, format the destination cells as Text before typing or pasting. Changing the format after zeros disappear does not reconstruct the original identifier.

Invented identifier

00124 → import as text → 00124

Display formatting is a different choice

A custom number format such as 00000 displays the number 124 as 00124. The underlying value remains numeric. Use this only when you intentionally want a fixed-width display, and check the exported result separately.

Check the round trip

Try a small test file with 00124, 00007, and 12400. Import it, export it, and inspect the exported text in a plain-text editor. Then test the receiving application's import settings too.

Keep the original file. If an unknown number of zeros was removed, adding a guessed prefix could produce the wrong identifier. Recover that value from the source instead.

Further reading