How to Keep Leading Zeros in Google Sheets

If you work with numbers in Google Sheets, there is one thing that can often frustrate many users.

When you enter a number in a cell in Google sheets that has leading zeros, it will automatically remove those leading zeros. For example, if you enter 000123, it will automatically remove the first three 0’s and only give you 123 as the result.

But what if you want to keep the leading zeros in Google sheets ?

This is especially useful when you’re working with phone numbers data or data such as ids or social security numbers.

That’s exactly what I am going to show you in this tutorial.

There are multiple methods for keeping leading zeros in Google Sheets, and you can choose the method that works for you (these same methods would also work in Microsoft Excel).

Let’s get started!

How to Keep Leading Zeros in Google Sheets

The reason Excel removes the leading zeros is that these are not relevant for a number. For example, 000123 is equal to 123 and the three zeros are not required.

But if you can change the format of the cell to text, then Google sheets will keep the leading zeros.

The following methods are covered in this tutorial:

1. Adding an apostrophe before the number
2. Changing the format of the cells to text
3. Creating a Custom Format for cells

Let’s deep dive into each of these methods.

Add Apostrophe (‘) Before the Number

This works well when you have to manually enter the data in the cells.

All you need to do is first type an apostrophe (‘) symbol and then type the number.

So if you want the value in the cell to be 000123, you should enter ‘000123.

Adding an apostrophe converts the number you enter into text, and the leading zeros don’t go away.

When you add an apostrophe at the beginning of the number, you would notice that it aligns to the left. This is because now Google Sheets considers it as text (by default text aligns to the left and numbers to the right in Google Sheets).

Note: Even though adding an apostrophe changes the number to a text, you can still use it in formulas and calculations as if it is a number

Change the Cells format to Text

Another quick way to keep the leading zeros is by changing the format of the cells so that anything that you put in it is considered text.

Once you have done this, you can type a number of leading 0’s (or copy and paste such values into these cells) and it will not lose those numbers at the beginning.

Below are the steps to change the format of the cells to text:

1. Select the cells for which you want to change the format
2. Click the Format option in the menu
3. Hover the cursor over the Number option. It will make some additional options show up on the right.
4. Click on ‘Plain text’

Now when you enter a number which has leading zeroes, Google Sheets will keep them.

While these cells have been formatted as text, once you have the numbers in these cells, you can also use them in calculations if you want.

Create a Custom Format with Fix Number of Digits

In Google Sheets, you can create a custom format where you force a cell to display a specific number of digits.

For example, you can format the cells in such a way that it always shows 6 digits. And in case there is less number of digits, the custom format will show you leading zeros to make sure the end result is six digits.

So, if you enter the number 123 in the cell where this custom formatting has been applied, it will automatically change to 000123

Below are the steps to apply this custom formatting in Google Sheets:

1. Select the cells for which you want to change the format
2. Click the Format option in the menu
3. Hover the cursor over the Number option. It will make some additional options show up on the right.
4. Hover the cursor over the ‘More Formats’ option. It will further show some options.
5. Click on ‘Custom number format’
6. In the ‘Custom number formats’ dialog box, enter the following format: 000000 (6 zeros)
7. Click on Apply

Now, when you enter any number in these selected cells, it would at least show you six digits.

If the number is less than six digits, leading zeros will be added. And if the number has more than six digits, nothing would change.

This will only impact the numbers. In case you enter any text value in these cells, it would be displayed as expected.

Note: When you change the custom number format of a cell, it only changes the way the number is displayed. It doesn’t change the underlying value. So if you enter 123, it would remain as 123 in the cell but would be shown as 000123.

So these are three simple ways you can use to keep the leading zeroes in Google Sheets.

Hope you found this tutorial useful.

You may also like the following Google Sheets tutorials:
Adding a trend line in Google Sheets

• How to Hide Zero Values in Google Sheets
• How To Remove Duplicates In Google Sheets (3 Easy Ways)
• How to Hide Columns In Google Sheets
• Remove the First Character from a String in Google Sheets
• How to Round Numbers in Google Sheets
• How To Add Leading Zeros in Excel [Easy Guide]