How to Stop Google Sheets from Deleting Leading Zeros

Google Sheets is an excellent tool for working with numbers. But due to some default settings, it keeps deleting leading zeros in a spreadsheet.

This means that when you’re entering data like phone numbers, zip codes, or even social security numbers, you’re going to run into a problem. So, is there a way to fix this issue?

Fortunately, there’s more than one way to prevent Google Sheets from removing leading zeros, and we’ll tell you about them all in this article.

Method One – from Number to Text

One of the simplest ways to prevent Google Sheets deleting the leading zeros in the spreadsheet cells is to disable the “Automatic” setting for formatting numbers.

All you need to do is signify to Google Sheets that anything you enter into the cell is considered the text, even the numbers. Here’s how to do that:

1. Open the Google Sheets document.
   
   
2. Navigate to the toolbar and select “Format”.
   
   
3. From the drop-down menu, select “Number”.
   
   
4. Check the option for “Plain text”.
   

Go back to your Google Sheets file and try entering a few zeros before other numbers. Press enter and see if they stick around. This is a sure way to never miss a leading zero in your spreadsheet.

Important Note: You need to change this setting before entering data or copying and pasting it to the cells. This method won’t work if you’re already working on a document that had leading zeros but was already removed by Google Sheets.

And here’s another thing to remember – make sure that numbers converted to text are entered or pasted as values. That’s the only way to ensure that the text formatting and everything else works as it needs to.

Method Two – Add an Apostrophe

Changing numbers from automatic to plain text is a great solution when dealing with huge amounts of data and when you’re mostly pasting it.

But if you need to enter a lot of numbers manually, and many of them have leading zeros, there’s a better way to go about it. All you need to do is remember to put an apostrophe symbol (‘) in front of the numbers with leading zeros.

For example, ‘000456 will remain 000456 in the Google Sheets cell after you hit enter. The apostrophe mostly does the same thing as the previous method – it changes numbers to text.

And it will also align the numbers to the left side, as it does with text in general. But not to worry. Just because adding an apostrophe changes the numbers to text, you’ll still be able to apply formulas and other calculations when necessary.

Method Three – Use the Custom Formatting

Did you know that Google Sheets lets you use custom formatting to ensure that a cell displays a specific number of digits?

Meaning that if the custom number of digits is six and you enter only three digits, the leading zeros will appear. For example, you’ve entered 789, but if the custom setting is six digits per cell, it will display 000789. Here’s how to change this setting:

1. Open the Google Sheets on your computer.
2. Select all the cells for which you want to change the formatting.
3. Navigate to the toolbar and select “Format”.
4. Hover over the “Number” option so a new menu will appear.
5. Now select “More Formats” and wait for other options to appear.
6. Finally, select the “Custom number format” option.
7. Then, a pop-up window will appear where you can enter the format you need. For example, 00000 and click “Apply”.

If you’re concerned about what will happen if you enter more than the number of digits you’ve listed, there’s no need to be.

The leading zeros do appear when you enter less than a custom number of digits, but if there are more numbers, nothing will happen.

The number will stay as you’ve entered it. Also, keep in mind that the custom formatting of a cell in the spreadsheet won’t change the underlying value of the number which is shown.

You can even add color to the custom formatting. It’s often easier to differentiate between positive and negative numbers in a large spreadsheet.

You can change the color by putting it in brackets like this – [Green]. But keep in mind that the colors have to be written in English.

Zeros Are There for a Reason

We all know that zero isn’t really a number. But we often treat it as such. Its purpose is specific, and that kind of makes it special.

If you need to have leading zeros in your Google Sheets document, the good news is that you can. And the even better news is that you can go about it in different ways.

Turn the numbers to text when it suits you. Or apply custom formatting when you don’t want the numbers converted to text.

Do you often enter numbers into Google Sheets? Let us know in the comments section below.