How to keep leading zeros in Excel
'00123. For a whole column, select it and set Home > Number Format > Text before you type or paste.Ways to keep the zeros
- Format the column as Text first
- Type an apostrophe before the value
- Use a custom number format
- Rebuild zeros that are already gone
- Import CSV files with the column set to Text
1Format the column as Text first
Excel converts anything that looks like a number into a number, and numbers have no leading zeros. A column formatted as Text keeps exactly what you type.
Fix
Select the column, choose Home > Number Format > Text, then type or paste the values. Formatting after the zeros are gone doesn't bring them back.
Use this for IDs, part numbers and phone numbers: values you never calculate with.
2Type an apostrophe before the value
An apostrophe tells Excel to store the entry as text. It doesn't show in the cell.
Fix
Type '00123. The green triangle that appears only warns that a number is stored as text; you can ignore it.
3Use a custom number format
If the values must stay numbers (for sorting or lookups against numbers), keep them as numbers and only change how they look.
Fix
Select the cells, press Ctrl+1, choose Custom and type one 0 for each digit, such as 00000 for 5-digit ZIP codes. 123 then shows as 00123.
4Rebuild zeros that are already gone
If every value should have the same number of digits, you can add the zeros back with a formula.
Fix
=TEXT(A2,"00000")
=RIGHT("00000"&A2,5)Both return text. Copy the results and use Paste Special > Values to replace the originals.
5Import CSV files with the column set to Text
Opening a CSV file by double-clicking it lets Excel convert every column, so zeros disappear on the way in.
Fix
Use Data > From Text/CSV, click Transform Data, then right-click the column heading and choose Change Type > Text before you load it.
In recent Microsoft 365 versions you can also turn this off for good: File > Options > Data > Automatic Data Conversion, then clear the option that removes leading zeros.
Still stuck?
- Values stored as text don't match numbers in lookups. If VLOOKUP returns #N/A, see VLOOKUP not working.
- Phone numbers with spaces or dashes (such as 010-1234-5678) already stay as text.
- Long numbers like card numbers lose their last digits as well. See Long numbers turn into E+.