If you are trying to write a number in Excel or Google Sheets that starts with zero (0), you may not do that due to the default settings. In that case, you should follow this tutorial to enter zero before a number in Google Sheets and Microsoft Excel. No third-party add-on is required to get it done.
Microsoft Excel and Google Sheets are two life-saving tools that help you create a spreadsheet with different data. Like other tools, they have some limitations or issues too. For example, users cannot write a number like this – 00123 or 000012 or anything like that. It doesn’t matter which row or column you choose; you cannot enter such numbers in either of the tools. However, sometimes, you may need to do that while writing a zipcode, rank or anything else. For that, you should follow these tricks that we mentioned here. There are mainly two different ways to enter zero before a number in Google Sheets and Microsoft Excel.
How to add Zero in front of number in Excel & Google Sheets
To add a zero before a number in Google Sheets and Excel, follow these steps-
- Use an apostrophe before writing a number
- Change cell formatting
- Use function
1] Use an apostrophe before writing a number
It is the easiest method to get the job done, and you can use this technique in Google Sheets as well as Microsoft Excel. You do not need to do anything else apart from typing an apostrophe before writing the numbers. For instance, you want to write 00123 in a cell. To do that, you must select the cell and type this – 00123.
It removes the default formatting of the cell and allows you to write the whole number with zero in the beginning.
2] Change cell formatting
If you need to write a lot of numbers and the first method seems time consuming for you, you should change the cell formatting. That way, it will let you write anything in plain text. To do so, select a cell or the whole column, go to Format > Number > Plain text.
Now you can write any number you want. For your information, these steps apply to Google Sheets. If you are using Microsoft Excel, you should find the same options in a different place.
Make sure that you are on the Home tab. Now, find the Number section and select Text from the respective drop-down menu.
From now onwards, you can type any number you want.
The best thing about the second method is that you can choose different styles for different cells and keep adding various entries.
3] Use custom function
This is the third method you can use to add a zero in front of the number. There are three functions you can use to get the job done.
Use CONCAT
CONCAT function is being used when you need to link a series in Excel. In this case, you can use this function to display the number with leading zero in a different cell. Having said that, make sure that you have a digit in a cell. Then, choose another cell and enter this formula:
=CONCAT("000", A1)
According to the formula, your desired cell will display three zeros in front of the A1 cell’s number. You can change the number of zeros and cells according to your requirements.
Use TEXT
TEXT function works the same way as CONCAT for this purpose. However, the only difference is that you need to enter the number of zero as per the predefined digit. That being said, if you have 123 in A1 cell and you want to add one zero in front of the number, you need to add four zeros. Similarly, if you have a five digit number, you need to add six zeros in the formula.
To use this function, you need to enter this formula:
=TEXT(A1, "0000")
Here, A1 is your cell number.
Use ampersand
You can use ampersand(&) to get the same thing done. It is probably the easiest way to get zeros in front of the existing number. To use it, enter the formula like this:
="000" & A1
You can change the number of zero as per your requirements and A1 is the desired cell number.
Read: How to add Prefix or Suffix to a Range of Cells in Excel
How do I make zeros appear before a number in Excel?
To make zeros appear before a number in Excel, you can go through any method mentioned above. You can change the cell formatting to add zero. However, if you want to display the new number in a different cell, you can use the aforementioned functions. The easiest way to make zeros appear before a number in Excel is by using this formula: = “000” & A1.
How do I add a zero after a number in Excel?
To add a zero after a number in Excel, you can use the CONCATENATE function. For that, you need to choose a cell and enter this formula: =CONCATENATE(A1, “000”). According to the formula, it will fetch the A1 cell and add three zeros after the existing number. You can change or customize it according to your needs. Alternatively, you can change the cell format to Custom and enter this custom format: 0 “0”. Then, you can type zeros after any digit without any problem.
