Table of Contents
To remove trailing zeros in Excel, you can use the “Format Cells” feature. First, select the cells containing the numbers with trailing zeros. Then, go to the “Home” tab and click on the “Number Format” drop-down menu. Select “More Number Formats” and a new window will appear. Under the “Number” tab, choose “Custom” from the Category list. In the “Type” field, enter #,### (or any desired number format) and click “OK”. This will remove the trailing zeros from the selected cells. Alternatively, you can use the “Find and Replace” feature to replace all trailing zeros with a blank space. Simply select the cells, press “Ctrl + F” and enter “.0” in the “Find what” field and leave the “Replace with” field blank. Then, click “Replace All” to remove the trailing zeros.
Remove Trailing Zeros in Excel (With Example)
You can use the following formula in Excel to remove trailing zeros from numbers:
=LEFT(A2,MAX(IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0,ROW(INDIRECT("1:"&LEN(A2))))))
This particular formula removes all trailing zeros from the number in cell A2.
For example, if cell A2 contains 104000 then this formula will return 104.
Note that any zeros within the number (and not trailing at the end) will be left in the number.
The following example shows how to use this formula in practice.
Example: How to Remove Trailing Zeros in Excel
Suppose we have the following column of numbers in Excel:
Notice that all of the numbers have at least one trailing zero.
Suppose we would like to remove the trailing zeros from each number.
We can type the following formula into cell B2 to do so:
=LEFT(A2,MAX(IF(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0,ROW(INDIRECT("1:"&LEN(A2))))))
We can then click and drag this formula down to each remaining cell in column B:
Notice that column B displays each corresponding value in column B with the trailing zeros removed.
For example:
- 104000 becomes 104.
- 540000000 becomes 54.
- 30050 becomes 3005.
- 120000000 becomes 12.
And so on.
All trailing zeros from each number have been removed.
Note: If a number has no trailing zeros, then this formula will simply return the number itself.
Additional Resources
The following tutorials explain how to perform other common operations in Excel: