- Excel Book Excerpt
Excel Separate the Integer From the Decimals
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Separate the Integer From the Decimals
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.
Figure 355 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.
For more resources for Microsoft Excel: