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.
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.
Your number should now have a 0 in front of it.
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.
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.
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.
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.
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.
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.
The number 90 should now have two leading zeros (0090).
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.
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(
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.
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”).
Press your comma key (,) and select A5 and close your bracket.
This should give you the formula =CONCAT(“0000”,A5)
Press your Enter key.
The information from A5 and the zeros should now be together, giving your number leading zeros.
Option 3: Ampersand
The Ampersand symbol (&) allows you to morph information together from different cells.
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”).
Now, press your Ampersand key (Shift + 7) and select the original number (87 in cell A8).
This should give you the formula =”0000”&A8
You should now have you number with four leading zeros in front of it.
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.
Click onto More Number Formats.
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.
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.
Your numbers should all now be pre-set to six characters.
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.
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.