This blog post is about 3 different methods to extract the decimal part of a Number.
Table of Contents
TRUNC function to extract the Decimal part of a Number
The TRUNC function in Excel truncates a number to the given precision. The formula =TRUNC(4.159) will return 4.
The following formula will extract the decimal part of the number in the cell B3.
=B3-TRUNC(B3)
If you are dealing with negative numbers, wrap the above formula with the ABS function.
=ABS(B3-TRUNC(B3))
MOD function to get the Decimal part of a Number
The MOD function in Excel returns the ‘Remainder’ of Division between two numbers.
When a number is divided by 1, the remainder is the decimal part of the number. So, the formula =MOD(4.159) will return 4.
The formula given below will extract the decimal part of the number in the cell B3.
=MOD(B3,1)
Note that this formula won’t return the desired result with negative numbers.
But of course, we can make use of the ABS function to overcome this problem.
=MOD(ABS(B3),1)
=ABS(B3-TRUNC(B3))
Extracting the Decimal part of a Number using Power Query
Power Query in Excel also can be used to get the decimal part of a Number.
Select the cells containing data > go to the Data tab of the Excel ribbon > click on From Table/Range
If the selected data range is not an official Excel Table, we will be prompted to convert the same into an Excel Table.
Click OK and the selected data will be loaded into the Power Query Editor of Excel.
Here, we will be using the ‘Modulo’ technique to extract the decimal part. But this technique won’t give the desired results with negative numbers.
So, to convert every number into a positive number,
right-click on the column header of the column containing values > Transform > Absolute Value
All negative numbers are converted into Positive numbers.
Go to Transform tab of the Power Query Editor > Standard > Modulo
In the Modulo dialog, type in 1 and Click OK
The integer part of the numbers are gone and the decimal part remain.
If you want to rename the column, double-click on the column header and type in the new name. Here, I have renamed the column as ‘Decimal Part’.
Now, to load this data into the Excel worksheet,
In the Home tab of the Power Query Editor > click on the split button for Close & Load > Close & Load To…
Import Data dialog is activated. Use this dialog to specify the location where you want to insert the output from the Power Query Editor. Here, I have selected the cell E2.
Click on OK and we have the decimal part of the numbers in a separate table.
