Do you have a lot of numbers with an uneven number of digits in your Excel list?

Do you want to make them uniform by adding leading zeros to them?

Well, it’s a pain to add zeros in front of them one by one!

Thankfully, Excel allows you to add leading zeros with one single formula!


STEP 1: We need to enter the TEXT function in a blank cell:


Add Leading Zeros in Excel


STEP 2: The TEXT arguments:


What is the value that you want to add leading zeros on?


Add Leading Zeros in Excel


How many leading zeros do you need?

If we want our number to be 6 digits long, then type in 6 zeros: “000000”

=TEXT(B9, “000000”)

Add Leading Zeros in Excel

Apply the same formula to the rest of the cells by dragging the lower right corner downwards. Your leading zeros are now ready!

Add Leading Zeros in Excel

How to Add Leading Zeros in Excel

Add Leading Zeros in Excel

Helpful Resource:



If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Excel Filter by Selection When you have an array of data in Excel you can quickly select an item and press the AutoFilter button which will filter that selection in your data. You can then go over to another column within your data and select another item, apply the same steps above and further filter ...
CountIf Formula in Excel What does it do? Counts the number of cells that matches your specified condition Formula breakdown: =COUNTIF(range, criteria) What it means: =COUNTIF(range of cells to check, condition to check against) Do you have a scenario where you want to count the num...
Lock & Protect Formula Cells If you have a workbook with lots of formulas and you want to protect those formulas from being amended by other people who share your workbook, then you can! You need to follow these steps: 1. Press the Go To Special shortcut CTRL+G 2. Select the Constants box and press ...
Show The Percent of Column Total With Excel Pivot ... Excel Pivot Tables have a lot of useful calculations under the SHOW VALUES AS option and one that can help you a lot is the PERCENT OF COLUMN TOTAL calculation. This option will immediately calculate the percentages for you from a table filled with numbers such as sales data, ...