Excel Keeps Removing Leading Zeros? Preserve IDs Before Importing
If Excel turns 00123 into 123, the value has been interpreted as a number rather than an identifier. For product codes, account references or other IDs, preserve the column as text before importing or entering the data. Adding a display format afterwards is a different operation and may not recover the original code.
Decide whether the column is an identifier
Ask what arithmetic would mean for the values. Adding two quantities can produce a useful total. Adding two customer IDs usually does not. That is a clue that a numeric-looking code should be treated as text.
Consider a sample inventory file containing 00123, 01230 and 12300. All three codes have five characters. If the first two lose their leading zeros, the sheet no longer preserves the original strings. The problem becomes more serious when another system expects an exact character match.
Control the type during import
Microsoft’s guidance recommends using the text/CSV import and transformation process to set identifier columns to Text. In supported desktop versions, start with Data → From Text/CSV, open the transformation editor and inspect the column type before loading it.
If an automatic type-conversion step has already changed the values, return to the step before that conversion and set the original column to Text. Merely converting 123 to text produces the string “123”; it cannot tell you whether the source was “00123,” “0123” or “123.”
For manual entry, set the destination cells to Text before pasting the source codes. Test a few representative values first instead of pasting an entire list and discovering the conversion later.
Formatting and stored values solve different problems
A custom number format can make a number appear with a fixed width, but that is a display rule. It is useful when you deliberately want a fixed-width presentation. It is not evidence that the original imported identifier survived.
| Situation | Better starting point |
|---|---|
| Exact product or account code | Preserve the source as text |
| Numeric quantity with a display convention | Choose an appropriate number format |
| Zeros already lost and width unknown | Recover from the original source |
Check the handoff, not just the screen
Keep an untouched source file and compare sample IDs after importing. If the next step is an export, test the exported file in the receiving application too. A correct-looking Excel cell does not settle how another tool will interpret it.
Check an ID with leading zeros, one without them and one with letters. Record the expected result before testing. For the file-format decision, read when to send CSV and when to keep XLSX.
