How to keep the leading 0 in a cell?

How to keep the leading 0 in a cell?
Last Updated on 02/05/2023
Reading time: 2 minutes

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.

Add a quote before you cell 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
Change the cell format 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.

Change the number format to keep the leading 0

In this situation, you can display the zip code with 5 digits but the real value of the cell is 6000

The real value of the cell is different

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

wrong result because of the cell display

Leave a Reply

Your email address will not be published. Required fields are marked *

How to keep the leading 0 in a cell?

Reading time: 2 minutes
Last Updated on 02/05/2023

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.

Add a quote before you cell 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
Change the cell format 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.

Change the number format to keep the leading 0

In this situation, you can display the zip code with 5 digits but the real value of the cell is 6000

The real value of the cell is different

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

wrong result because of the cell display

Leave a Reply

Your email address will not be published. Required fields are marked *