# How to separate number by its decimal point in Excel/Calc?

I've got a number in a spreadsheet like so: `28.686279`

When I open this sheet in either LibreOffice Calc or Microsoft Excel, is there a formula function that returns the "whole number", I.e. 28. Also, is there a formula that returns the "decimal part", I.e. `0.686279`?

-

`TRUNC()` is designed to remove the decimal part of any number immediately and without any modification to the non-decimal part.

So, as per LinYan's answer, you just need to use:

• `TRUNC(A1)` to obtain the integer part of the value in `A1`
• `A1-TRUNC(A1)` to obtain the fractional part of the value in `A1`

Unlike `FLOOR()`, `TRUNC()` works on both positive and negative numbers without requiring adjustment, and works in the same way in both Microsoft Excel and LibreOffice.

`FLOOR()` requires the significance parameter to have the same sign as the number being processed (or else will throw an error), so the `1` at the end would have to be changed to `-1` to process negative numbers, or you could insert `SIGN()` and unnecessarily complicate the formula further.

And, in OpenOffice and LibreOffice, `FLOOR()` also has an additional (compared to Excel) third "mode" parameter that changes the results that the function returns for negative numbers.

-

you can try `FLOOR` function, `floor(A1,1)` for integer part of A1, `A1-floor(A1,1)` for decimal part of A1.

-
That works, thanks! –  hpy May 9 '11 at 3:59
Just wondering, why isn't there some form of FRAC(A1) function to retrieve the decimal part? It seems that's something people do quite often, right? –  Alexis Wilke Aug 29 '13 at 22:50

Don't think there's a specific function to do this, however by nesting a couple you can.

Assuming you're trying to return the right-of-decimal value for cell A1, the formula would be:

``````=MID(A1,SEARCH(".",A1,1)+1,LEN(A1))
``````

Effectively what you're doing here is using the MID function to return some number of characters starting at the decimal point. All 3 references to A1 need to be updated for each cell you're targeting for it to work correctly.

-
This returns text rather than a number. –  fixer1234 Dec 3 '14 at 9:09

For example, imagine A1 is 167.583 :
`int(A1)` would give 167 and
`mod(A1,1)` would give 0.583 .

-
``````=RIGHT(TEXT(ABS(A1)-INT(ABS(A1));",00");2)
``````

would'nt this be just perfect, and aesthetic, too?

-
This returns text rather than a number. –  fixer1234 Dec 3 '14 at 9:08