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:

How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel

 

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

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+