Add Leading Zeros in Excel: Formatting and Formulas

Formulas & functions · Updated November 20, 2026

To show a zero before a number in Excel, select the cells and apply a custom number format such as 00000; Excel will display 123 as 00123 while keeping the value numeric. If the zeros are part of an ID, code, or phone number and must be retained as actual characters, store the entry as text instead. The right method depends on whether you need the value to calculate or simply preserve its appearance.

What You’ll Need

  • An Excel workbook with the values you want to change, or a blank column where you can enter or create them.
  • The required length, if every value should have the same number of digits. For example, use five digits for codes such as 00123 and 00008.
  • A decision about whether the result must remain numeric. Use number formatting for values that need to calculate; use text for identifiers whose leading zeros are meaningful.

The instructions below are for Excel for Microsoft 365 on Windows. In desktop Excel for Mac, the Format Cells shortcut is Command+1 rather than Ctrl+1; the dialog options are similar. Excel for the web may not offer the same controls for creating a custom format. If you do not see the Custom option there, use desktop Excel or the formula/text approaches below.

Step-by-Step

  1. Decide what the zero means. If you are displaying a number at a fixed width—for example, every product code must appear as five digits—use a custom number format. The underlying value remains numeric, which is useful for calculations and sorting by numeric value. If the zero is part of a label, account number, postal code, or phone number, the value is usually better treated as text. Text preserves exactly what was entered and avoids treating an identifier as a quantity.
  2. Use a custom format to display zeros without changing values. Select the cells or entire column. Press Ctrl+1 on Windows, or Command+1 on Mac, to open Format Cells. On the Number tab, choose Custom in the Category list. Click the Type box, enter 00000 for a five-digit display, and select OK. Change the number of zero characters to match the required width: 0000 displays four digits, while 000000 displays six.
  3. Check the displayed result and the underlying value. With the five-digit format applied, a cell containing 123 displays as 00123, and a cell containing 8 displays as 00008. The formula bar still shows 123 or 8, not the padded version. That is expected: the format affects presentation, not the stored number. If the cell is used in addition or another calculation, Excel still works with the original numeric value.
  4. Enter future values as text when zeros must be retained. Before typing or pasting identifier values into blank cells, select those cells. On the Home tab, open the Number Format dropdown in the Number group and choose Text. Now enter values such as 00123. Excel should keep the zeros as characters. You can also type an apostrophe before an individual entry, such as '00123. The apostrophe signals text entry and does not appear in the cell display.
  5. Use a formula to create a padded result in another column. This is useful when the source values already exist and you want to keep them unchanged. For a five-character result, with the original numeric value in A2, enter =TEXT(A2,"00000") in B2. For example, if A2 contains 123, B2 displays 00123. Fill the formula down for the other rows by dragging the fill handle at the lower-right corner of B2, or double-clicking it when adjacent rows contain data. The TEXT function returns text, not a numeric value.
  6. Add exactly one zero with concatenation if that is what you need. A fixed-width format pads shorter values to a specified length; it does not necessarily mean “put one zero before every value.” If you want to prefix one zero to the contents of A2, enter ="0"&A2 in B2. If A2 contains 123, the result is 0123. Fill down to apply it to more rows. This formula produces text. It will add another zero if you run it on a value that already has a leading zero, so check the source data first.
  7. Replace formulas with values only if needed. Formula results update when their source cells change. If you need a fixed copy, select the results, press Ctrl+C, then right-click the selection and choose Paste Special > Values. On Mac, copy the cells and use the Paste Special options to paste values; the precise menu presentation can vary by Excel release. Check the pasted results before deleting the source column. Do not use this step if you want the padded output to keep updating automatically.
  8. Save and check a few examples. Verify a short value, a value already at the required length, and a blank cell. If you are importing or sharing the workbook, confirm that the recipient will see the intended zeros. A custom format may not travel with a value copied into a plain-text file, while a text result carries its zeros in the characters themselves.

Choosing the correct width: a format with five zero placeholders sets a minimum displayed width of five digits. It does not truncate longer numbers: 123456 still displays as 123456. If values have different required widths, a single custom format may not suit every row. Use separate formats or a formula that applies the correct rule to each record.

Common Mistakes

  • Changing the format to Text after zeros have disappeared. If Excel has already converted 00123 to the number 123, choosing Text does not restore the lost zeros. You need to re-create them using a suitable format, formula, or the original source file.
  • Confusing what you see with what is stored. A custom format can make a numeric 123 look like 00123, but a formula bar or exported value may still contain 123. Use a text result when the zeros must be part of the data itself.
  • Choosing a format with the wrong number of placeholders. 00000 pads to at least five digits. It will not make every result exactly five characters if some source values are longer, and it will not add only one zero in every case.
  • Expecting identifiers to behave like quantities. Phone numbers, postal codes, and many ID numbers are labels, not numbers for arithmetic. Storing them as text is often safer, especially when they include leading zeros, plus signs, spaces, or punctuation.
  • Typing an apostrophe into a formula or expecting it to display. The apostrophe is an entry marker for a manually typed value. It is not a replacement for number formatting, and it usually will not appear in the cell after entry.

