How to remove decimals in Excel without rounding

TRUNC: cut the number, don't round it

The sheet holds amounts with too many decimals: a converted price of 18.4967, 42.879 litres of fuel, a refund of -3.79, an exchange fee of 0.0659. =TRUNC(B2) returns 18. TRUNC simply chops off everything after the decimal point, whatever the digits are.

The second argument says how many decimals to keep. =TRUNC(B2,2) keeps cents and returns 18.49, while =ROUND(B2,2) returns 18.5 because the next digit is 6. =TRUNC(B3,1) gives 42.8 litres. A small value like the 0.0659 fee becomes 0.06 with =TRUNC(B5,2).

A negative second argument cuts to the left of the decimal point: =TRUNC(B3,-1) returns 40. ROUNDDOWN does the same job with the same arguments (=ROUNDDOWN(B2,2) is also 18.49); the only difference is that TRUNC lets you leave out the second argument.

INT is not the same on negative numbers

INT is often suggested for "remove decimals", and for positive numbers it gives the same answer as TRUNC: =INT(B2) is 18. On negatives it doesn't. INT always goes down the number line, so =INT(B4) turns the -3.79 refund into -4, while =TRUNC(B4) gives -3.

The same trap catches the "multiply, INT, divide" trick for cents. =INT(B7*100)/100 turns the -12.555 credit note into -12.56, a cent more than the original. =TRUNC(B7,2) gives -12.55. If your column can ever contain refunds, credits or losses, use TRUNC.

Get only the decimal part

Sometimes you want the opposite: the part after the decimal point, such as the 0.75 of 7.75 hours. =MOD(B2,1) returns 0.4967, the remainder after dividing by 1. Use it on positive values only.

On negatives MOD gives a surprise: =MOD(B4,1) returns 0.21, not 0.79, because MOD's result always takes the sign of the divisor. Subtract the whole part instead. =B4-TRUNC(B4) returns -0.79, and wrapping it in ABS gives 0.79 if you only need the digits.

Decimal formats hide digits and round what you see

Home > Decrease Decimal, or a number format like 0 or 0.00, changes only the display. The full value stays in the cell and is used in every sum. Worse, the display is rounded, not cut: 7.75 hours shown with no decimals reads 8, and the 2.999 unit cost shown with two decimals reads 3.00. TRUNC gives 7 and 2.99.

That is why totals look off. =SUM(B2:B8) uses the hidden digits and returns 55.8456, while =SUM(TRUNC(B2:B8,2)) adds the cut values and returns 55.82. Decide which one you need and use a formula when the stored value matters.

If you need the cut value as text, for a label or an export, combine both: =TEXT(TRUNC(B2,2),"0.00") returns 18.49 with exactly two digits.

Many cells, text numbers, and no formula at all

In Excel 365, one formula cuts a whole column: =TRUNC(B2:B8,2) spills the results down. In older versions, put =TRUNC(B2,2) in a helper column and fill it down. To replace the originals, copy the helper column, then Home > Paste > Paste Values over column B, and delete the helper.

TRUNC also converts numbers stored as text. The weight in B9 came in as the text 12.865 (ISTEXT(B9) is TRUE), and =TRUNC(B9,2) still returns the number 12.86.

For typed values with no formulas, Find and Replace can remove decimals without a helper column, but it is permanent, so try it on a copy.

1. Select the cells with the numbers (not formulas).
2. Press Ctrl+H to open Find and Replace.
3. In Find what, type .* (a dot and an asterisk). Leave Replace with empty.
4. Click Replace All. 42.879 becomes 42 and -3.79 becomes -3, because everything from the dot onward is deleted.