How to Add a Zero in Front of a Number in Excel - Tutorial

Sharing buttons:

Have you ever had to deal

with a set of numbers in Excel

where you need for all of them to be the same number

of characters but they're not,

so you have to figure out how to add

the right number of zeros in front of them?

Well in this Excel tutorial, we're going to go over

a few ways you can add a zero in front of a number in Excel,

or what is sometimes called leading zeros.

So on my screen is a series of ID numbers.

Some have 3 digits, some have 4,

but we need for all of them to have 6 digits,

beginning with zeros.

The first option to add zeros in the front

is to add an apostrophe

and your zeros to each cell.

It's an easy option to use if you only have

a small number of numbers to add zeros to.

Otherwise, doing this to each cell

can quickly become time consuming.

This works by converting your numbers

to a text format, letting you add

however many zeros you want.

Also, Excel flags these as an error.

That's why you see this green triangle

in each of the cells we converted to text.

It's an alert letting you know

that the content of those cells are now stored as text.

If you need to use these for any calculations,

you should leave them formatted as numbers,

but you likely are not doing any calculations on IDs.

So if you want to hide the green triangle,

select all of the cells with it,

and then click this exclamation point

and select Ignore Error.

So let's undo this.

The second way you can add zeros

in front of numbers is by using the repeat or REPT function.

REPT works by repeating whatever character you want

a certain number of times.

For example, if we need for these numbers

to have a total of 6 characters

beginning with zeros, we can use =REPT,

and then where it says Text, we'll type zero

because that's the character that we want repeated,

followed by the number of times

that we want that character repeated,

so we'll need to add 2 zeros to this cell,

and then we'll combine it with cell A2

by using the ampersand and typing A2.

But you have to be careful when using REPT

and copying it down to all the other cells,

because it works great if all of the cells

have the same number of characters.

However, notice that some of our cells

are only 3 digits, so adding 2 zeros

in the front does not get them

to the 6 digit formatting that we need.

So to get around this, we can use an IF function

along with REPT.

So let's delete this, and we'll type =IF.

So we need for the IF function to evaluate

if the number of characters in the cell

is 6 or less, and to do that, we'll use

the LEN function, which counts the number

of characters in a cell, of A2, we'll say less than 6,

and we're going to assume that none of our IDs

have more than 6 characters.

And if this is True that the cell

has less than 6 characters, then we'll say

REPT, repeat the number zero, and the number of times

will be the difference between the number

of characters and 6.

So we'll say 6 minus LEN of A2,

and we'll use the ampersand to combine that

with the current contents of cell A2,

and should the cell already have 6 characters,

i.e., if it's False, then simply return

to contents of cell A2.

And then we can copy this down

to create a version of our cells

with the right number of zeros in front of each ID number.

So let's delete these.

A third, and my preferred way, to add zeros

in front of a number in Excel is by formatting the cells

so they display the number of zeros you need.

So to do this, I've selected all of the cells

that I want to format in this way,

right-click, and select Format Cells.

Let's move this up.

In the Number tab, go to Custom, and here

where it says General, under Type, simply type six zeros,

and you'll see in the Sample box

how this will display, and click OK.

For more Excel tutorials like this, click on these links.

Thanks for watching!