How to add leading zeros in Excel

How to keep leading zeros in Excel as you type

For starters, let's see how you can put 0 in front of a number in Excel, for example type 01 in a cell. For this, simply change the cell format to Text:

• Select the cell(s) where you want to prefix numbers with 0.
• Go to the Home tab > Number group, and select Text in the Number Format box.

As soon as you type a zero(s) before number, Excel will display a small green triangle in the top-left corner of the cell indicating that something is wrong with the cell contents. To remove that error indicator, select the cell(s), click the warning sign, and then click Ignore Error.

The following screenshot shows the result:

Another way to keep leading zeros in Excel is to prefix a number with an apostrophe ('). For example, instead of typing 01, type '01. In this case, you don't need to change the cell's format.

Bottom line: This simple technique has a significant limitation - the resulting value is a text string, not number, and consequently it cannot be used in calculations and numeric formulas. If that is not what you want, then change only the visual representation of the value by applying a custom number format as demonstrated in the next example.

How to show leading zeroes in Excel with custom number format

To display leading zeroes, apply a custom number format by performing these steps:

1. Select a cell(s) where you want to show leading zeros, and press Ctrl+1 to open the Format Cells dialog.
2. Under Category, select Custom.
3. Type a format code in the Type box.

   In most cases, you will need a format code consisting of 0 placeholders, like 00. The number of zeros in the format code corresponds to the total number of digits you want to show in a cell (you will find a few examples below).

4. Click OK to save the changes.

For example, to add leading zeros to create a 5-digit number, use the following format code: 00000

By using Excel custom numbers formats, you can add leading zeros to create fixed-length numbers, like in the above example, and variable-length numbers. It all boils down to which placeholder you use in the format code:

• 0 - displays extra zeros
• # - does not display extra zeros

For example, if you apply the 000# format to some cell, any number you type in that cell will have up to 3 leading zeros.

Your custom number formats can also include spaces, hyphens, parentheses, etc. The detailed explanation can be found here: Custom Excel number format.

The following spreadsheet gives a few more examples of custom formats that can show leading zeros in Excel.

And the following format codes can be used to display numbers in special formats such us zip codes, phone numbers, credit card numbers, and social security numbers.

Bottom line: This method is best to be used in situations when you work with a numeric dataset and the results should be numbers, not text. It changes only the display of a number, but not the number itself: leading zeros show up in cells, the actual value displays in the formula bar. When you reference such cells in formulas, the calculations are perfumed with the original values. Custom formats can only be applied to numeric data (numbers and dates) and the result is also a number or date.

How to add leading zeros in Excel with the TEXT function

While a custom number format shows zero in front of a number without actually changing the underlying value, the Excel TEXT function pads numbers with zeros by "physically" inserting leading zeros in cells.

To add leading zeros with a TEXT(value, format_text) formula, you use the same format codes as in custom number formats. However, the result of the TEXT function is always a text string, even if it looks much like a number.

For example, to insert 0 before a value in cell A2, use this formula:

=TEXT(A2, "0#")

To create a zero-prefixed string of a fixed length, say a 5-character string, use this one:

=TEXT(A2, "000000")

Please pay attention that the TEXT function requires enclosing the format codes in quotation marks. And this is how the results will look like in Excel:

For more information about Text formulas, please see How to use the TEXT function in Excel.

Bottom line: The Excel TEXT function always returns a text string, not number, and therefore you won't be able to use the results in arithmetic calculations and other formulas, unless you need to compare the output with other text strings.

How to add leading zeros to text strings

In the previous examples, you learned how to add zero before a number in Excel. But what if you need to put zero(s) in front of a text string like 0A102? In that case, neither TEXT nor custom format will work because they deal with numeric values only.

If the value to be padded with zero contains letters or other text characters, use one of the following formulas, which offer a universal solution applicable to both numbers and text strings.

How to add a fixed number of preceding zeros

To prefix all values in a column (numbers or text strings) with a certain number of zeros, use the CONCATENATE function, or the CONCAT function in Excel 365 - 2019, or the ampersand operator.

For example, to put 0 before a number in cell A2, use one of these formulas:

=CONCATENATE(0,A2)

or

=0&A2

As shown in the screenshot below, the formula adds just one leading zero to all cells in a column regardless of how many characters the original value contains:

In the same manner, you can insert 2 leading zeros (00), 3 zeros (000) or as many zeros as you want before numbers and text strings.

Bottom line: The result of this formula is also a text string even when you are concatenating zeros with numbers.

How to remove leading zeros in Excel

The method you use to remove leading zeros in Excel depends on how those zeros were added:

• If preceding zeroes were added with a custom number format (zeros are visible in a cell, but not in the formula bar), apply another custom format or revert back General as shown here.
• If zeros were typed or otherwise entered in cells formatted as Text (a small green triangle is displayed in the top-left corner of the cell), convert text to number.
• If leading zeroes were added by using a formula (the formula appears in the formula bar when the cell is selected), use the VALUE function to remove them.

The following image shows all three cases to help you choose the right technique:

How to hide zeros in Excel

If you don't want to display zero values in your Excel sheet, you have the following two options:

1. To hide zeros across the entire sheet, uncheck the Show a zero in cells that have zero value option. For this, click File > Options > Advanced, and scroll down to the Display options for this worksheet section:
   
2. To hide zero values in certain cells, apply the following custom number format to those cells: #;#;;@

For this, select the cells where you want to hide zeros, click Ctrl+1 to open the Format Cells dialog, select Custom under Category, and type the above format code in the Type box.

The screenshot below shows that cell B2 does contain a zero value, but it is not displayed in the cell:

Add and remove zeros in Excel an easy way

Finally, good news for users of our Ultimate Suite for Excel - a new tool especially designed for handling zeros is released! Please welcome Add/Remove Leading Zeros.

As usual, we've strived to reduce the number of moves to an absolute minimum :)

To add leading zeros, here's what you do:

1. Select the target cells and run the Add/Remove Leading Zeros tool.
2. Specify the total number of characters that should be displayed.
3. Click Apply.

Done!

To remove leading zeros, the steps are very much alike:

1. Select the cells with your numbers and run the add-in.
2. Specify how many characters should be displayed. To get the maximum number of significant digits in the selected range, click the Get Max Length
3. Click Apply.

The add-in can add leading zeros to both numbers and strings:

• For numbers, a custom number format is set, i.e. only a visual representation of a number is changed, not the underlying value.
• Alpha-numeric strings are prefixed with leading zeros, i.e. zeroes are physically inserted in cells.

Available downloads

Excel Leading Zeros examples (.xlsx file)
Ultimate Suite 14-day fully-functional version (.exe file)