Leading Zeros in Excel: How to Keep Them

To keep a leading zero in Excel, store the entry as text or apply a custom number format before entering it. Use text for codes and IDs whose digits must stay exactly as typed; use a custom format such as 00000 when you want a number to display at a fixed length. The right choice depends on whether the value needs to behave like a number or simply be preserved as written.

What You’ll Need

A Microsoft Excel workbook in Excel for Microsoft 365 on Windows. The main steps are similar in Excel for Mac; notes are included where menus may differ.
The values you want to enter or change, and, for fixed-width numbers, the required number of digits. For example, decide whether every code should have five digits.
If the zeros have already disappeared, the original values or a known rule for their correct length. Excel cannot infer how many zeros were removed.

Step-by-Step

Decide whether the value is a number or a code. Phone numbers, postal codes, employee IDs, product codes, and account references are usually identifiers, not quantities. If you will not calculate with the value and must retain every character, treat it as text. If it represents a number that should still be used in arithmetic but must appear at a fixed width, use a custom number format instead.
For new code entries, format the cells as Text first. Select the target cells or entire column. On Windows, go to Home > Number Format (the dropdown that may show General) and choose Text. Then type the value, such as 00427, and press Enter. Excel will preserve the two starting zeros. Setting the format before typing matters: changing a cell to Text after Excel has already converted the entry does not bring the zeros back. On Mac, select the cells and use the number format dropdown on the Home tab to choose Text.
Use an apostrophe for a one-off text entry. In a General-format cell, type an apostrophe immediately before the value, for example ‘00427, and press Enter. Excel uses the apostrophe as an instruction to store the following characters as text; it does not show the apostrophe in the cell. This is handy for an occasional entry, but for a whole column, formatting the column as Text first is easier to manage.
Apply a custom number format when the displayed length should be fixed. Select the cells, press Ctrl+1 on Windows to open Format Cells, choose the Number tab, select Custom, and enter a format made of zeros in the Type box. For five-digit display, enter 00000, then select OK. A value of 427 will display as 00427, while the underlying value remains 427. On Mac, select the cells and open Format Cells from the Format menu or use Command+1, then choose Custom and enter the same format. Custom number formats affect how values look, not the actual stored number.
Choose a format length that matches your rule. In a custom format, each zero is a required digit position. The format 00000 displays values with at least five digits by adding zeros to shorter values. A value that is already longer than five digits is not shortened. If codes can have different legitimate lengths and zeros are meaningful characters, store them as text instead of applying one fixed-width format.
Check a few cells before filling the full column. Enter a short value, a value already at the expected length, and, if relevant, a longer value. Confirm that the display matches the code rule. If using Text, check that the first zero remains after you press Enter. If using a custom format, click a cell and look at the formula bar: it will show the underlying number without display-only zeros. This check helps confirm that you chose the intended method before applying it to many records.
For an existing list, choose a repair based on what remains. If the values are still numbers and all should have the same length, apply the appropriate custom format. If they need to be text, first confirm the desired width or consult the original source, then convert or recreate the entries as text. Merely choosing Text after the zeros have vanished will not restore them. For a CSV or other import, use Excel’s import process and set the relevant column to Text before loading it, rather than opening the file by double-clicking and letting Excel guess the data type.

Common Mistakes

Formatting after Excel has removed the zeros. When you enter 00427 into a General-format cell, Excel normally interprets it as the number 427. Applying Text afterward changes the cell’s format but cannot tell Excel that two zeros were originally present. Recover the intended value from a trusted source, or add zeros only if a documented fixed-width rule confirms how many belong there.

Using a custom number format for codes that need to remain text. A display such as 00427 can look right while the stored value is still numeric 427. That can cause problems when exchanging data with another system or comparing it with a text value. Use Text when the leading zeros are part of the identifier itself, not just a display preference.

Assuming the cell width controls the number of digits. Widening a column, changing its alignment, or adding decimal places does not preserve leading zeros. Use Text or a custom format; those are separate from column size and alignment.