Tips

  • Use the shortest method that matches the goal. Choose a custom format when you need the value to remain numeric and only its display needs padding. Choose Text or a text-producing formula when the zeros are part of an identifier.
  • Set the format before pasting or importing. Preformat a destination column as Text before entering codes. This reduces the chance that Excel interprets strings such as 00123 as the number 123.
  • Keep the original column until you verify the output. A helper formula column makes it easy to compare source values with padded results. Once you have checked the output and saved a copy, you can decide whether to retain the source.
  • Use formats consistently across a whole field. If product codes should all be six digits, apply the same six-zero format to the complete column rather than formatting cells one at a time. Consistency makes later review and data entry easier.
  • Remember that sorting differs for numbers and text. Numeric values sort by magnitude; text values sort as character strings. For identifiers, text sorting can be appropriate, but use the same data type throughout the column to avoid confusing mixed results.

Troubleshooting

The zeros still do not appear. Confirm that you selected the cells containing the values, not just a nearby range. Open Format Cells again and check that the Type field contains the intended number of zero placeholders, such as 00000. If a formula is producing the result, check that its output column is wide enough and that you are viewing the formula result rather than editing the formula.

The zeros appeared, but they vanish when I copy the data. If you used a custom number format, the zeros may be only a display effect. Copying into a plain-text editor, CSV, or another application can expose the underlying number. Create a text result with a formula such as =TEXT(A2,"00000") instead, using the source value in A2, or convert the displayed results to values before exporting. For a CSV import, use Excel’s Data > From Text/CSV workflow and set the identifier column’s data type to Text before loading, where that option is available.

Excel removed zeros while opening a CSV. Close the workbook without saving over the original file if the original data may still be intact. Re-import it using Data > From Text/CSV rather than double-clicking the CSV. In the import preview or Power Query editor, set the relevant column to Text before loading it. If Excel has already saved the shortened values over the only copy, a number format can restore a known fixed-width display, but it cannot determine how many zeros each original item had unless you know the required width.

The formula displays a number without zeros or shows an error. Check that the formula uses straight double quotation marks and the correct cell reference. The TEXT formula needs a number format inside quotes, as in =TEXT(A2,"00000"). If your Excel installation uses a different list separator because of regional settings, formulas with multiple arguments may require semicolons instead of commas; use the separator expected by your Excel settings. Also check that the source cell is not empty or containing unexpected text.

I need to remove the added zeros. If you applied a custom format, select the cells, press Ctrl+1, and choose General on the Number tab. The original numbers will display without padding. If you created text with a formula, keep the source values and remove the helper column after confirming you no longer need it. If you overwrote the source, undo immediately with Ctrl+Z if the action is still available, or restore a backup; changing text back to a number will not reliably identify which zeros were original.

Frequently Asked Questions

How do I add one zero before every number in Excel?

If you need exactly one leading zero, use a helper column. With the original value in A2, enter ="0"&A2 in B2 and fill down. The result is text, so it preserves the added character. If you instead want every value padded to the same length, use a custom format or the TEXT function.

How do I add zeros until every value has five digits?

Select the numeric cells, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, select Custom, and enter 00000. A value of 42 will display as 00042. For a separate text result, with the value in A2, use =TEXT(A2,"00000"). Values longer than five digits are not shortened.

Will adding a leading zero change the number?

A custom number format changes only how the number is displayed; 00123 is still stored as 123. A text entry or a formula such as TEXT or concatenation creates text containing the zero. That distinction matters if you plan to calculate with the result or export it to another system.

Can I use a custom format for phone numbers or postal codes?

You can use one when the values follow a consistent numeric pattern and only need a display change. However, phone numbers and postal codes are generally identifiers, and some contain leading zeros, country codes, or other characters. Storing them as Text is usually more dependable than treating them as numbers.

Why does Excel remove the zero when I type it?

Excel normally interprets entries made of digits as numbers, and numbers do not retain leading zeros. Format the blank cells as Text before typing, or enter an apostrophe before an individual value. If Excel has already removed the zero, restore it with a format or formula using the known required width.

Can I make Excel keep leading zeros in a CSV file?

A CSV file does not preserve Excel’s custom number formats. If you export a numeric value that only looks padded, its zeros may be absent in the CSV. Create actual text values before export, then check the resulting file. When bringing a CSV into Excel, import the identifier column as Text so Excel does not convert it to a number.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.