How to Prevent Google Sheets from Deleting Leading Zeros

Are numbers your thing?

If you spend your days working with figures, decimals, and math operations, tools like Google Sheets are probably a part of your essentials kit. Google Sheets is an excellent program to help keep you organized, and on top of your game.

Still, there may be a few settings that may cause you additional work or make you feel irritated. Have you ever seen Google Sheets deleting your leading zeros?

Here’s why it’s happening and how you can fix it.

What’s the Problem?

Suppose you want to enter a phone number that starts with a zero (or more than one). Unfortunately, Google Sheets will likely delete the leading zeros, even though they’re a legit part of the information you’re trying to type in.

This can happen regardless of the data: you may be entering social security numbers, IDs, zip codes, etc.

Thankfully, there are multiple ways to keep these zeros where they belong. Note that you can also use some of these if you run into the same issue in Microsoft Excel.

1. Create a Custom Number Format

The first method refers to creating a custom format so that it has to display everything you enter in a cell. You can adjust the settings in a way that the cells need to show, for example, nine digits if you’re entering social security numbers.

This way, if the number starts with one, two, or more zeros, they will all be displayed since the cell needs to contain nine digits in total.

When you use this approach, ensure that you always type in an adequate number of digits. If you accidentally enter more, Google Sheets will remove the digits from the beginning. If you enter fewer digits than you’re supposed to, Google Sheets adds leading zeros on its own.

Follow these steps to apply this method:

1. Click on the cells you’d like to format to select them.
   
2. Select the Format tab from the bar at the top.
   
3. Navigate to Number and find More Formats in the new menu.
   
4. From this drop-down menu, choose Custom Number Format.
   
5. When a new dialogue box opens, enter the number of digits you want to set for the selected cells. You will enter them in the form of zeros. For instance, if you want the cells to contain eleven digits, enter eleven zeros.
   
6. Click on Apply.
   

2. Use the Apostrophe

Using the apostrophe is quite a simple solution to this issue. Here’s what you need to do.

Before you start typing in the number that begins with a zero, enter an apostrophe symbol.

For example:

Instead of 005219, enter ‘005219.

After you press Enter, the apostrophe will disappear from the cell. You’ll see a plain number just like you’ve entered. It’s because the role of the apostrophe in Google Sheets is to disable the setting that removes leading zeros from the cells.

3. Plain Text Formatting

Switching the cell format from number to text will also help you solve this issue. If you change this setting, you will be able to keep all the leading zeros, regardless of their number. This method may work better for you if you don’t want to add apostrophes every time you enter a number.

To do this, follow these instructions:

1. Click on the row or column you want to format as plain text. It can be only a few cells, as well.
   
2. Choose Format from the top bar and navigate to Number.
   
3. You’ll see that Automatic is already checked, so choose Plain text instead.
   
4. Now you can keep entering numbers without leading zeros disappearing from the cells.
   

How to Remove Leading Zeros from Google Sheets

What happens if you want to remove the leading zeros from your worksheet?

You might find yourself in a situation where Google Sheets added leading zeros to your number sequence, and you want to remove them.

This may happen if you’ve previously used the custom number format to set a specific number of digits for a cell. If you’ve entered insufficient digits, Google Sheets will add leading zeros in front of the numbers.

You can remove these zeros by changing the cell format back to Number or General formats.

Pick Your Solution

As you see, there are three different ways to prevent Google Sheets from removing leading zeros.

This default setting can sometimes cause serious issues when using data where a single zero can make a huge difference. However, when you notice what’s happening on time, you can solve the problem in a minute. It’s up to you to find out which of these methods is suitable for you.

Have you already tried one of our fixes? Which one works best for you? Share your experience in the comments section below.