Separate the Integer From the Decimals
February 28, 2022 - by Bill Jelen
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.
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.
This article is an excerpt from Power Excel With MrExcel
