By default, when entering numbers into Excel, leading zeros will be removed. This can be an issue when entering phone numbers and IDs. In this article, I will show you several ways to solve this problem and keep the leading zeros.
1. One-time Solution : Keep the Leading Zero as you Type
If you wanted to ensure that the leading zero is kept when typing, enter a single quote before you type the number.
This instructs Excel to store the value as text and not as a number.
When you press “Enter” to confirm, excel will show a green triangle in the top left corner of the cell. Excel is checking that you intended to do that or if you want to convert it to a number.
The green triangle should then disappear. This solution will only work every time you type single quote as shown above. To make excel allow leading zeros all the time, follow the next solutions.
2. Apply Formatting
If you are planning to have a lot of leading zeros in your document, you need to consider this solution.
Select the range of cells you want to format as text. Next, click the “Home” tab, select the list arrow in the Number group, and choose “Text.”
The values you enter into this formatted range will now automatically be stored as text, and leading zeros preserved.
3. Keep Leading Zeros to Make Fixed Width
The previous two options are great and sufficient for most needs. But what if you needed it as a number because you are to perform some calculations on it?
For example, maybe you have an ID number for invoices you have in a list. These ID numbers are exactly five characters in length for consistency such as 00055 and 03116.
To perform basic calculations such as adding or subtracting one to increment the invoice number automatically, it should be stored as a number to perform such a calculation.
Select the range of cells you want to format. Right-click the selected range and click “Format Cells.”
From the “Number” tab, select “Custom” in the Category list and enter 00000 into the Type field.
Entering the five zeros forces a fixed-length number format. If just three numbers are entered into the cell, excel will add two extra zeros automatically to the beginning of the number.
You can play around with custom number formatting to get the exact format you require.