How to Keep Leading Zeros in Excel
Type 07700 900123 into Excel and you get 7700900123. Type the product code 00456 and you get 456. Excel thinks you've typed a number, and numbers don't have leading zeros, so it helpfully removes them. Here's how to keep them.
First: do you need a number or text?
Phone numbers, account numbers, postcodes and product codes aren't really numbers. You'll never add them up. Store them as text (methods 1 and 3).
If they're genuine numbers that just need to be shown with a fixed number of digits, like invoice numbers you want to count up, use a custom format (method 2).
1. Type an apostrophe first
Type an apostrophe before the number:
'00456
The apostrophe doesn't appear in the cell or when printed, but it tells Excel to treat the entry as text. The zeros stay.
It's perfect for a few entries typed by hand.
2. Use a custom number format
This keeps the value as a real number but always displays it with leading zeros.
1. Select the cells.
2. Press Ctrl + 1 to open Format Cells.
3. Click Custom.
4. In the Type box, type a zero for each digit you want. For a 5-digit code, type 00000.
5. Click OK.
2. Press Ctrl + 1 to open Format Cells.
3. Click Custom.
4. In the Type box, type a zero for each digit you want. For a 5-digit code, type 00000.
5. Click OK.
Now typing 456 shows as 00456, and 12 shows as 00012.
You can add fixed text too. A format of "INV-"00000 shows 456 as INV-00456.
The zeros are only displayed, though. If you copy the cell into another program, or export to CSV, they may be lost.
3. Format the cells as Text before typing
1. Select the empty cells or column.
2. On the Home tab, open the Number Format drop-down and choose Text.
3. Now type or paste your numbers.
2. On the Home tab, open the Number Format drop-down and choose Text.
3. Now type or paste your numbers.
Anything you enter is kept exactly as typed. This has to be done before you type. Changing the format afterwards won't bring the zeros back.
4. Add zeros with a formula
To pad existing numbers to a fixed length, use TEXT:
=TEXT(A2, "00000")
This turns 456 into 00456 as text. Copy and paste as values if you want to replace the originals.
For a phone number missing its first zero, you can simply add it back: ="0"&A2.
5. Turn off automatic conversion (Microsoft 365)
Newer versions of Microsoft 365 let you stop Excel removing leading zeros altogether:
1. Go to File > Options > Data.
2. Under Automatic Data Conversion, untick Remove leading zeros and convert to a number.
3. Click OK.
2. Under Automatic Data Conversion, untick Remove leading zeros and convert to a number.
3. Click OK.
This applies when typing, pasting and opening CSV files. If you don't see this section, your version doesn't have it yet, so use the other methods.
Opening CSV files
Double-clicking a CSV file strips leading zeros straight away. Instead, use Data > From Text/CSV, click Transform Data, right-click the column and choose Change Type > Text. See our CSV import guide for the full steps.
Troubleshooting
There's a green triangle in the corner of each cell. Excel is warning that a number is stored as text. That's exactly what you want here. Select the cells, click the warning icon and choose Ignore Error.
Long numbers turn into 1.23457E+11. That's scientific notation, which Excel uses for long numbers. Use the apostrophe or Text format. Numbers longer than 15 digits (like card or IMEI numbers) must be stored as text, or Excel will change the last digits to zeros.
My VLOOKUP stopped matching. One list has codes as text and the other as numbers. Make them the same type.
Wrapping up
Type an apostrophe for quick entries, format the column as Text before pasting, and use a custom format like 00000 when you need real numbers that look the part.
