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.
TEXT Function in Excel to add leading Zeros
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.
REPT Function in Excel to add leading Zeros
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
Custom Formatting in Excel to add leading Zeros
We can use Custom Number Formatting in Excel 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.
