How to keep the leading 0 in a cell?

Last Updated on 02/05/2023 by Frédéric LE GUEN

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

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.

1. Select the column where you want to write your data
2. Turn cell data type to Text
1. 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

=LEFT(C2,2)

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

How to keep the leading 0 in a cell?

Last Updated on 02/05/2023 by Frédéric LE GUEN

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

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.

1. Select the column where you want to write your data
2. Turn cell data type to Text
1. 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

=LEFT(C2,2)

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