Mrinmoy Roy
In some cases, a number starts with zero (0). Such as phone numbers, credit card numbers, product codes, postal codes, personal identification numbers, etc. When you insert any of these numbers in an Excel worksheet having a leading zero, Excel automatically removes the zeros (0) from the front side. The way you can skip this problem is to apply the Text format to the numbers. In this article, you will get to know 6 methods to put 0 before a phone number in Excel.
Why Excel Removes Leading Zeros?
When you insert numbers in an Excel worksheet having leading zeros, Excel automatically removes all of them. For example, if you insert 007 in a cell in Excel, you will see only 7 is there. The two zeros before 7 are no more. This happens because any number of zeros before real numbers is meaningless. So, Excel automatically removes them from a cell. It only keeps the digits that make sense. You have to use some other methods to add leading zeros in Excel.
How Can You Retain Leading Zeros in Excel?
There are two possible ways that you can follow to retain the leading zeros in Excel.
- Format the numbers into Text. This will prevent Excel from removing the leading zeros. Put an apostrophe (‘) in front of the numbers. This will change the format into Text.
- You can set the cell format in such a way that it always displays a certain number of digits. For example, you have set a cell to display 7 digits in total. Now, you are inserting only 5 digits. Excel will automatically insert two leading zeros to make it 7 digits in total. This is how you can retain the leading zeros, too.
How to Keep 0 Before a Phone Number in Excel: 6 Methods
1. Apply Text Format to Retain 0 Before a Phone Number in Excel
When you insert a number with leading zeros in a cell, Excel automatically removes that zero. So, you might face problems while inserting phone numbers in your Excel worksheet.
To keep that leading zero of phone numbers, follow the process below:
- Highlight all the cells where you want to keep the numbers with a leading zero.
- Go to the HOME ribbon.
- From the Number group, select the Text format.
So, all the cells in your selected area are now under the Text format.
After that, you can insert any kind of number with a leading zero. This time, Excel won’t remove the 0 from the front side and will keep all the numbers like this:
This is how you can retain the leading zero of a phone number or any kind of number in Excel.
2. Use Apostrophes to Keep the Leading Zeros of a Phone Number in Excel
Using an apostrophe before inserting numbers in an Excel cell is the quickest way to apply the Text format. This is how you can keep 0 before any kind of numbers, such as personal identification numbers, postal codes, credit card numbers, bank account numbers, area codes, product numbers, etc.
All you need to do is,
- Insert an apostrophe (‘) in an Excel cell first.
- Then, type the numbers starting with a zero (0).
- Finally, hit the ENTER button.
Add an extra apostrophe before the numbers will convert the cell format into Text. Thus, Excel won’t remove that zero in this case.
3. Use Format Cells to Keep Leading Zero of a Phone Number in Excel
Besides the two methods to apply the Text format within the cells, you can try this one.
To do that,
- Select all the cells where you want to insert cell numbers.
- Press CTRL + 1 to open the Format Cells dialog box.
- Or you can right-click on the selection area. Then, from the pop-up list, select Format Cells.
Now, from the Format Cells dialog box,
- Select the Number ribbon.
- Navigate to the Text from the Category list.
- Then hit the OK command.
So, you have successfully applied the Text format to the highlighted cells. Now, just enter the numbers with leading zeros. This time, they won’t just disappear from the Excel worksheet.
Read More: How to Format Phone Number with Extension in Excel
Similar Readings
4. Apply Custom Format to Keep the Preceding Zero in a Phone Number in Excel
To apply the Custom format to the cells,
- Highlight the cells to insert numbers with preceding zeros.
- Press CTRL +1 to open the Format Cells dialog box.
- Select the Number ribbon.
- Click on Custom under the Category list.
- Then Insert into the Type box and hit OK.
“0”##########
Here, the zero (0) within the double quotation mark will ensure that 0 stays in a cell in Excel. Then, the following hashes (#) define the number of digits that you want to allow after the leading zero.
This makes 11 digits in total. Now, if you insert 11 digits with a zero at the front, Excel will keep the zero untouched.
So after applying the Custom format within the cells, Excel will keep the leading zero like the image below:
Read More: How to Remove Parentheses from Phone Numbers in Excel
5. Use CONCATENATE Function to put 0 Before a Phone Number in Excel
Using the CONCATENATE function is another way to put zero before a phone number in Excel. This function just merges an extra zero before a series of numbers.
To apply the CONCATENATE function,
- Select cell C5 and type the following formula:
=CONCATENATE("0",B5)In the argument section of the function, “0” is the 0 to add before a string of numbers. Then B5 is the cell address, where the draft version of the phone number is primarily stored.
- After that, hit the ENTER button on your keyboard.
You have applied the formula to a single cell only. To apply the same formula to all the cells, drag the Fill Handle icon from cell C5 to C14.
After applying the formula to all the cells, you will see all the numbers still have their preceding zeros, unlike before.
Read More: Excel Formula to Change Phone Number Format
6. Use Hyphens, Dots, or Spaces to Restore the Leading 0 in Phone Number in Excel
You can insert either Hyphens (-), Dots (.), or Spaces in-between numbers in Excel. If you separate the numbers using signs or symbols, then Excel doesn’t remove the frontline zeros from a number. This is how you can restore the leading zeros in phone numbers in Excel.
Read More: How to Format Phone Number with Dashes in Excel
Things to Remember
📌 By default, Excel doesn’t keep the leading zeros of a number.
📌 You can press CTRL + 1 to open the Format Cells dialog box.
📌 After applying the Text format upon a cell, you will see an error box. You can easily remove the box by clicking on it. Then hit the Ignore Error command.
Download Practice Workbook
You can download the Excel file from the following link and practice along with it.
Conclusion
To sum up, we have discussed 6 methods to put 0 in front of a phone number in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries ASAP.
