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

Text To Columns: Dates Whenever you download data from an external ERP system like Oracle, SAP, etc, you can have data that is not formatted the way you and Excel likes.Sometimes "Date" values are downloaded as "Text", so you cannot sort in the periodic date format.No worries!  Text to Columns ...
Clear a #REF! Error in Excel When your Excel workbook has formulas that are linked to other workbooks/cells that have been deleted, you will get a #REF! error.This means that the formula refers to a cell that isn’t valid.To get rid of this error message we have to select the cell(s) with this error, ...
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, ...
Extract Numbers Using Flash Fill In Excel  Flash Fill in Excel is a new feature that was introduced in Excel 2013.  Flash Fill allows you to combine, extract, move & transform data that belongs in one column, into a new column.One of the cool uses of Flash Fill is extracting the numbers from your text au...