How to keep leading zeros in a CSV opened in Excel
Open a CSV in Excel and an ID like 00123 turns into 123. Excel reads the file with no type information, decides the column looks numeric, and stores a number, so the leading zeros are gone before you see the data.
Why it happens
A CSV is plain text with no data types. Excel guesses a type for each cell, and a string made only of digits is treated as a number. Numbers have no leading zeros, so ZIP codes, product SKUs, account numbers and employee IDs are the most common casualties.
Ways to keep the zeros
- Import instead of double-clicking. In Excel, use Data > From Text/CSV, set data type detection to “Do not detect data types”, then load. Every column arrives as text.
- Set the column to Text during import. Choose Transform Data and set the affected columns to Text. Formatting a column as Text after the file is already open does not bring the zeros back, because they are already gone.
- Avoid custom formats like 00000. They only change how the number looks. The stored value is still 123, and a fixed width fails for IDs of different lengths.
- Use KeepLeadingZeros.com. Load the CSV, review the flagged columns, and export an XLSX where those columns are real text cells. The tool checks every exported cell against the original.
Do not re-save as CSV
Opening the file in Excel and saving it again as CSV writes whatever Excel stored, so any damage becomes permanent. Keep the original CSV untouched and work from the XLSX.