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!

DOWNLOAD EXCEL WORKBOOK

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

=TEXT(

Add Leading Zeros in Excel

 

STEP 2: The TEXT arguments:

value

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

=TEXT(B9,

Add Leading Zeros in Excel

format_text

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:

728x90

 

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Show The Percent of Parent Row Total With Excel Pi... 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 PARENT ROW TOTAL calculation.This is a new calculation in Excel 2010 and onwards.This option will immediately calculate the percentages f...
Top Excel Data Cleansing Techniques Data cleansing is an important activity within Excel and one that we find ourselves doing day in day out, sometimes without even knowing it.So what is data cleansing?In the words of Oz du Soleil, Excel MVP, it is like "Hercules being sent out to capture the three-he...
Extracting Data with Excel’s LEFT Formula What does it do?It returns the first character or characters in a text string, based on the number of characters you specify.Formula breakdown:=LEFT(text, )What it means:=LEFT(look in this cell, extract X characters)There are times when you will need t...
Rank Smallest to Largest With Excel Pivot Tables  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 RANK SMALLEST TO LARGEST calculation.This option will immediately calculate the rankings (1 being the smallest value) for your values, allowin...