You can remove trailing zeros from numbers in Google Sheets in two ways, depending on whether the numbers have currency symbols or not.

Removing trailing zeros means removing zeros after the decimal point. For example, the original numbers 1500.00, 142.25, and 142.10 will become 1500, 142.25, and 142.1 after removing the trailing zeros.

The solution you use depends on whether you have formatted the number as currency or not.

We usually need to remove trailing zeros in Google Sheets in many different scenarios:

• When we have a range containing numbers whose decimal places are increased using the Increase decimal places button on the Quick Access Toolbar.
• In a range formatted using Format > Number > Currency.
• In a range formatted using Format > Number > Number.
• When a formula returns numbers with trailing zeros.
• When a Google Forms response contains numbers with trailing zeros.

If the number doesn’t contain a currency sign, you can easily remove the trailing zeros by formatting the number back to Automatic. However, if you do the same with a currency-formatted number, it will remove the currency symbol too.

How to Remove Trailing Zeros from Numbers in Google Sheets Without a Currency Sign

To remove all trailing zeros from numbers that don’t contain currency signs in the range E2:E5, follow these steps:

1. Select the cell range E2:E5.
2. Click Format > Number > Automatic.

Please see the GIF below for a clear picture.

How to Remove Trailing Zeros from Currency-Formatted Numbers in Google Sheets

My Sheets’ default currency format is Pound Sterling (GBP) as my locale in File > Settings is the United Kingdom. So, I can enter a valid number like £1514 in a cell. It doesn’t have any decimal places, so Sheets won’t add trailing zeros.

But if I type £1514.1, Sheets will format this to £1514.10.

In a single cell number like this, I can click on the Decrease decimal places button on the Quick Access Toolbar. When I apply it in a range of cells, it will decrease the decimal places irrespective of trailing zeros or not.

I can’t apply Format > Number > Automatic as per our previous example as it will remove the currency symbols.

What is the solution then?

We can use a REGEXREPLACE formula in a helper cell to remove trailing zeros from currency-formatted numbers in Google Sheets:

If the range is B2:B, insert the following formula in C2:

=ARRAYFORMULA(IF(LEN(B2:B),REGEXREPLACE("£"&B2:B&"","(\.[0-9]*?[1-9])0+$",""),))

Replace £ in the formula with the currency symbol in B2:B.

Note: If your keyboard doesn’t have your currency symbol, use the following formula in any blank cell, copy that result, and paste it into the formula:

=LEFT(B2)

The result in C2:C will be text. Select it and right-align it using Format > Alignment > Right or by clicking on the Horizontal align button on the Quick Access Toolbar.

When using the converted numbers for calculation purposes wrap them with the VALUE function.

Example:

=ARRAYFORMULA(SUM(VALUE(C2:C6)))

Anatomy of the Formula

We can break the formula =ARRAYFORMULA(IF(LEN(B2:B),REGEXREPLACE("£"&B2:B&"","(\.[0-9]*?[1-9])0+$",""),)) as follows:

ARRAYFORMULA: The ARRAYFORMULA function applies the formula to all of the cells in the range B2:B and returns an array of results.

IF(LEN(B2:B),…): Checks if each cell in the range B2:B contains a value. If it does, the formula returns the result of the REGEXREPLACE function. Otherwise, it returns an empty string.

=REGEXREPLACE("£"&B2:B&"","(\.[0-9]*?[1-9])0+$",""): Removes all trailing zeros from the number in the cell, after the decimal point.

Related: How to Add Leading Zeros in Google Sheets.