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.