.

But, what about zip codes or phone numbers?

Some data, such as zip codes or phone numbers, must keep the leading 0. But in this situation…. they are not numbers but text 🤔😮

It’s pretty simple to understand this point. This type of data does not require operations to be done with each other. There is no reason to multiply a zip code by another number …. it’s ridiculous 😂

#1 Solution: Add a quote before your data

To keep the leading 0, you can add a quote before your number. This will automatically convert your number to Text data type.

💡 Tips: A text is always framed on the left in a cell

This solution is not the best. In fact it requires you to add all the quotes one-by-one 😒

#2 Solution: Change the cell type to Text

This time, you will ‘force’ the data type of your column.

1. Select the column where you want to write your data
2. Turn cell data type to Text

3. Enter your data (it will be framed on the left automatically)

#3 Solution: Change one option in Excel 365

If you work with Excel 365, there are new options in the tab Data

1. Go to File > Options
2. Then go to the Data tab
3. Unchecked the option Remove leading zeros and convert to a number

And just like that, even if your cells are in Standard data type, you keep the leading 0 in your cells

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

=LEFT(C2,2)

The result will be wrong because for the first cell you will return 60 and not 06 as expected