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.
Download the Practice Workbook
You can download the Excel file from the following link and practice along with it.
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, when you insert numbers with leading zeros in an Excel worksheet, Excel automatically removes them from a cell. It only keeps the digits that make sense.
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.
6 Methods to Keep 0 (Zero) Before a Phone Number in Excel
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.
Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)
2. Use Apostrophe 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 started 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.
Read More: How to Use Phone Number Format in Excel (8 Examples)
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: Add or Keep Leading Zeros in Excel (10 Suitable Ways)
Similar Readings:
- How to Put Parentheses for Negative Numbers in Excel
- How to Format a Number in Thousands K and Millions M in Excel (4 Ways)
- Excel round to nearest 10000 (5 Easiest Ways)
- How to Round up Decimals in Excel (4 Simple Ways)
- How to Round Off Numbers in Excel (4 Easy Ways)
4. Apply Custom Format to Keep the Preceding Zero in a Phone Number in Excel
To apply the Custom format upon 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: Excel Custom Number Format Multiple Conditions
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 from your keyboard.
You have applied the formula upon 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 upon all the cells, you will see all the numbers still have their preceding zeros unlike before.
Read More: How to Format Number with VBA in Excel (3 Methods)
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 Change Number Format from Comma to Dot in Excel (5 Ways)
Ignore Error in Text Format
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.
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.
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. And please visit our website Exceldemy to explore more.
