How to keep the leading 0 in a cell?

How to keep the leading 0 in a cell?
Last Updated on 16/03/2024
Reading time: 3 minutes

How to keep the leading 0 in your Excel cell? This is a computer's rule. A computer never keeps the leading 0 for numbers
3 techniques could be used to solve this problem

  1. Add a quote in the first position of your cell

    This trick will immediately convert the data type of your cell in Text

  2. Force the data type of your cell to Text

    Before to write anything, you can force the data type of your column to Text

  3. Unchecked an Excel option

    With Excel 365, you have an option to keep the leading 0

.

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

Add a quote before you cell content

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
Change the cell format to Text
  1. 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
Excel Option to keep the leading 0

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

Keep Leading 0 with Excel Option

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: 3 minutes
Last Updated on 16/03/2024

How to keep the leading 0 in your Excel cell? This is a computer's rule. A computer never keeps the leading 0 for numbers
3 techniques could be used to solve this problem

  1. Add a quote in the first position of your cell

    This trick will immediately convert the data type of your cell in Text

  2. Force the data type of your cell to Text

    Before to write anything, you can force the data type of your column to Text

  3. Unchecked an Excel option

    With Excel 365, you have an option to keep the leading 0

.

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

Add a quote before you cell content

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
Change the cell format to Text
  1. 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
Excel Option to keep the leading 0

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

Keep Leading 0 with Excel Option

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 *