Why Excel Drops Leading Zeros and How to Keep Them

Excel removes leading zeros because it reads anything that looks like a number as a number, and the number 00482 is simply 482. Microsoft's explanation is that Excel is "trained to look for numbers being entered in cells, not numbers that look like text, like part numbers or SKU's." The fix depends on whether the data is not yet entered or has already lost its zeros.

1. Before you enter or paste: format as Text

Microsoft's guidance for data that has not been typed in yet is to format the destination first. Select the column or range, press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells, choose Text on the Number tab, and only then paste or type the values. Excel then keeps the zeros as typed.

The order matters. Changing a column to Text after the zeros are gone does not bring them back, because the stored value is already 482.

2. After the zeros are gone: the TEXT function

If a file arrives with 482 where it should say 00482, restore the zeros with a formula. Microsoft gives this pattern: =TEXT(value, "00000"), where "the number of zeros in the formula represents the total number of characters you want."

For a five-character ID in A2, use:

=TEXT(A2, "00000")

The last row is Microsoft's own example of adding leading zeros to reach seven characters. The third row shows what happens with a number that is already long enough: nothing changes. A format code pads short values but does not cut long ones, so check that your longest ID fits the code.

Google Sheets has the same idea. Its documentation says 0 forces display of zeros when a number has fewer digits than the format specifies, with the example TEXT(12.3,"000.00") producing 012.30. So =TEXT(A2,"00000") works in both apps for whole numbers.

3. Built-in formats for ZIP codes and similar IDs

Excel has special formats that already include the zeros. Microsoft lists four: Zip Code (00000), Zip Code + 4 (00000-0000), Phone Number, and Social Security Number (000-00-0000). Special formats vary by locale, and you can build your own under Format Cells, Custom.

Note the difference between a cell format and the TEXT function. A custom format such as 00000 changes how the number is displayed while the cell still holds a real number (482). TEXT produces actual text, "00482". Both look the same on screen, but they behave differently in calculations and lookups.

4. The catch: the result is text

Microsoft's note on TEXT is direct: it converts numbers to text, which "may make it difficult to reference in later calculations." Its advice is to keep the original value in one cell and use TEXT in another, and to build other formulas from the original.

This also matters when you look up IDs. A lookup for the number 482 will not match the text "00482", and the reverse is true. Decide on one form for each ID column and use it everywhere, including the lookup value.

5. Going back to numbers

If you need to turn text values back into numbers, Microsoft says to multiply by 1, like =D4*1, or use the double-unary operator, =--D4. Either way the zeros disappear again, since 00482 as a number is 482. Do this only for columns you want to calculate with, not for identifiers.

6. A related problem: 1.22E+07

Long numeric strings get a similar treatment. Microsoft calls the result scientific notation: 12,200,000 can display as 1.22E+07. Excel converts numbers longer than 12 digits automatically when a cell is formatted as General, and longer than 15 digits when formatted as Number. The advice is the same as for leading zeros: format the cells as Text before you input or paste long numeric strings such as account or tracking numbers.

Microsoft's format-code section also shows the display form =TEXT(12200000,"0.00E+00"), which returns 1.22E+07, so scientific notation is a formatting choice as well as a side effect.

7. A quick decision guide

• IDs, ZIP codes, SKUs, phone numbers: treat as text. Format the column as Text before data goes in.
• Already lost the zeros: use =TEXT(A2,"00000") with the right number of zeros, then copy and paste as values if you want to drop the formula.
• Anything you will add, average or compare numerically: keep it a number, and use a custom format only if you need zeros on display.
• Very long digit strings: Text format first, then paste.

Details above follow the Microsoft and Google help pages as read in October 2026.

8. Key takeaways

• Excel stores 00482 as 482 because it reads the entry as a number.
• Format cells as Text before entering or pasting data, since doing it afterward cannot restore the zeros.
• =TEXT(A2,"00000") restores them; the number of zeros sets the total length.
• TEXT returns text, so keep the original number in its own cell and match lookups type for type.
• Numbers over 12 digits in General format (15 in Number format) turn into scientific notation unless formatted as Text first.