Excel Not Letting You Put a 0 in Front of a Number?

Excel removes a leading zero when it interprets what you type as a number. To keep the zero as part of an entry such as an ID or postal code, format the cell as Text before typing, or type an apostrophe first: enter '00123 and Excel displays 00123. If the value should remain numeric and only needs to look padded, apply a custom number format such as 00000.

Why Excel removes leading zeros

Excel treats ordinary entries such as 00123 as the number 123. In standard number formatting, a number has no need for zeros before its first nonzero digit, so Excel displays 123. The zeros are not part of the numeric value Excel has stored; they are formatting or text, depending on how you enter the data.

This behavior is useful for calculations. The values 00123 and 123 represent the same quantity, so Excel normally stores both as 123. But many real-world entries that look like numbers are labels, not quantities. A postal code, employee ID, product code, or account reference may need its zeros to remain exactly as supplied. Excel cannot always infer that distinction from the characters you type.

For example, if a product code is always five characters long, 00427 and 427 may identify the same item in one system, but they may be different codes in another. Decide whether the value is a number to calculate with or a string whose characters must be preserved before choosing a fix.

Choose between text and number formatting

Use Text when the zero is genuinely part of the identifier and you do not need Excel to perform arithmetic on the entry. Use a custom number format when the underlying value should remain numeric and the zeros merely fill a fixed-width display. These methods can look alike in a cell, but they behave differently in sorting, calculations, exports, and other programs.

Text: preserves the entered characters, including leading zeros. It is usually best for postal codes, phone numbers, account references, and codes that are not used in arithmetic.
Custom number format: keeps a numeric value while displaying extra zeros. It is useful for sequence numbers or other values that may be calculated but should appear at a consistent width.
Apostrophe: quickly makes one entry text without changing the cell’s number-format setting. Excel does not show the leading apostrophe in the cell.

Do not choose a method just because one makes the worksheet look right. Consider what will happen when someone sorts, adds, exports, or imports the data. A displayed code can look correct while its stored value is still unsuitable for the next step.

Keep zeros by formatting cells as Text

To prepare a range in Excel for Microsoft 365 on Windows, select the cells, then choose Home > Number Format (the format drop-down in the Number group) > Text. Now type or paste the entries. For a single cell, you can also type an apostrophe immediately before the first zero, as in '00731. The apostrophe acts as an instruction to treat the entry as text; it is not displayed as part of the cell contents.

On Windows, Ctrl+1 opens the Format Cells dialog, where you can choose Number > Text. On Mac, Command+1 opens Format Cells; choose Text there. Excel for the web has a Number Format control on the Home tab, though the available controls and dialog layout can differ from desktop Excel.

Formatting the cells first matters. If Excel has already changed 00731 to 731, changing the cell format to Text afterward does not reconstruct the missing zeros. The original information is no longer in that value. You must re-enter the correct code or restore it from a source that still has the original characters.

Text entries are not ordinary numbers. They may not sort in numeric order: text values such as 1, 10, and 2 can sort alphabetically as 1, 10, 2. Arithmetic functions may ignore text values or treat them differently from numbers. That is usually appropriate for identifiers, but it can cause confusion if a column mixes text codes with numeric quantities.

Display leading zeros with a custom number format

If you need a numeric value but want a fixed-width display, select the cells and open Format Cells with Ctrl+1 on Windows or Command+1 on Mac. Select Number > Custom, enter 00000 in the Type box, and select OK. For instance, the numeric value 427 displays as 00427. The stored value remains 427, so Excel can still use it in arithmetic.

Each zero in a custom format is a required digit position. The format 00000 shows at least five digits, adding zeros on the left when needed. It does not truncate longer numbers: 123456 still displays as 123456. A format of 000 would show 7 as 007, but would not turn it into text or change its underlying value.

Formatting changes appearance, not the contents used by other systems. If you copy a formatted cell and paste it into a plain-text editor, or export the workbook to a format that does not preserve Excel’s number formats, the receiving system may get 427 rather than 00427. If the zeros must travel with the value, store the identifier as text instead.

A custom format also cannot recover zeros that were not part of the intended fixed width. Applying 00000 to 427 displays 00427 because you have specified a five-digit presentation rule. It does not tell Excel whether the original entry was 00427, 000427, or simply 427. Choose the width based on a known rule for the data.

