Keep 0 in Front of a Number in Excel Without Losing It

How-to basics · Updated November 20, 2026

To keep a leading zero in Excel, store the entry as text: format the cells as Text before typing or paste an apostrophe before the value, such as '00123. If the entry should remain a number for calculations, apply a custom number format such as 00000 instead. Choose text for IDs, postal codes, and codes; choose a number format when the value is genuinely numeric and should still calculate.

Excel normally treats entries made only of digits as numbers. Because leading zeros do not change a number’s value, Excel removes them from the display. The methods below let you control whether Excel preserves the digits as text or displays them as padded numbers.

What You’ll Need

  • Excel for Microsoft 365 on Windows, or access to Excel for the web or Excel for Mac if that is where your workbook is open.
  • The cells or column where you want the values to appear.
  • For custom formatting, the number of digits each entry should display, such as five digits for codes from 00000 to 99999.
  • If you are fixing existing values, the required length or original source data. Excel cannot infer how many zeros were removed.

Step-by-Step

  1. Decide whether the entry is a number or an identifier. Use text for values that identify or label something, including postal codes, account references, product codes, and employee numbers. You usually will not add, subtract, or average these values. Use a custom number format for values that represent quantities and need to remain numeric, such as a four-digit sequence number that may be sorted or used in calculations. This distinction matters: changing the appearance of a number is not the same as storing its leading zeros as characters.
  2. For new IDs or codes, format the cells as Text before entering them. Select the target cells or the entire column. On Windows, go to Home > Number Format and choose Text. Then type or paste the values, including their zeros, for example 00123. Formatting first tells Excel not to interpret the entry as a number. If you paste a whole column, set the destination cells to Text before pasting. This is the most dependable approach for values that need to retain their exact digits when copied or exported.
  3. For a one-off entry, start with an apostrophe. Click a blank cell, type an apostrophe followed by the digits, and press Enter. For example, enter '00123. Excel uses the apostrophe to mark the entry as text; it does not show the apostrophe in the cell. The displayed value is 00123. This is convenient for a few entries, but for a large list it is easier to format the destination column as Text first. Do not add spaces before the apostrophe or after the digits, because spaces become part of the cell’s text.
  4. For numeric values, apply a custom number format. Select the cells, then open the Format Cells dialog. On Windows, press Ctrl+1, or right-click the selection and choose Format Cells. On Mac, press Command+1, or use the Format Cells command from the cell context menu. Choose the Number tab, select Custom, enter the required pattern in the Type box, and select OK. For a five-digit display, use 00000. The number 42 then appears as 00042, while its stored value remains 42. In Excel for the web, select the cells and use Home > Number Format > More Number Formats to open the formatting options; if the custom-format controls are unavailable in your interface, open the workbook in the desktop app.
  5. Set the format to match the longest required code. Each zero in a custom format is a required display position. A format of 00000 makes 7 display as 00007, 42 as 00042, and 12345 as 12345. It does not cut off a longer number. If codes have different fixed lengths, such as some five digits and others eight, use separate columns or store the codes as text rather than applying one format to all of them.
  6. Repair existing values only after choosing the intended length. If Excel has already changed 00123 to the number 123, apply the custom format 00000 if the only goal is to show five digits while keeping a number. To create text values instead, and if the original IDs are in A2:A100 and every code must have five digits, enter this in B2 and fill it down: =TEXT(A2,"00000"). For an A2 value of 123, the formula returns the text 00123. Copy the results and use Paste Special > Values if you want to replace the formulas with fixed text. Confirm the expected width first: if the original value was 123 and its original code could have been three, five, or six digits, the lost zeros cannot be recovered from that number alone.
  7. Check the result and save the workbook. Click a few cells and inspect the Formula Bar. A custom-formatted numeric cell shows its underlying value there, while the grid displays the padded version. Text entries retain the zeros as part of the value. If another person or system will receive the file, test a copy of the export or transfer first; some formats and applications do not preserve Excel’s display formatting.

Common Mistakes

Applying the format after typing and expecting the original text to return. A custom format can display zeros in front of an existing number, but it cannot know whether 123 used to be 0123, 00123, or something else. Use a known fixed width or retrieve the original values from the source.

Confusing appearance with stored content. A cell displaying 00042 with the custom format 00000 still contains the number 42. That is fine for calculations, but a database or ID system may require the literal text digits. Use Text formatting or the apostrophe method when the zeros must travel as part of the value.

