Split numbers into integer and decimal parts in Excel – Quick Tip

 Excel is all about numbers and we often end up with decimal numbers. Situations can arise when we want to split or separate the integer part from fractional (decimal) part.

This can be achieved in number of ways. So lets discuss few options.

1 Text-to-column

Though it does the job but its probably the dirty way of doing it. The only thing it achieves is splitting the numbers but data loses its integrity especially the decimal portions and one might has put additional effort in correcting the data

2 Flash Fill

In recent versions of Excel Flash Fill has become quite able and can automate many things for its users. However, the gripe is that data has to structured in a well defined pattern and user is required to give initial input so that Excel can recognize the pattern required for processing.

3 TRUNC function

From the solutions above, Flash Fill version is a little better but still not quite right as decimal portion loses its sign which might be necessary for the user. To get around this problem, TRUNC function gives the best results.

Having the number in cell A2:

For integer part use: =TRUNC(A2) or =TRUNC(A2,0)

For decimal part use: =A1-TRUNC(A2)

The cool part is that solution is dynamic and if underlying values change, the resultant will change as well and also the decimal parts are with respective negative sign.

4 INT and MOD functions

Yes we do have dedicated functions to get the integers and decimal parts of numbers in Excel.

To get integers or whole part of a number use: =INT(A2)

And to get the fractional or decimal part use: =MOD(A2,1)

But again we sucked in the same problem – decimal parts deprived of respective negative sign. To get around it we need to modify the formula for decimal portion a little as following:

=MOD(A2,1)*SIGN(A2)

or

=MOD(A2,SIGN(A2))