May 4

0 comments

Add 0 in front of a number in Excel

By Sharyn Baines

May 4, 2021

CONCAT, CONCATENATE, Formatting, Functions, Number Formats, Text

It can be frustrating when you’re trying to type a zero in front of a value or a number in Excel and it just disappears.

In this blog we will cover easy ways to take control of your zeros.

How to add 0 in front of a number while entering data

Option 1: Apostrophe key

The Apostrophe key allows you to hold onto zeros at the front of your number or value. It does this by converting a cell to a text cell.

Let’s look at an example in Excel.

add 0 in front of number

Here we have the number 90 with no leading zeros.

To add a zero, click into the cell and in front of the number press your Apostrophe key (‘) and add as many zeros as you want. For this example, one zero will be added.

how to enter zero before a number in excel

Press Enter.

Your number should now have a 0 in front of it.

adding a zero before values

This converts the cell into a text cell. Due to this, Excel will send you a warning sign which comes up as a yellow diamond with an exclamation mark inside. The cell that has been converted will also have a green triangle in the top left corner.

how to add 0 in front of number in excel

The warning is to alert you of this change as it can have some ramifications as far as working in Excel but sometimes this is not important for your worksheet.

You can ignore the warning by selecting the drop-down arrow on the warning sign and selecting Ignore Error.

add zero in front of number in excel

Option 2: Change the Number Format

Instead of using your Apostrophe key, you can highlight over an area and change the Number Format from General to Text.

On the Home tab, click the drop-down arrow for the Number Format options (shown in the example below) and select Text.

how to add a zero before values

This means that you can start entering numbers but you now don't need to put the apostrophe in as the cells are already seen as text.

Add leading zeros using a formula

Option 1: Text Function

The Text function can be used to add zeros in front of numbers that already exist in a worksheet.

Let’s look at an example in Excel.

how to enter zero before a number

Column A holds numbers that already exist in the worksheet and you want to add zeros to the front of these numbers.

The value in A5 is being returned into cell B5 using the Text function.

enter zero before a number

The Text function allows you to take a value and set it to a specific number format.

This means it allows you to add zeros to the front of your value but keep it within a set character limit.

In this example, following a comma there are four 0’s inside quotation marks (“0000”).

This is telling Excel that you want four characters in this value.

The 90 in cell A5 is two of those characters.

Press Enter.

The number 90 should now have two leading zeros (0090).

how to enter 0 before a number

This means the number 288 will only have one zero added to the front of it to be 0288.

But what if you wanted to add four zeros in front of every number down the list?

Option 2: CONCAT Function

The CONCAT function has the ability to merge together different information. 

Extra information: The CONCAT function is like the CONCATENATE function. However, the CONCATENATE function is the older version of this function and is still available in Office 365 and the newer versions of Excel. If you are using an older version of Excel substitute CONCAT with CONCATENATE.

Let’s look at another example in Excel.

add zero in front of number

For this example, four leading zeros will be added to the original number (90 in cell A5).

In cell C5, press your equals key and then type CONCAT.

This should give you =CONCAT(

How to enter 0 before number in excel

Useful tip: When you start typing CONCAT, before you’re finished Excel will drop down a list of what it thinks you are wanting to type. If the word you are wanting to write is highlighted then you can press your Tab key and it will automatically fill in the word for you.

how to enter a zero before a number in excel

Now, inside quote marks, type however many zeros you want to add in the front of your original number. In this example, it is four (“0000”).

excel keep leading zeros

Press your comma key (,) and select A5 and close your bracket.

This should give you the formula =CONCAT(“0000”,A5)

adding zero before values (2 fixes)

Press your Enter key.

The information from A5 and the zeros should now be together, giving your number leading zeros.

add 0 in front of number in excel 2 fixes

Option 3: Ampersand

The Ampersand symbol (&) allows you to morph information together from different cells.

adding a 0 before values

In the cell you want your information (in this example it is cell C8) press your Equals key (=) and in quote marks type as many leading zeros as you would like. For this example, it is four (“0000”).

excel add leading zero

Now, press your Ampersand key (Shift + 7) and select the original number (87 in cell A8).

This should give you the formula =”0000”&A8

excel why does zero disappear

Press Enter.

You should now have you number with four leading zeros in front of it.

zero disappears on excel

Option 4: Number formatting

You can also add leading zeros using custom number formatting.

In order to do this, highlight the numbers you want to have leading zeros.

Go to the Home tab and click on the drop-down arrow in the number area.

in excel zero disappears

Click onto More Number Formats.

zero disappears from excel

A Format Cells dialogue box should have now appeared.

Select Custom at the bottom of the list and in the Type box type the amount of characters you want for your numbers. In this example, the numbers have been pre-set to six characters by typing six zeros in the Type box.

excel first 0 disappears

This means if you have a two digit number (for example 90), it would have 4 leading zeros, where a three digit number (for example 288) would have three leading zeros.

Click OK.

Your numbers should all now be pre-set to six characters.

0 disappears from excel

In the formula bar you will see that this only has the original number and no leading zeros. This is because it is actually a number and can still be referenced as a number in functions and formulas.

in excel when i type zero disappears

The cell format should now be set to Special because you have created a special number format.

Useful tip: Use your number formatting to change the look of your number but leave your cell format as number. This is extremely helpful if you are trying to avoid #VALUE errors.

Was this Blog helpful? Let us know in the comments below.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>