Fix data that has already lost its zeros

First check whether the zero is merely hidden by the current display or has been removed from the stored value. Select the cell and look at the formula bar. If the formula bar shows 427 while the cell displays 00427, a number format is supplying the extra zeros. If both show 427, Excel has no record of the original leading zeros in that cell.

When every code has a known fixed length, applying the appropriate custom number format can make numeric values display at that width. For codes that must be preserved as text, re-import or re-enter them after setting the destination column to Text. If you have a reliable reference list, use it to restore the original strings rather than guessing. A code’s intended length is not always obvious from the numeric value alone.

Changing a cell’s format after entry is not the same as converting the original data back. Selecting Text after Excel has stored 427 as a number usually changes how future entries are treated, but it does not turn that existing value into the text string 00427. Likewise, typing zeros into a format does not make those zeros part of a text identifier.

Importing codes from CSV files and other sources

Leading-zero problems often start during import. A CSV file can contain characters such as 00123, but if Excel opens or imports the column as numbers, it may interpret the entry as 123. A CSV file itself does not carry Excel cell formatting, so the way Excel interprets a column when opening it matters.

For a more controlled import in desktop Excel, use Data > From Text/CSV. In the import or Power Query steps, check the affected column’s data type and set it to Text before loading the data if the characters must be preserved. The precise preview and transformation controls can vary with the Excel build and import route, so verify the loaded results rather than assuming the source formatting was retained.

If you receive a workbook rather than a CSV, inspect the cells and their number formats before editing. If the workbook already stores the codes as text, preserve that type when copying or transforming the column. If the source has already converted a code to a number, ask for the original source or a documented fixed-width rule; a missing zero cannot be recovered reliably by Excel alone.

Also watch for long digit strings. Excel supports only 15 significant digits of numeric precision. Longer numeric-looking identifiers can be altered if Excel treats them as numbers, independently of the leading-zero issue. Import such identifiers as Text so their digits remain intact.

Common problems and useful checks

If the suggested fix does not work, check the cell type and the way the data entered the sheet. A format that was applied after typing, a pasted value, or an import step can produce a different result from typing into a prepared Text cell.

The apostrophe appears in the cell: check whether it was entered as part of a pasted string or whether the cell is being displayed as text in another application. When typed directly at the start of an Excel entry, the apostrophe is normally an entry prefix and is not shown in the cell.
Zeros vanish after pasting: the destination may be using General or a numeric format. Format the destination as Text before pasting, or import the source column as Text.
The value looks right but exports incorrectly: a custom number format may be adding display-only zeros. Store the code as text if the exported characters themselves must include the zeros.
Codes sort strangely: text sorts alphabetically, while numbers sort by numeric value. Keep a column consistently typed, and choose the order that matches how the codes are meant to be compared.
Excel changes a long code: treat it as text before entry or import. Numeric precision limits can affect long digit strings, even when they have no leading zeros.

For a quick diagnosis, select a cell and inspect the formula bar, then check its number format through the Home tab or Format Cells. That tells you whether the display is padded by a format or whether the cell contains text. It also helps explain why two cells that look identical may behave differently in calculations or exports.

Frequently Asked Questions

Why does Excel remove the zero when I type a number?
Excel interprets an ordinary digit entry as a number, and leading zeros do not change that number’s value. Use Text for an identifier or a custom number format for a numeric value that should display at a fixed width.

How do I add a zero in front of a number in Excel without changing it?
If you mean without changing the stored numeric value, apply a custom format such as 00000 for a five-digit display. If the zero must be part of the stored characters, enter the value as text instead.

Can I put an apostrophe before a number in Excel?
Yes. Type an apostrophe before the entry, such as '00123. Excel treats the entry as text and normally hides the apostrophe in the cell.

Will formatting a cell as Text restore zeros that disappeared?
No. If Excel already stored 00123 as 123, changing the format cannot know how many zeros were originally present. Re-enter the value, recover it from the source, or apply a fixed-width display format when that rule is known.

Should postal codes and phone numbers be stored as numbers?
Usually not. They are identifiers rather than quantities, so storing them as text avoids losing leading zeros and supports characters such as spaces or hyphens. Use numeric storage only when the data genuinely represents a value for calculation.

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.