Why does Excel remove the first 0 in a cell?
This is a computer's rule. A computer never keeps the leading 0 for numbers.
But, what about zip codes or phone numbers?
Some data, such as zip codes or phone numbers, need to keep the leading 0.
But in this situation.... they are not numbers but text 🤔😮
In fact, it's pretty simple to understand this fact. This type of data does not require making operations in relation to each other. There is no reason to multiply a zip code by another number .... it's ridiculous 😂
Turn data into Text
So the only solution to keep the 0 is to force the data type in Text.
💡 Tips: A text is always framed on the left in a cell
First method: Add a quote before your data
One of the solutions to keep the leading 0 is to put a quote before the cell's content.
This solution is not the best at all. In fact it requires you to add all the quotes one-by-one 😒
Second method: Change the cell type to Text
That is the best way to proceed.
- Select the column where you want to write your data
- Turn cell data type to Text
- Enter your data (it will be framed on the left automatically)
NEVER apply for a custom format number ⛔⛔⛔
One thing you should NEVER do is to change the number's format to 0000.
In this situation, you can display the zip code with 5 digits but the real value of the cell is 6000
This situation can generate a lot of mistakes. Especially if you use Text functions like LEFT, MID or LEN. Let's say you want to extract the 2 first digits with this formula
The result will be wrong because for the first cell you will return 60 and not 06 as expected