Excel Number Format for Leading Zeros: How It Works

How-to basics · Updated November 19, 2026

To display leading zeros in Excel without changing a number’s value, apply a custom number format: select the cells, press Ctrl+1, choose Custom, and enter 00000 for a five-digit display. For example, the number 123 will appear as 00123; the stored value remains 123.

What a leading-zero number format does

Excel normally removes leading zeros from numbers because zeros at the beginning do not change a number’s mathematical value. Entering 00123 into a General-format cell, for example, typically leaves 123 in the cell. A custom number format tells Excel how to display that numeric value, including how many zeros to show in front of it.

This distinction matters when the digits are identifiers rather than quantities. Product codes, employee numbers, postal codes, and account references may need a fixed appearance even though arithmetic on them is not useful. A number format can provide that appearance while keeping the cell’s underlying value numeric.

  • 00000 displays 7 as 00007 and 123 as 00123.
  • 000 displays 7 as 007 and 123 as 123.
  • 000000 displays 123 as 000123.

The number of zero placeholders sets a minimum display width. If a value has more digits than the format, Excel shows all of them; a five-zero format does not cut 123456 down to five digits.

Apply the format in Excel for Windows

In Excel for Microsoft 365 on Windows, use the Format Cells dialog to apply the format:

  1. Select the cells or column you want to format.
  2. Press Ctrl+1 to open Format Cells.
  3. On the Number tab, select Custom in the Category list.
  4. Click the Type box, enter 00000 (or the required number of zeros), and select OK.

You can also open Format Cells by right-clicking the selection and choosing Format Cells. If the cells contain values of different lengths, choose a width that meets the longest required code, such as six zeros for six-character numeric codes. Applying the format to an entire column before entering values can make a template easier to use, though it does not guarantee that pasted or imported data will retain the format.

On a Mac, select the cells and press Command+1 to open Format Cells; then choose Custom and enter the format code. Excel for the web may expose different number-format editing options from the desktop app. If you cannot create or edit the required custom format there, open the workbook in the desktop version of Excel.

How zero placeholders and other symbols work

In a custom number format, 0 is a required digit placeholder. Excel displays a digit when one exists and adds a zero when a digit is missing at that position. That is why 00000 pads shorter values to five places.

Two other placeholders are useful to know. The # placeholder shows a digit when needed but does not add zeros for missing positions. The ? placeholder also leaves missing positions blank, while reserving space for them so decimal points can line up in a column. For leading-zero identifiers, a sequence of zero placeholders is usually the clearest choice.

A format can also include literal punctuation. For example, 000-00 displays the number 12345 as 123-45. This changes only the appearance: Excel still stores 12345, and a formula that refers to the cell uses that numeric value rather than a separate hyphenated code. Use punctuation in the format only if a consistent pattern is appropriate for every value in the selected cells.

Choose between formatting a number and storing text

A custom format is convenient when you need a fixed-width display but want the values to remain numbers. A cell displaying 00123 with the format 00000 still has the value 123. It can be used in arithmetic as 123, and a formula bar or a formula that reads the value will not treat the displayed zeros as part of the number.

That behavior is not always right for codes. If the leading zeros are part of the identifier itself, store the code as text. Text is often safer for values such as postal codes, barcode strings, account references, or identifiers that should never be summed. It also avoids Excel’s limit of 15 significant digits for numeric values: digits beyond that precision may not be retained accurately in a number. Long codes should be imported and stored as text.

To enter a short code as text manually, format the destination cells as Text before typing or pasting the codes. Another option is to type an apostrophe before a code, such as '00123; Excel uses the entry as text, while the apostrophe is not displayed in the cell. Formatting a cell as Text after a value has already been converted does not restore zeros that Excel discarded. You must re-enter or reconstruct the original code.

