To get 0.159 from 4.159, subtract the whole-number part: =B3-TRUNC(B3). MOD and Power Query work too, but they handle negative numbers differently, so pick the method that gives the answer you need.
Formula=B3-TRUNC(B3)
Always positive=MOD(ABS(B3),1)
Whole part=TRUNC(B3)
Table of Contents
Formulas compared
| Formula | 4.159 | −4.159 |
|---|---|---|
=B3-TRUNC(B3) |
0.159 | −0.159 |
=ABS(B3-TRUNC(B3)) |
0.159 | 0.159 |
=MOD(B3,1) |
0.159 | 0.841 |
=MOD(ABS(B3),1) |
0.159 | 0.159 |
MOD returns 0.841 for −4.159 because it rounds towards minus infinity: −4.159 = −5 + 0.841. That’s right for clock times, but surprising for most other uses.
Avoid tiny rounding errors
Computers store decimals in binary, so =4.159-TRUNC(4.159) is really 0.15899999999999981. It displays as 0.159, but a comparison like =C3=0.159 returns FALSE. Round the result:
=ROUND(B3-TRUNC(B3),3)Decimal part as a whole number
For 159 instead of 0.159, as with paise from rupees: =ROUND(MOD(ABS(B3),1)*1000,0). For money, use *100 for two decimals.
Power Query
- Load the data: Data → From Table/Range.
- Right-click the column → Transform → Absolute Value (to handle negatives).
- Transform → Standard → Modulo → 1 → OK.
- Rename the column and Close & Load To.
