How to Remove/Keep Leading Zeros in Google Sheets

Leading zeros in Google Sheets refer to the zeros that appear at the beginning of a number.

These zeros don’t affect the value of a number itself but can impact how the number is displayed and interpreted.

Leading zeros are particularly relevant when working with numbers that have a specific format, like phone numbers, ZIP codes, or product codes, where maintaining the leading zeros is important for the accuracy of the information. For example, in many cases, you might want to display a phone number like “012-345-6789” with the leading zero intact rather than “12-345-6789” without the leading zero.

However, by default, Google Sheets may remove leading zeros from numbers when they are entered in cells. This behavior can sometimes cause issues when you need to preserve the leading zeros.

This post will look at how to remove or keep leading zeros in Google Sheets and some practical applications.

Keep reading to learn more.

How to Add/Keep Leading Zeros in Google Sheets

Keeping leading zeros in Google Sheets is relatively easy, and there are several ways you can go about it.

Below, we look at the three most notable methods.

Method 1: Adding an Apostrophe to Remove Leading Zeros

If you type numbers in a Google Sheets cell, add leading zeros at the beginning, and then hit enter, you will notice that the zeros disappear and only the numbers remain. However, adding an apostrophe before typing the leading zeros allows you to keep them.

Below are the steps to keeping leading zeros.

Step 1: Pick a Cell

Click on whatever cell you want to add a number with a leading zero. In our example, this will be cell A1.

Step 2: Add an Apostrophe before leading zeros

Type an apostrophe first before typing in the numbers with the leading zeros you want to add for your figures. For our example, we will have three leading zeros for each figure.

Step 3- Press Enter

Once you press enter, the Google Sheets document should retain the leading zeros. 

Method 2: Using Text Format to Keep Leading Zeros in Google sheets

This next method involves formatting the entire column you want to keep leading zeros using text format.

Below are the steps to how this works. 

Step 1: Pick a Column

Select the column you want to input your numbers and highlight it, as seen below. 

Step 2: Go to “Format > Number > Plain text” to change the Text Format.

After highlighting the column, go to the menu section, click Format, hover the cursor on Number, and select Plain Text. 

Step 3: Input Your Leading Zeros and Numbers

Now you should be able to type in the number of leading zeros you want per figure without them disappearing after you press enter. 

You should also note leading zeros will only work in this column. If you want to keep the leading zeros in another, you will have to repeat the process and change the Text Format in that new column. 

Method 3: Using Custom Number Format to Automatically Add and Keep Leading Zeros in Google Sheets

A custom number format allows you to enter a certain number of digits as a custom number format that any number you enter will automatically add leading zeros to your number until it is the number of digits your format specifies. 

For instance, if your custom number format specifies an output of 5 numbers, if you type in 32, the formatting will include 3 leading zeros to make a 5-digit number. 

Here are the steps to creating a custom number format.

Step 1: Pick a Column

We will pick column A for our example, but this could be any column, depending on your application. 

Step 2: Highlight the Column

Place your cursor at the top of the column’s heading section and click on it to highlight the entire column. The cursor should turn into a grip arm. 

Step 3: Go to “Format > Number > Custom number format” to customize the number formatting

After highlighting the column, go to the top bar menu section and click on Format. Then hover your cursor over the Number option for a secondary menu option to open and scroll to the bottom. 

The last option should be Custom Number Format, and click on it.

Step 4: Set Your Custom Number Format by typing 5 zeros

A new menu dialog box will pop up after clicking the Custom Number format option. Click on the text field at the top of the new menu option and type in 5 zeros. 

You should also note that the 5 zeros are just for demonstration purposes for this example, and they can be any custom number you’re trying to create. 

Step 5- Click Apply

Click apply to apply the custom number format to your column. Now if you type in a number with less than 5 digits, the equivalent number of leading zeros will automatically be added to bring the tally of the digits to 5, as seen below. 

As you can see, keeping leading zeros in Google Sheets is not that complicated, and there are several ways you can implement this, depending on your application.

How to Remove Leading Zeros in Google Sheets

Conversely, you can also remove leading zeros from numbers in Google Sheets using these simple methods. 

Method 1: Reversing Leading Zero Insertion

Interestingly, the same methods that help to insert and keep leading zeros in Google Sheets can also be used to remove them if they were inserted using those techniques initially.

If you find yourself needing to remove leading zeros while ensuring data consistency, these methods come in handy.

