How to Keep Leading Zeros in Excel as Text or Numbers

To keep a leading zero in Excel, format the cell as Text before entering the value, or type an apostrophe before it, such as '0074. If you only need the zero to appear on screen while keeping the value numeric, apply a custom number format instead. Choose the text method for codes and identifiers, and the number format for values that you will calculate with.

What You’ll Need

Excel for Microsoft 365 on Windows, or a version of Excel that supports text and custom number formats.
The values you want to enter or fix, and, if applicable, the number of digits each value should display.
If the values come from a CSV file, keep the original file unchanged until you confirm the imported results.

You do not need a formula or add-in. The important decision is whether the zero is part of the actual value, as it is for a postal code or product ID, or merely a display choice for a number.

Step-by-Step

Decide whether your value is an identifier or a number. Phone numbers, postal codes, account references, and product codes are usually identifiers. Excel should treat them as text because you do not add or subtract them. Use a custom number format when the value is genuinely numeric and you want a consistent number of digits to show, for example, numbers in a report. This choice determines which method will behave correctly later.
For new IDs or codes, select the cells before typing. Click a cell or drag to select the range where the values will go. On the Windows ribbon, choose Home > Number Format (the drop-down that may currently say General) and select Text. Now enter the value, including its first zero, and press Enter. For example, typing 0074 into a cell already set to Text keeps all four characters.
Check that the value is stored as text. Select the cell and look at the formula bar. It should show the full entry, including the first zero. Text values often align to the left by default, while numbers usually align right, but alignment can be changed, so do not rely on it as the only check. You can also click the cell and verify that the Number Format drop-down says Text. A small green triangle may appear because Excel is warning that a number is stored as text; for an ID, that is normally intentional.
For a quick one-off entry, type an apostrophe first. In a General-formatted cell, type an apostrophe and then the value, such as '0074, and press Enter. Excel uses the apostrophe to mark the entry as text; it does not appear as part of the value in the cell. This is convenient for a few entries, but setting a whole range to Text first is clearer when you are entering many records or preparing a reusable template.
To display a fixed number of digits while keeping a numeric value, use a custom format. Select the cells, then choose Home > Number Format > More Number Formats. In the Format Cells dialog, open the Number tab, select Custom, and enter the required number of zero placeholders in the Type box. For a five-digit display, enter 00000, then select OK. A numeric value of 74 will display as 00074, while its underlying value remains 74. Choose a placeholder count that matches the minimum width you need.
Format existing values according to how they should be used. If Excel has already turned 0074 into the number 74, applying Text afterward does not restore the missing zeros; Excel has already discarded them. If you know every ID must contain five digits, select the cells and apply the custom format 00000 to display five digits. If the value must truly be text, re-enter it after setting the cells to Text, or recover the original characters from a source file or backup. Do not assume a display format has changed the stored value.
Import a CSV with ID columns set to Text before loading. In Excel for Microsoft 365 on Windows, choose Data > From Text/CSV, select the file, and review the preview. Choose Transform Data to open Power Query. Select the ID column and set its data type to Text using the data-type control or Transform > Data Type > Text. Then choose Home > Close & Load. Check the loaded column for the original zeros. If you load a CSV by opening it directly, Excel may automatically interpret digit strings as numbers and remove their zeros.
Save and verify a copy when the data matters. Save the workbook as an Excel workbook, such as .xlsx, if you need to retain formatting and worksheet settings. Then close and reopen it, or inspect a few cells and the formula bar, to confirm the zeros remain as expected. If you must deliver a CSV, remember that CSV files do not retain Excel cell formatting. Text values may export with their zeros, but a numeric value displayed with a custom format may be written without the extra zeros. Open a test export in a text editor to check the actual file contents.

On Excel for Mac, the same principles apply: set the cells to Text before entry, or use Format Cells to apply a custom format. Ribbon labels and the location of import controls can differ by release. Excel for the web supports cell formatting, but its CSV import and Power Query options may differ from the Windows desktop app; use desktop Excel when you need to control column types during a CSV import.

Common Mistakes

Formatting as Text after Excel has removed the zero. A format change does not know whether 74 used to be 074, 0074, or 00074. Apply Text before entry or recover the required width from a reliable source.
Using a custom number format for data that must remain text. The format can make an ID look right in a worksheet, but the stored value is still numeric. An export, copy, or another system may not preserve the displayed padding. Use Text when those zeros are part of the identifier itself.
Setting a custom format with too few placeholders. The format 000 displays 74 as 074, not 00074. Count the required digits and use that many zeros in the Type box.
Opening a CSV by double-clicking it. Excel can guess that a column contains numbers and remove leading zeros during import. Import through Data > From Text/CSV and set the relevant column to Text before loading.
Assuming CSV keeps worksheet formatting. CSV stores plain values, not custom number formats. A cell that looks like 00074 in Excel may be saved as 74 if it is numeric. Confirm the exported file itself when another person or system will use it.

Tips

Use Text for values that identify something and are not used in arithmetic. This includes codes made entirely of digits; looking numeric does not make an identifier a number.
Use custom formatting when you want numbers to line up to a fixed width in a report but still need to sum, compare, or calculate with them.
Apply the intended format to a whole entry column before pasting or typing a batch. For pasted data, check a few cells afterward; content copied from another source can bring its own formats or may already have lost zeros.
Keep a clean source copy of imported data. If an import changes a code, you can return to the original rather than trying to infer how many zeros were present.
When the required width varies by record, do not use one fixed custom format as a substitute for the original text. Store the exact identifier as text so that each value retains its own length.

Troubleshooting

The first zero disappears as soon as I press Enter. The cell is probably set to General or a numeric format. Undo immediately with Ctrl+Z on Windows (Command+Z on Mac), set the cell or range to Text, and enter the value again. Alternatively, type an apostrophe before the digits. If you did not undo and the zero is already gone, you need to know the intended length to reconstruct it reliably.

The cell shows the zero, but it disappears when I copy or export. You may be using a custom number format, which changes the display rather than the stored number. If the recipient needs the leading zero as actual data, convert or re-enter the values as text and check the destination format. For CSV delivery, inspect the exported file in a plain-text editor; do not judge it only by how the worksheet looks.

My imported CSV still loses zeros. Re-import the unchanged source file using Data > From Text/CSV. In Power Query, select the affected column and set its type to Text before choosing Close & Load. If the source file itself no longer contains the zeros, Excel cannot recreate them without a known pattern or another authoritative copy.

The green warning triangle appears on my IDs. Excel often flags digit-only text as a number stored as text. If the column contains identifiers and the zeros must be preserved, the warning is not a data problem; you can leave it alone. Avoid choosing a conversion option that changes the cells to numbers unless you are sure the values are quantities and do not need their zeros.

My values show #### instead of the digits. This usually means the column is too narrow to display the formatted value. Widen the column by dragging the right edge of its heading, or double-click that edge to AutoFit. The hashes do not normally mean the leading-zero format has failed.

Frequently Asked Questions

How do I type 00123 in Excel without losing the zeros?
Set the cell to Text before typing 00123, or type an apostrophe first: '00123. The apostrophe is an entry marker and does not show as part of the cell value.

Can I keep leading zeros and still do calculations?
Yes, but decide which representation you need. A custom number format such as 00000 keeps the underlying value numeric and displays a fixed width. If the zeros are part of an identifier, store the value as text; convert or handle it deliberately if calculations are later required.

Can Excel restore zeros that have already disappeared?
Not from the shortened value alone. Excel cannot tell how many zeros were removed. If all values should have a known width, a custom format can display that width, or you can rebuild text values using that rule. Otherwise, retrieve the original data from the source.

Will a custom number format keep zeros in a CSV file?
Not necessarily. CSV does not save Excel number formats, and a numeric value may be exported without its displayed padding. If the receiving system requires the zeros, use text data and check the exported CSV itself.

Why does Excel remove zeros from a phone number or postal code?
Excel interprets digit-only entries as numbers by default. A leading zero does not change a number’s numeric value, so Excel may omit it. Set the cells to Text first because phone numbers and postal codes are normally identifiers, not values for arithmetic.

Does the apostrophe become part of my data?
No. When you type an apostrophe at the start of an entry in Excel, it tells Excel to treat the following characters as text. The apostrophe is not displayed as part of the cell contents, though you may see it in the formula bar depending on the entry and Excel version.

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.