3 Quick ways to fix the problem of Excel losing leading zeros

Excel losing leading zeros when entering data or entering phone numbers is a common situation for users. However, this situation is not difficult to overcome. Refer to TipsMake 's article now to know how to do it!

If you often work with Excel, you are no longer unfamiliar with the situation of missing leading zeros when typing a series of numbers starting with 0. In fact, this is not Excel's error but the default. of this tool. Therefore, the way to solve the problem of Excel losing leading zeros is also quite simple. TipsMake will reveal the 3 quickest ways to help users type a series of numbers (especially phone numbers) with the first 0.

Instructions on how to quickly and quickly handle the case of Excel missing leading zeros

Losing zeros in Excel is a problem many people want to solve but don't know how to do. Here are the ways to write without losing zeros in Excel that you need.

Share how to add 0 to the beginning of a number range in Excel

To handle the problem of typing in Excel with the leading zero missing, apply the following measures:

Add a comma before entering

Specifically, if you just need to enter a few series of numbers with 0 in front. So to not lose the number 0, you just need to add a ' sign before entering. For example, if vfif is entered directly as 034567 > Then the user enters '034567 > After pressing Enter, the number 0 will not be lost.

Note:

At this time, in the number input box, a check mark will appear on the left side of that box. Initially, when hovering over the mouse, there will be a yellow warning with an exclamation mark. To fix it, just click on the arrow of this yellow warning > Select Ignore Error and you're done!

If you enter a phone number, the user can also add a – or a . in between the numbers. So the leading 0 will not be lost.

Text format for multiple cells

In case you need to enter data with 0 in multiple cells as well as entire ranges and rows. Then you do it in the following way. However, this method will not lose the leading zero, but you cannot perform calculations with these series of numbers.

1. Step 1 : You highlight the cells where you want to enter data with the number 0 at the beginning (Hold and drag the mouse or press Ctrl + A if you want to select them all).
2. Step 2 : Right-click on this highlighted area > Click Format Cells .
3. Step 3 : In the new interface that appears, select Number > Click on Text in Category > Click OK to save. Then, you just need to enter the numbers into the cells you have selected (After pressing Enter, you will also see a blue check mark. If you want to uncheck this mark, do it like method 1)

3/ Set to automatically add a 0 in front of the number line

If you want the number to still be able to be calculated after changing, then apply this method.

1. B1 : You proceed to highlight the cells you want to enter data as instructed in method 2 > Then right-click on this highlighted area > Select Format Cells .
2. Step 2 : At the Number tab > Click on the Custom line > Then, in the Type section , add the number 0 to this line. However, users should note that entering the number 0 must be greater than the number of digits in the current number range you want to enter. For example, the current number sequence is 123456 (including 6 digits). If you want to convert to 0123456, enter 7 zeroes in the Type line. If you want to display 00123456, enter 8 zeroes. After entering, click OK to save.

How to fix Excel error missing 3 zeros at the end

Besides the case of missing leading zeros, some of you also encounter the situation where Excel loses zeros at the end. In this case, try adding a ' to convert it to Text as instructed above to see if it fixes it.

Or some brothers have shared the trick: In the Excel file that is open and has the error of missing a zero at the end > Select File > Select Options > Select the Advanced tab > In the Editing options section > uncheck the line Automatically insert a decimal point > Click OK to save. Then check the problem again.

Wishing you success in overcoming the case of Excel missing the leading 0!

Micah Soto

Update 14 December 2023