In this tutorial, I’m going to reveal a little-known trick that will allow you to easily extract the decimal part of a number in Google Sheets. Whether you’re dealing with positive or negative numbers, I’ve got your back!
The INT Based Formula
Let’s start with the most common formula, known as the INT formula. This formula allows you to extract the integer part of a number.
In the example below, I’ll be using the value in cell B2, which is 11.256. If there are any changes, I’ll make sure to mention them.
=int(B2)The formula above will return the integer part of the number, which is 11. But what if you want to extract the decimal part? Don’t worry, I’ve got a solution for you!
You can use the following INT-based formula in Google Sheets:
=B2-int(B2)By using this formula, you will get the decimal part of the number, which in this case is 0.256. Amazing, right?
But what if the number is negative? Let’s say we change the number in B2 to -11.256. The result will be 0.744!
This happens because the INT function rounds up the integer part of negative numbers. To solve this, we can make the number an absolute value and then use the INT function.
Here’s the recommended INT formula to extract the decimal part of a number in Google Sheets:
=abs(B2)-int(abs(B2))Result: 0.256
The MOD Based Formula
Another formula you can use to extract the decimal part of a number in Google Sheets is the MOD based formula.
=mod(B2,1)The formula above will correctly extract the decimal part, which is 0.256, if the number in cell B2 is positive. If it’s a negative number, the result will be 0.744, just like the INT-based formula.
You can also use the ABS function to ensure that the extracted decimal part is always positive:
=mod(abs(B2),1)The TRUNC to Extract Negative Decimal Part of a Number in Google Sheets
In both the INT based formula and the MOD based formula, the extracted decimal part is always positive, regardless of the sign of the number in cell B2. But what if you want to get the decimal part of a negative number, but with a negative sign? Fear not, the TRUNC based formula is here to help!
=B2-trunc(B2)Using the TRUNC based formula, you can get the decimal part of a negative number with a negative sign.
To get the positive integer similar to the INT and MOD solutions, you can wrap the above formula with ABS:
=abs(B2-trunc(B2))And there you have it! Now you know how to easily extract the decimal part of a number in Google Sheets. If you’re hungry for more exciting tutorials like this, make sure to check out Crawlan.com!
Go ahead and impress your friends with your newfound knowledge. Happy spreadsheeting!