For a numeric value in A2 that you want returned as a five-digit text string, use =TEXT(A2,"00000"). If A2 contains 123, this formula returns the text 00123. Unlike applying a cell format, TEXT produces a text result, which can be useful for combining the padded value with other text. Be aware that formulas or lookups can behave differently when one side contains text and the other contains numbers.

Pad text codes with a formula

If A2 contains an identifier as text and you need a minimum width of five characters while retaining any longer codes, use =IF(LEN(A2)<5,REPT("0",5-LEN(A2))&A2,A2). With the text value 123 in A2, the result is the text 00123; with 123456, the result remains 123456. This formula does not shorten longer values.

Here, LEN counts the characters and REPT supplies just enough zeros to reach the target width. The ampersand joins the zeros to the original text. If A2 is a number rather than text, Excel may already have removed zeros that were typed at the start, so this approach cannot recover an original code such as 00123 from the numeric value 123. Use a known width and source data that has not lost the original characters.

Formulas are useful when you need a separate output column for an export or report. To make the results permanent, copy the formula results and use Paste Special > Values. Keep a copy of the original data if you may need to revise the padding rule later.

Preserve leading zeros when importing data

Importing is a frequent source of missing zeros. When Excel interprets a column of digit strings as numbers, it may convert entries such as 00123 to 123. Applying a custom format afterward can make every value appear five digits wide, but it cannot establish whether an original code was 00123, 000123, or a different length unless you already know the required width.

For a CSV or text file in Excel for Windows, use Data > From Text/CSV rather than relying on a double-click to open the file. In the import process, inspect the preview and, when necessary, choose Transform Data. In Power Query, set the identifier column’s data type to Text before loading it. The exact import controls can vary with the file and Excel version, so check that the preview preserves the original characters.

For data pasted from another system, check a few representative rows before applying a format or formula. Look for codes of different lengths, values beginning with zero, and very long strings. If the source has already converted codes to numbers, request a text export or a source column that retains the original characters; formatting cannot recover information that is no longer present.

Common problems and practical checks

  • The zeros disappear when typing: Excel may be using General format and interpreting the entry as a number. Apply a custom format for display-only zeros, or use Text for an identifier whose characters must be preserved.
  • Every value has too many zeros: The format width may be longer than intended. Change 00000 to the required width, such as 0000.
  • A long number changes unexpectedly: Numeric cells retain at most 15 significant digits. Store long identifiers as text instead of trying to solve this with a longer number format.
  • A lookup does not match: One value may be numeric and the other text. A numeric 123 with a five-digit display is not the same underlying value as the text string 00123. Make the data types consistent and check the actual cell values.
  • The format seems to vanish in another system: Custom number formats are Excel display instructions, not extra characters stored in the value. An export or another application may not carry that instruction along. Check the exported file itself when the literal zeros are required.

For a quick check, select a formatted cell and look at the formula bar. If it shows 123 while the grid shows 00123, the zeros come from formatting. If the formula bar shows 00123 as text, the characters are part of the stored value. This check helps you choose the right approach before using the data in formulas or sending it elsewhere.

Frequently Asked Questions

What custom format adds leading zeros in Excel? Use a string of zero placeholders, such as 00000 for a minimum five-digit display. Select the cells, press Ctrl+1, choose Custom, and enter the code.

Does a leading-zero number format change the cell value? No. It changes the displayed appearance only. A value of 123 formatted as 00000 still has the numeric value 123.

How do I keep zeros when entering an ID? Store the ID as text by formatting the cells as Text before entry or by prefixing an individual entry with an apostrophe. If a fixed-width numeric display is sufficient, use a custom number format instead.

Can Excel add zeros without changing the original number? Yes. Apply a custom format such as 00000. If you need a separate text result, use the TEXT function with a matching format code.

Why did Excel remove zeros from a CSV code? Excel likely interpreted the digit string as a number while opening or importing the file. Import the column as Text and verify the preview; a number format can display a known width but cannot determine the original code’s lost characters.

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.