Opening a CSV without checking how Excel interprets it. Excel may automatically convert digit strings to numbers when opening or importing a file. If the zeros are important, import the file through the Data tab and specify Text for the relevant column. Keep an untouched copy of the original file so you can re-import it if the values were converted.

Using an unsuitable number format for long identifiers. Excel stores numeric values with up to 15 significant digits of precision. A long account or product code entered as a number may lose digits as well as leading zeros. For long digit-only identifiers, use Text before entry or import; a custom number format does not make Excel store an arbitrarily long exact identifier as text.

Tips

Set up a whole column once. If you regularly add IDs, select the column and set its format to Text before entering or pasting new records. This reduces the chance that a later entry will be converted differently from earlier ones.
Keep the source’s width rule with the workbook. If every code must have six characters, note that rule in a heading or nearby instruction. A format such as 000000 is useful only when that six-digit rule is real and consistent.
Use paste carefully. Pasting from another workbook or a web page can bring in values and formatting together. If Excel strips zeros, undo immediately if possible, set the destination cells to Text, and paste again. When the source is a CSV, import it with the column type set correctly.
Distinguish display from stored value. With a custom format, the formula bar shows the numeric value, while the cell shows padded zeros. With Text, the value itself includes the zeros. This difference matters for sorting, matching, and sharing data with other programs.
Keep a clean source copy. Before changing or importing a large list, save a copy of the original file. This is especially helpful if you need to compare the imported results or restore codes that Excel converted.

Troubleshooting

The zeros disappear as soon as I press Enter. The cell is probably set to General or Number. Undo the entry, select the cells, choose Text from Home > Number Format, and type the values again. For just one value, prefix it with an apostrophe. The apostrophe should not appear in the cell after you confirm the entry.

I changed the format to Text, but the zeros are still missing. The original zeros were likely discarded when Excel first interpreted the entry as a number. Text formatting cannot reconstruct information that is no longer stored. Find the correct code in the source system or file. If every code has a known fixed length, a custom number format can display the missing padding for numeric values, but use it only if that rule is confirmed.

The cell looks correct, but another system or lookup does not recognize it. Check whether the cell is numeric with a display format or actually contains text. A visually padded number and a text code can be different values even if both appear as 00427. Confirm the required data type with the destination system, then store the column consistently as Text or as numbers with the agreed display rule.

Zeros disappear when I open or import a CSV. Re-import the original CSV using Data > From Text/CSV on Windows. In the import preview, choose Transform Data if needed, select the code column, and set its data type to Text before loading. In Excel for Mac, use the available text/CSV import option on the Data tab; labels and preview controls can vary by release. If the file has already been saved after conversion, the original zeros may not be recoverable from that copy.

A custom format does not show the number of zeros I expected. Count the zero characters in the format. The format 00000 sets a minimum display width of five digits, not a maximum. Also check that the cell contains a numeric value; a custom numeric format may not behave as expected for text. If entries are variable-length codes, select Text instead.

Frequently Asked Questions

Can I type a zero at the start without changing the cell format?
Yes. Type an apostrophe first, as in ‘00427. Excel stores the entry as text and hides the apostrophe in the cell. For repeated entries, set the cells to Text before typing.

Does a custom format add zeros to the actual value?
No. It changes only the display. A cell containing 427 with the format 00000 displays as 00427, but its underlying numeric value remains 427.

Which method is best for ZIP or postal codes?
Use Text when the code is an identifier or when codes may have different lengths. A fixed-width custom format is suitable when the relevant codes follow a known, consistent length rule and should remain numeric for your workflow.

Can I restore zeros after saving and closing the workbook?
Only if you know the correct value from another source or can apply a verified fixed-width rule. Excel does not retain leading zeros that were discarded during number conversion, so the workbook alone may not contain enough information to restore them.

Will the leading zeros remain when I export the workbook?
It depends on the export format and the system opening it. Text entries generally preserve the characters, but CSV files do not retain Excel’s custom cell formatting. If exporting, check the resulting file in a text editor or import a sample into the destination system to confirm the zeros are present.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.