Add Leading Zeros in Excel 365: Text or Number?

How-to basics · Updated November 18, 2026

To add leading zeros in Excel 365, select the cells and use a custom number format such as 00000 if you want the values to remain numbers. If the zeros are part of an ID, postal code, or other code that must be preserved as entered, store the entries as text instead.

Choose between displaying zeros and storing them

Excel normally treats entries such as 0042 as the number 42. Numbers do not retain unnecessary zeros at the left, because those zeros do not change the numeric value. The right solution depends on what the value means, not just how you want it to look.

  • Use a custom number format when the cell represents a number and you only need a consistent display, such as a five-digit number.
  • Use text when every character matters, as with account references, product codes, postal codes, or codes that may contain letters.
  • Use a formula when you need to create a padded text result from values already in a worksheet.

This distinction matters when you sort, calculate, look up records, or export data. A formatted number still has a numeric value underneath; text preserves the zeros as characters. If you do not know whether another system expects a number or a code, check its requirements before changing the column.

Display zeros with a custom number format

A custom number format is usually the quickest choice for numeric values. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1 to open Format Cells, choose the Number tab, select Custom, enter 00000 in the Type box, and select OK. The five zero placeholders tell Excel to show at least five digits, filling unused positions on the left with zeros.

For example, the number 42 displays as 00042, while 731 displays as 00731. A value that already has five or more digits is displayed without being cut off. The underlying values remain 42 and 731, so they can still be added or compared as numbers. To show a six-digit width, use six zeros, such as 000000.

You can also reach the format controls from Home > Number, then open the Number Format dialog. On Mac, select the cells and use Command+1 to open Format Cells; the dialog’s tabs and labels may vary slightly by Excel release. In Excel for the web, look under Home > Number Format for the custom-format options available in your workbook.

To remove the padding later, select the cells and change their format to General. This changes the display, not the stored values. A custom format is useful for reports and worksheets, but it is not a substitute for storing a code as text when the zeros must travel with the data.

Enter new values as text

If the value is an identifier rather than a quantity, enter it as text from the start. One straightforward way is to format the destination cells as Text before typing or pasting the codes. Select the range, then choose Home > Number Format > Text. After that, entering 0042 keeps all four characters.

You can also type an apostrophe before an individual entry, for example '0042. Excel uses the apostrophe to treat the entry as text; the apostrophe itself is not shown in the cell. This is convenient for a small number of values, but it is not a practical way to prepare a large imported list.

Be aware that applying the Text format after numbers have already been entered does not restore zeros that Excel discarded. If a cell contains the number 42, changing its format to Text will not reveal whether the original entry was 42, 042, or 0042. You need a known width or a reliable source to reconstruct the intended code.

Text values are appropriate for codes, but they do not behave exactly like numbers. They are not suitable for ordinary arithmetic, and text sorting may not follow numeric order: for instance, a text code beginning with 10 can sort before one beginning with 2. For fixed-width codes, padding every entry to the same length can make sorting more predictable.

Pad existing values with the TEXT function

If a column already contains numbers and you need a separate column of padded text, use the TEXT function. This is useful for creating labels or preparing values for a system that expects fixed-width codes.

With the number 42 in cell A2, enter this in B2 to return a five-character text value:

=TEXT(A2,"00000")

The result is 00042. The format pattern sets a minimum width of five digits, and TEXT returns text rather than a numeric value. Fill the formula down for other rows, then copy the results and use Paste Special > Values if you need a fixed set of results rather than formulas.

Choose the width based on the rules for your data. For example, if codes must contain eight digits, use a pattern with eight zero placeholders. Do not guess the width if leading zeros carry meaning; consult the source data or the system that will receive the codes. If an input can exceed the chosen width, the pattern does not remove its extra digits, so check the resulting column against the required format.

Which method should you use?

Ask what the value represents and what must happen to it later. A quantity such as a count should generally stay numeric, while a product identifier or postal code is often better treated as text. A fixed-width report may need only a display format, even when the underlying values are numbers.

  • For calculations: keep the cells numeric and use a custom format to show leading zeros.
  • For codes that must retain exact characters: store them as text before entry or import.
  • For a converted output column: use TEXT with a format pattern matching the required width.
  • For a mix of digits and letters: use text; number formatting is designed for numeric values.

Also consider the next step. A workbook lookup, database upload, barcode process, or CSV exchange may interpret text and numbers differently. Test a few representative records in the receiving system, especially codes that begin with zero or have different lengths.

Import codes without losing zeros

Opening a CSV file by double-clicking it can let Excel guess the data type. If a column looks numeric, Excel may convert values such as 0042 to 42. For a more controlled import in Excel for Microsoft 365 on Windows, use Data > From Text/CSV, select the file, and review the preview. Choose Transform Data if needed, set the code column’s data type to Text in Power Query, then select Close & Load.

Import controls and available choices can differ by file type and Excel platform. The key is to make the column text before Excel interprets the codes as numbers. If the zeros have already disappeared in the source workbook or file, formatting alone cannot determine how many were originally present.

CSV files do not carry Excel’s cell formatting or data types as workbook metadata. If another application needs the zeros, confirm that the exported file contains the intended character strings. For a one-off export, open the saved file in a text editor or re-import it into a clean workbook as text to verify the actual contents rather than relying only on the worksheet display.

Common problems and misconceptions

  • Zeros disappear after typing: Excel likely interpreted the entry as a number. Format the cells as Text before entering codes, or use an apostrophe for a one-off entry.
  • Changing a cell to Text did not bring zeros back: the original characters are no longer available if Excel already converted the entry to a number. Re-create them only when you know the required width.
  • A formatted cell looks right, but a formula shows a different value: formulas that refer to a custom-formatted number usually use the underlying numeric value. Use TEXT when the formula needs a padded text result.
  • A lookup does not find a matching code: one value may be text and the other numeric, or the strings may have different lengths. Make both lookup columns use a consistent representation and check for spaces.
  • Leading zeros vanish in another program: that program may be interpreting the field as numeric. Import the field as text or use an agreed format for the receiving system.

In particular, a custom format changes how a number appears; it does not turn that number into a code string. Conversely, text that looks like a number is still text, even if Excel displays it without an obvious visual difference.

Frequently Asked Questions

How do I add one leading zero in Excel 365?
For a numeric display, apply a custom format such as 00 to show at least two digits. For an exact text entry, format the cell as Text first or type an apostrophe before the value.

How do I add zeros until every number has five digits?
Apply the custom format 00000 to keep numeric values, or use =TEXT(A2,"00000") in another column to produce five-digit text from the value in A2.

Can I keep leading zeros and still calculate with the values?
Yes, use a custom number format. The displayed zeros are added without changing the underlying numeric value, so arithmetic still works normally.

Why did Excel remove zeros from my ZIP or postal code?
Excel probably interpreted the entry as a number. Postal codes are identifiers, so import or enter them as text. If the original value has already been reduced to a number, you need a trustworthy rule or source to know how many zeros to restore.

Does the custom format change the actual cell value?
No. It changes the display only. Use text entry or a formula such as TEXT when the zeros themselves must be part of the stored result.

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.