How To Stop Google Sheets From Deleting Zeros

by

Google Sheets, similar to other spreadsheet software, automatically removes leading zeros from numbers. While this is generally helpful and efficient, there are certain situations where you may want to retain those zeros. For instance, if you’re dealing with telephone numbers or zip codes, those zeros are essential.

So, how do you prevent Google Sheets from deleting zeros? Let’s jump right in.

Method 1: Using Single Quotes

The simplest way to keep leading zeros in Google Sheets is by inputting the number as text. This can be done by adding a single quotation mark (‘) before the number.

Simply type a single quote before the number. For instance, if you want to input the number 007, you would type ‘007. Google Sheets will now treat this as text, preserving the leading zeros.

Method 2: Using Format Cells

If you have a column of numbers and you wish to keep leading zeros for all of them, using Format Cells is a more efficient method.

Here’s how to do it:

  1. Select the cells you want to change
  2. Go to the Format menu
  3. Click on Number, then select Plain text.

This will change the format of the cells to plain text, preserving any leading zeros.

Method 3: Using Custom Number Formatting

If you know the exact number of digits and want the zeros to appear for smaller numbers, you can use custom number formatting.

  1. Select the cells you want to change
  2. Go to the Format menu
  3. Click on Number, then select More Formats followed by Custom number format.
  4. In the dialog box that opens, input the desired format. For instance, if you want a five-digit number, type ‘00000’.

This way, any number that you type in the selected cells will be automatically formatted to a five-digit number with leading zeros as necessary.

For example,

    7 becomes 00007
    83 becomes 00083
    992 becomes 00992

Wrap Up

The automatic deletion of leading zeros in Google Sheets can be a source of inconvenience for some. However, with these simple workarounds, you can have full control over how you want your numbers to appear in Google Sheets. Keep leading zeros and make your data more accurate and meaningful!