Excel formula: Get decimal part of a number

Generic formula

=number-TRUNC(number)

Summary

To get just the decimal part of a number, you can use a formula based on the TRUNC function. In the example shown, the formula in cell C6 is:

=B6-TRUNC(B6)

Explanation

Excel contains a number of rounding functions. Two of these functions, the INT function and the TRUNC function will return the integer portion of a number that contains a decimal value.

The INT function behaves a bit differently with negative values, so in this example we are using the TRUNC function. The TRUNC function simply truncates (i.e. removes) decimal values if they exist – it doesn't do any rounding.

In the example shown, cell C6 contains this formula:

=B6-TRUNC(B6)

The TRUNC function returns the integer portion of the number which is then subtracted from the original value. The result is the decimal portion of the number.

If the original number is an integer to begin with, the result of this formula is zero.

Author

Dave Bruns

Related formulas

Get integer part of a number

With TRUNC, no rounding takes place. The TRUNC function simply slices off the decimal part of the number with default settings. TRUNC actually takes an optional second argument to specify the precision of truncation, but when you don't supply this...

Related functions

Excel TRUNC Function

The Excel TRUNC function returns a truncated number based on an (optional) number of digits. For example, TRUNC(4.9) will return 4, and TRUNC(-3.5) will return -3. The TRUNC function does no rounding, it simply truncates as specified.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.