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.