1. Reverting Formatting: If you’ve used the “Formatting Cells as Text” method to keep leading zeros and need to revert, it’s as simple as changing the formatting back to “Automatic” or “Number” formatting. Select the range, go to Format > Number, and this time, opt for the Number format or the default “Automatic,” and the leading zeros will vanish.
2. Ditching the Apostrophe: Remember that clever apostrophe trick? If you’ve used it and want to remove the leading zeros, just edit the cell. Delete the apostrophe, and the number will adjust accordingly.
3. Tinkering with Custom Number Formatting: If you’ve applied custom number formatting for leading zeros, you can easily remove them without sacrificing the numerical value. Simply go to “Format > Number > Automatic to reset the number formatting.

Knowing how to remove leading zeros added through these methods is as important as preserving them. With a few simple steps, you can modify your data without compromising its accuracy. The key lies in understanding which method you’ve used and employing the right approach for removal.

So, whether you’re correcting errors, standardizing data, or enhancing readability, the techniques that once guarded your data’s accuracy can now help you fine-tune it.

Method 2: Using Value Trim Formula to Remove Leading Zeros in Google sheets

When you have an existing Google Sheets with a section containing leading zeros, using the value trim formula is the most convenient and effective way to remove the leading zeros. 

In our example below, a worker tries to get the employee Id numbers without the leading zeros. Here is a closer look at the steps.

Step 1: Pick a Cell

First, pick the cell you want the formula to return the zeroless values. In our example, we’ve selected cell D2. 

Step 2: Input the Formula

This is the formula you will need to remove the leading zeros

=VALUE(TRIM(CLEAN(A2)))

Step 3: Press Enter

Once you hit enter, the first employee ID number without the leading zeros should autofill the cell. A pop-up should also appear asking you to confirm if you want to fill the rest of the cell, which you can click, yes. 

If not, the first cell with the zeroless employee ID number should have a small nob you can drag down to autofill the rest of the cells, as seen below. 

Method 3: Using the Value Text Formula to Get rid of Leading Zeros from Google Sheets

Unlike our previous example, the value text formula takes a number in Google Sheets with leading zeros, turns the values into text, and then back to numeric numbers while ignoring the zeros. 

We will use the same Google Sheets document as the previous example to show how the value text formula works. Here are the steps you should follow.

Step 1: Pick a Cell

As with the previous example, you will start by identifying the cell you want to input your formula and the results to be returned. This will be cell D2. 

Step 2: Input Formula

Here is the formula to remove the zeros from the employee ID section

=VALUE(TEXT(A2,”0″))

Step 3: Press Enter

Once you press enter, the zeroless employee ID number should appear in the cell. To auto-fill the rest of the cells, click on the small nob on the active cell with the first result and drag it down. 

This should reveal the remaining zeroless employee ID numbers, as seen below. 

Conclusion

Leading zeros allow you to keep a consistent format while working in Google Sheets for a more professional look and appearance. However, Google Sheets does not apply leading zeros by default, and you have to know how to apply them in the most efficient way for what you’re doing. 

You can also get an existing Google Sheets document with leading zeros that might be inconvenient to work with and want to remove them. Therefore, knowing how to remove or keep leading zeros in Google Sheets is an essential skill you will need to optimize workflow when working with such data management programs. 

We hope this post has helped you understand how to remove and keep leading zeros using apostrophes, formulas, and the custom number format. Now go out there and use Google Sheets for your data management and analysis, knowing you can also remove or keep leading zeros. 

FAQs

What are leading zeros in Google Sheets? 

Leading zeros are zeros that appear at the beginning of a number before any other significant digits. They are often used to maintain formatting or convey specific meaning, such as postal codes or identification numbers.

Can I remove leading zeros from a range of cells at once? 

You can remove leading zeros from a range of cells by applying the VALUE function to the entire range. For instance, if your range is A1:A10, you can use the formula: =ARRAYFORMULA(VALUE(A1:A10)).

When should I remove leading zeros? 

Consider removing leading zeros when they are irrelevant to the data’s meaning or when the data is used in calculations where leading zeros might affect results.

When should I keep leading zeros? 

It’s advisable to keep leading zeros when they hold significance, such as in codes, identifiers, or any data where maintaining a specific format is crucial.

My formulas for removing/keeping leading zeros aren’t working. What could be wrong?

Double-check your formulas for typos and correct cell references. Ensure your data is in the expected format and using the appropriate functions.

Can removing or keeping leading zeros affect my calculations? 

Altering leading zeros can impact calculations, especially if they hold a specific meaning. Always review your data and the impact of formatting changes on your calculations.