You can use the following formula to extract only the whole number from a value in Excel:
And you can use the following formula to extract only the decimal from a value in Excel:
The following example shows how to use these formulas in practice.
Example: How to Separate Whole Number from Decimal in Excel
Suppose we have the following list of numbers in Excel:
Suppose we would like to create a new column that contains the whole numbers of each value and another column that contains the decimal of each value.
To extract just the whole number, we can type the following formula into cell B2:
We can then drag and fill this formula down to each remaining cell in column B:
To extract just the decimal, we can type the following formula into cell C2:
We can then drag and fill this formula down to each remaining cell in column C:
Column B now displays the whole number of each value in column A and column C displays the decimal portion.
Note that when a number in column A is an integer, then the value in column C is simply zero.
Note that the MOD function in Excel is used to return the remainder after a number is divided by a divisor. By dividing by 1, the remainder will always be just the decimal portion of a number.
You can find the complete documentation for the Excel MOD function here.
The following tutorials explain how to perform other common operations in Excel: