How to add leading Zeros in Excel

There are instances in which leading zeroes need to be added to a dataset in Excel, such as when maintaining Transactions IDs, Employee IDs, Zip Codes, Social Security Numbers, etc., In this blog post, we will see the different ways in which we can pad numbers with zeros at the beginning.

Method 1

The TEXT function in Excel can be used to maintain the desired text length by padding numbers with zeros. The following formula pads the number in cell A1 with n number of zeros to maintain a text length of 10.

=TEXT(A1,"0000000000")

Another formula that can produce the same result.

=TEXT(A1,REPT(0,10))

The above formulas are meant for numbers and won’t work with text strings.

Method 2

To pad zeros or any other characters in text strings as well as numbers to maintain a specific length, use the following formulas.

To add zeros in front of the text in cell A1 and maintain a text length of 8,

=REPT(0,10-LEN(A1))&A1



To add # in front of the text in cell A1 and maintain a text length of 5,

=REPT(“#”,5-LEN(A1))&A1

Method 3

Use Custom Formatting to display leading zeros without making any changes to the original content.

Select the cells containing numbers > Right-click > Format Cells > In the Format Cells dialog, go to the tab called Number, select Custom listed under different categories > in the input box under the label Type:, type in the following Custom Number Format and click OK.

0000000000

This method, too, will work only with numbers.

Method 4

In this method, we are making use of the Power Query in Excel to pad Text as well Numbers with leading zeros.

First of all, we need to convert the data into an official Excel Table. Once you have loaded this table into the Power Query Editor, Go to the Add Column tab > click on Custom Column

In the Custom Column dialog, specify a name for the new column and type in the following formula.

=Text.PadStart([#"Text/Number"],10,"0")

Note that Text/Number is the column containing data and Result is the new column with output.