Formatting cells only after pasting a list. If Excel already interpreted the incoming digits as numbers, choosing Text afterward does not restore removed zeros. Undo the paste if possible, set the destination to Text, and paste again. If undo is no longer available, use the original source or a known fixed length to rebuild the entries.

Using too few zeros or adding zeros manually to numeric data. A pattern of 000 only pads values to three positions. Also, typing extra zeros into ordinary numeric cells will not help because Excel strips them again. Choose a format with the correct width, or enter the values as text.

Assuming a CSV file will keep Excel formatting. CSV stores plain text fields, not custom number formats. A number displayed with a custom format may be written without its visual padding when saved as CSV. For IDs, use text values and inspect the exported file before relying on it.

Tips

  • Use a whole-column Text format for recurring imports. If a particular column always contains identifiers, format the destination column once before bringing in new data. This reduces accidental number conversion during manual entry and pasting, though import tools can still apply their own type detection.
  • Keep the fixed width documented. A custom format such as 00000 is easiest to maintain when colleagues know that every code is intended to have five digits. Put the expected width in a column heading or workbook instructions, especially when several code systems are used.
  • Sort and match consistently. Text codes and numeric values are different data types. If you compare or look up codes, make sure both lists store them in a compatible way. For example, text 00123 may not match numeric 123 in every formula or external system, even if the number is displayed with leading zeros.
  • Use the Formula Bar as a quick check. If the cell grid shows five digits but the Formula Bar shows a shorter number, the display is probably controlled by a custom format. If the Formula Bar also shows the leading zeros, the value is likely stored as text.
  • Preserve source data when cleaning a list. Work in a new column or a copy of the workbook while you verify the required width and data type. This gives you a way to compare the repaired values with the original source instead of overwriting information you may need.

Troubleshooting

The zeros disappear as soon as I press Enter. Excel is treating the entry as numeric. Undo the entry, format the cell as Text, and type it again, or re-enter it with an apostrophe at the start. Setting the format after the zeros have disappeared does not restore them.

The cell displays ####### instead of the value. This usually means the column is too narrow to display the formatted number. Widen the column by dragging the boundary at the right of its column heading, or double-click that boundary to AutoFit. The zeros may still be present in the cell’s format even though the display is clipped.

I applied 00000, but a long code still looks different. Required zeros set a minimum display width; they do not truncate values longer than that width. Check the underlying value and confirm whether longer codes are valid. If every entry must have exactly five characters, use text and validate the source rather than relying on a number format to shorten long entries.

My zeros disappear after saving or opening the file elsewhere. Check the file type and the destination application. CSV files do not preserve Excel number formats, and another system may convert digit-only text back into numbers. Keep the values as text where possible, and check the exported data in a plain-text editor or test file before sending a full dataset.

I cannot find the Custom option in Excel for the web. The ribbon and available formatting controls can vary by interface. Try Home > Number Format > More Number Formats. If you still cannot create the required custom format, open the file in Excel for Microsoft 365 on Windows or Mac and apply it in the Format Cells dialog.

Frequently Asked Questions

How do I keep one leading zero in Excel? For an ID, format the cell as Text before typing it, or enter an apostrophe before the digits. For a numeric value that should remain a number, apply a custom format with enough zeros, such as 0 for a single required digit position.

How do I show exactly five digits, including leading zeros? If the values are numeric and five digits is a minimum display width, apply the custom number format 00000. If they must be exactly five characters as identifiers, store them as text and check that no source value is longer than five characters.

Can I add leading zeros without changing a number into text? Yes. Apply a custom number format such as 00000. Excel continues to store the value as a number, so it remains available for arithmetic. The padding is a display effect, not part of the stored numeric value.

Why does Excel remove zeros from a postal code? Excel sees a string of digits and may interpret it as a number. Postal codes are identifiers, not quantities, so store them as text. Format the column as Text before entry or import, or use an apostrophe for an individual value.

Will the leading zeros remain if I save as CSV? Text values generally retain their literal digits in the CSV, but custom number formats are not saved as formatting instructions in a CSV file. If the zeros exist only because of a numeric display format, they may not appear in the exported field. Check the CSV output before sharing it.

Can Excel recover zeros that were already removed? Not from the shortened number alone. You need the original source or a reliable rule, such as every code being exactly five digits. If that rule is certain, a custom format or a text conversion can pad the value; otherwise, guessing may create incorrect IDs.

Related

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.