How to Extract Decimal Part of a Number in Google Sheets

Published on

In this quick tutorial, you can read about formula options to extract the decimal part of a number in Google Sheets. The number can be positive or negative.

In one of my earlier posts, as part of the core formula (a combo formula), I have shown you how to extract the decimal part of a number in Google Sheets. But I haven’t left any details there.

For example, in the tutorial How to Filter Decimal Numbers in Google Sheets, you can see one such formula in use.

To get the decimal part in Google Sheets, we can use three formulas (or maybe more). I have used one of them in my above-linked tutorial.

In this Google Sheets tutorial, you will get the most commonly used formulas to get the decimal part of negative or positive numbers.

Formula to Extract Decimal Part in Google Sheets

Let me start with the most common INT formula.

INT Based Formula

In all the formulas below, you must read the value in cell B2 as 11.256. If there is any change in any of the formulas below, I’ll mention the same.

We can use the below INT formula to extract the integer part of the number from the number in cell B2.

=int(B2)

The formula will return the number 11. That means, to extract the decimal part, we can use the below INT-based formula in Google Sheets.

=B2-int(B2)

The above INT based formula will return the decimal part of the number, i.e., 0.256.

Change the number in B2 to a negative number such as -11.256. The result will be 0.744!

It’s because the INT function will return -12 as the integer part of the negative number -11.256 instead of returning -11. It ROUNDUP the integer.

So the solution is to make the number an absolute value and then use the INT.

Here is the recommended INT formula to extract the decimal part of a number in Google Sheets.

FORMULA 1

=abs(B2)-int(abs(B2))

Result: 0.256

MOD Based Formula

Another formula to extract the decimal part of a number in Google Sheets is the below MOD based one.

=mod(B2,1)

The above formula will correctly extract 0.256, which is the decimal part if the number in cell B2 is positive.

If it’s a negative number, the result will be 0.744, similar to the INT-based formula.

Here also we can use the ABS function as below.

FORMULA 2

=mod(abs(B2),1)

TRUNC to Extract Negative Decimal Part of a Number in Google Sheets

In both the above formulas (formula 1 and formula 2), irrespective of the sign of the number in cell B2, the extracted decimal part is positive.

Assume I want to get the decimal part from the number -11.256 as -0.256 instead of 0.256. Here we can use the TRUNC based formula as below.

FORMULA 3

=B2-trunc(B2)

That means, to extract the decimal part of a negative number with a negative sign, you can depend on the above TRUNC based formula in Google Sheets.

By wrapping the above formula with ABS, we can get the positive integer similar to INT and MOD solutions.

FORMULA 4

=abs(B2-trunc(B2))
Formulas to Extract Decimal Part in Google Sheets

That’s all. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.