How to Remove Trailing Zeros from Numbers in Google Sheets

This guide will explain how to remove trailing zeros from numbers in Google Sheets.

Trailing zeroes are a sequence of one or more zeros in a number after which no other digits follow.

Trailing zeroes that appear after a decimal point do not affect the value of the number but may indicate the level of accuracy for a measurement. We may want to remove these trailing zeroes to simplify our figures or to follow a certain standard (such as in accounting).

We can also remove trailing zeros from numbers without a decimal point. For instance, the number 1500 has two trailing zeroes. You may want to remove these trailing zeroes in situations where you need to output the significant figures of a measurement.

Google Sheets allows you to remove trailing zeros after the decimal point by using the number formatting options. For trailing zeros before the decimal point, we can use a custom regular expression and REGEXREPLACE to remove the trailing zeros from a numeric value.

In this guide, we will provide a step-by-step tutorial on how to remove trailing zeros from different numbers in Google Sheets.

A Real Example of Removing Trailing Zeros in Google Sheets

Let’s explore a few simple examples that illustrate how to remove trailing zeros from numbers in Google Sheets.

Removing Trailing Zeros After the Decimal Point

Removing trailing zeros after the decimal point does not affect the numerical value of a cell. For example, the numbers 25.00 and 25 are equal in value. However, there are several cases where you may want to remove trailing zeros after the decimal point.

For example, numbers with trailing zeros may lead people to believe that the measurement is more accurate than it actually is. Removing trailing zeros can also help with the readability of your data and can make your data look cleaner.

In the table above, some of our decimals contain trailing zeros that we want to drop. To remove these trailing zeros, we can set the number formatting to Automatic. This formatting option automatically applies formatting rules to numerical data to improve readability and consistency.

The Automatic formatting will remove trailing zeros to reflect the actual precision of a measurement or calculation.

Removing Trailing Zeros Before the Decimal Point

Removing trailing zeros before the decimal point is a bit trickier since there is no default formatting option for it. 

However, we can use regular expressions to find and remove trailing zeros from a number. Google Sheets includes a built-in REGEXREPLACE function that allows us to match all sets of zeros at the end of a number and replace it with an empty string.

In the example above, we have a range of numbers with trailing zeros. We can use the following formula to remove these trailing zeros:

=REGEXREPLACE(TO_TEXT(A2), "0+$", "")

Since REGEXREPLACE requires a text value to work with, we’ll need to use the TO_TEXT function to convert our numerical value into its equivalent text value.

The REGEXREPLACE function should replace all zeros found at the end of the number with an empty string.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to remove trailing zeros from numbers in Google Sheets.

How to Remove Trailing Zeros from Numbers in Google Sheets

1. Select the range of numbers that you want to remove trailing zeros from.
   
2. In the Quick Access toolbar, select the More Formats option (the button with the numbers 123). Select the Automatic formatting from the dropdown menu.
   
3. The selected values should now have all trailing zeros removed.
   
4. If you want to remove trailing zeros that appear before the decimal point, we will need to use a custom formula that uses the REGEXREPLACE function.
   Select the cell where you wish to return the output of REGEXREPLACE.
   
5. Next, type the REGEXREPLACE function. For the first argument, use the TO_TEXT function to convert the target number into a text value. Next, provide the regex pattern “0+$”. This pattern matches one or more zeros that appear at the end of the number. Set the third argument to an empty string (“”).
   In the example above, we used the formula =REGEXREPLACE(TO_TEXT(A2), “0+$”, “”) to remove the trailing zeros from the number 25000. 
   
6. We can use the AutoFill feature to apply the formula to the remaining numbers in our table.
   

These are all the steps you need to remove trailing zeros from numbers in Google Sheets.

FAQs

1. Will removing trailing zeros affect my calculations in Google Sheets?
   Calculation results will not be affected when formatting cells to drop trailing zeros. Formatting does not change the actual value of the cell, only the way it is displayed.

To learn more about formatting numbers in your spreadsheet, you can read our post on how to convert a YYYYMMDD string to a date value in Excel. You may also be interested in our guide on how to convert decimals to fractions in Google Sheets.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!