Skip to content
Klargrid

Keep leading zeros in Excel

01000 becomes 1000, the thirteen-digit EAN becomes 3.76E+12: Excel sees numbers where there are codes. They are protected on the way in; afterwards, they have to be rebuilt.

In Excel, step by step

  1. When typing

    Format the column as Text before typing, or start with an apostrophe: '01000.

  2. When importing a CSV

    Data, From Text/CSV, Transform Data, then column type: Text.

  3. For display only

    Format Cells, Custom, 00000: the value stays 1000, it shows as 01000.

Pitfalls

  • Beyond 15 digits, Excel replaces the following digits with zeros: a card number is lost as soon as the file opens.
  • The 00000 format only adds the zeros on screen: a CSV export or a VLOOKUP loses them.
  • A code with letters (a Corsican postcode, 2A004) does not go through a number format.

Faster, on the whole file

Leading zeros and long numbers gives codes back their original length (from the column's most frequent length), keeps long numbers as text, and writes a workbook where they will no longer move.

Frequently asked questions

Can I recover the digits of a 16-digit number shown as 1.23457E+15?
Not if the file was saved: Excel only kept 15 significant digits. You have to go back to the source.