Excel: Separate the Integer From the Decimals

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I have a column of values that include digits before and after the decimal point. I don't want to round anything, I just want the whole number. Or, I just want the decimal. How can I easily break those apart?

Strategy: Use the INT function to return the integer portion of the number.

  1. Use INT to chop the decimals off your numbers.

    To lose the integer and keep only the decimals, I use =A2-INT(A2). Another solution is to use MOD(A2,1). The MOD function is equivalent to the math concept of modulo. Divide A2 by 1 and return the remainder.

    Gotcha: the INT function for negative numbers may not act like you expect. The INT of -9.1 is -10, since this is the integer just less than -9.1. If your values might contain negative numbers, then consider using the TRUNC function to truncate the decimals. For positive numbers, INT and TRUNC are identical. The only difference is for negative numbers. TRUNC(-9.1) will be -9.