How to have
A 0 In Front Of A Number In Excel
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!
