How to remove/preserve leading zeros in Google Sheets - Counter Software (2023)

Leading zeros in Google Sheets refer to the leading zeros in a number.

These zeros do not affect the value of a number itself, but they can affect how the number is displayed and interpreted.

Leading zeros are particularly relevant when working with numbers that have a specific format, such as phone numbers, postal codes, or product numbers, where keeping 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 instead of "12-345-6789" without the leading zero.

However, by default, Google Sheets can remove leading zeros from numbers when they're entered into cells. This behavior can sometimes cause problems when it is necessary to preserve leading zeros.

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

Continue reading to learn more.

How to add/retain leading zeros in Google Sheets

Keeping leading zeros in Google Sheets is relatively easy, and there are several ways to do it.

Below we review the three most prominent methods.

Method 1 – Add an apostrophe to remove leading zeros

If you enter numbers into a cell in Google Sheets, add leading zeros at the beginning, and press Enter, you'll notice that the zeros disappear and only the numbers remain. However, if you add an apostrophe before entering the leading zeroes, you can keep them.

Below are the steps to preserve leading zeros.

Step 1 - Select a cell

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

Step 2 - Add an Apostrophe Before the Leading Zeros

Enter an apostrophe before typing the numbers with leading zeros that you want to add to your digits. For our example, we will have three leading zeros for each number.

Step 3 – Press Enter

After pressing Enter, the Google Sheets document should have leading zeros.

Method 2 – Use Text Formatting to Keep Leading Zeros in Google Sheets

The following method involves formatting the entire column that you want to keep with leading zeros using the text format.

Below are the steps on how this works.

Step 1 - Select a column

Select the column where you want to enter your numbers and check it as shown below.

Step 2: Go to "Format > Number > Plain Text" to change the text format.

After highlighting the column, go to the menu section, click onFormat, hover overNumberand selectplain text.

Step 3: Enter leading zeros and numbers

You should now be able to enter the number of leading zeroes you want for numbers without them disappearing after pressing Enter.

Also note that leading zeros will only work in this column. If you want to keep the leading zeros in another column, repeat the process and change the format of the text in the new column.

Method 3: Use a custom number format to automatically add and preserve leading zeros in Google Sheets

and organizenumeric formatallows you to enter a specific number of digits as a custom number format, where any number you enter automatically adds leading zeros until it is the number of digits specified by your format.

For example, if your custom number format indicates a 5-number output, if you enter 32, the format includes 3 leading zeros to form a 5-digit number.

Here are the steps to create a custom number format.

Step 1 - Select a column

We'll choose column A for our example, but it could be any column, depending on your application.

Step 2 – Highlight the Column

Place your cursor at the top of the column header section and click it to highlight the entire column. The cursor should be changed to a support arm.

Step 3 Go to "Format > Numbers > Custom Number Format" to customize the number format

After highlighting the column, go to the menu section on the top bar and click onFormat. Then hover overNumberoption to open a secondary menu option and scroll to the bottom.

The last option should becustom number formatand click on it.

Step 4 - Define your custom number format by typing 5 zeros

A new menu dialog appears when you click the buttoncustom number formatpossibility. Click in the text field at the top of the new menu item and type 5 zeros.

Also note that the 5 zeroes are for demo purposes only in this example and could be any custom number you are trying to create.

Step 5 – Click Apply

Click Apply to apply the custom.numeric formatfor your column. Now, if you enter a number with less than 5 digits, the corresponding number of leading zeros will be added automatically to bring the number of digits to 5, as shown below.

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

How to remove leading zeros in Google Sheets

On the other hand, you can also remove leading zeros from numbers in Google Sheets using these simple methods.

Method 1: Invert the leading zero entry

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

If you need to remove leading zeros and ensure data consistency, these methods will help.

1. restore format: If you used the "Format cells as text" method to keep leading zeros and need to revert it, it's as simple as changing the format back to "Automatic"o"Number"format. Select range, go toFormat > From,and this time chooseNumberformat the pattern"Automatic”, and the leading zeros disappear.
2. Get rid of the apostrophe: Remember that clever apostrophe trick? If you've already used it and want to remove the leading zeros, just edit the cell. Remove the apostrophe and the number will adjust accordingly.
3. Adjust custom number format: If you have applied custom numeric formatting to leading zeros, you can easily remove them without sacrificing numeric value. just go to"Format > Number > Automaticto reset the number format.

Knowing how to remove leading zeros added by these methods is just as important as preserving them. With a few simple steps, you can change your data without compromising its accuracy. The key is to understand which method you used and use the correct removal approach.

So whether you're fixing errors, standardizing data, or improving readability, the techniques that once protected the accuracy of your data can now help you fine-tune it.

Method 2 – Using Cutoff Value Formula to Remove Leading Zeros in Google Sheets

When you already have a Google Sheet with a section that contains leading zeros, using the Cutoffs formula is the most convenient and efficient way to remove leading zeros.

In our example below, a worker tries to get employee ID numbers without the leading zeros. Here's a more detailed look at the steps.

Step 1 - Select a cell

First, select the cell where you want the formula to return non-zero values. In our example, we select cell D2.

Step 2: Enter the formula

This is the formula you need to remove leading zeros

=VALUE(TRIM(CLEAR(A2)))

Step 3 – Press Enter

After pressing Enter, the first employee ID number without the leading zeros should auto-populate the cell. A popup should also appear asking you to confirm that you want to fill in the rest of the cell, which you can click Yes.

Otherwise, the first cell with a non-zero employee ID number should have a little node that you can drag down to auto-populate the rest of the cells, as shown below.

Method 3 – Use Value Text Formula to Get Rid of Leading Zeros in Google Sheets

Unlike our previous example, the text-to-value formula takes a number in Google Sheets with leading zeros, converts the values ​​to text, and then returns them to numeric values, ignoring the zeros.

We'll use the same Google Sheets document from the previous example to demonstrate how the value text formula works. These are the steps you must follow.

Step 1 - Select a cell

As in the previous example, you start by identifying the cell where you want to enter your formula and the results that will be returned. This will be cell D2.

Step 2: Enter the formula

Here is the formula to remove null values ​​from the employee id section.

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

Step 3 – Press Enter

After pressing enter, the non-zero employee identification number should appear in the cell. To automatically complete the rest of the cells, click the small button in the active cell with the first result and drag it down.

This should reveal the remaining non-zero employee ID numbers, as shown below.

Conclusion

Leading zeros allow you to maintain consistent formatting when working in Google Sheets for a more professional appearance. However, Google Sheets doesn't use leading zeros by default, and you need to know how to use them most effectively for what you're doing.

You may also have an existing Google Sheets document that has leading zeros that can be awkward to work with and you want to remove them. So knowing how to remove or keep leading zeros in Google Sheets is an important skill you can use to streamline your workflow when working with these data management programs.

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

Common questions

What are leading zeros in Google Sheets?

Leading zeros are zeros that appear at the beginning of a number before other significant digits. They are often used to preserve format or convey specific meanings, 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 example, 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 meaning of the data or when the data is used in calculations where leading zeros might affect the results.

When should I continue with leading zeros?

It is advisable to retain leading zeros where they are significant, such as in codes, identifiers, or other data where it is essential to maintain a particular format.

My formulas to remove/retain leading zeros don't work. What could be wrong?

Check your formulas for typos and correct cell references. Make sure your data is in the expected format and uses the proper functions.

Will removing or keeping leading zeros affect my calculations?

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