Table of Contents
In order to separate a whole number from a decimal in Excel, you can use the “INT” function to extract the integer part of a number and the “MOD” function to extract the decimal part. The “INT” function will return the largest integer less than or equal to the specified number, while the “MOD” function will return the remainder after dividing the number by 1. This allows you to separate the whole number and decimal portions of a number, allowing for more precise data analysis and calculations. Additionally, you can also use the “ROUND” function to round the decimal portion to a specific number of decimal places.
Excel: Separate Whole Number from Decimal
You can use the following formula to extract only the whole number from a value in Excel:
=INT(A2)
And you can use the following formula to extract only the decimal from a value in Excel:
=MOD(A2,1)
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:
=INT(A2)
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:
=MOD(A2, 1)
We can then drag and fill this formula down to each remaining cell in column C:
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 .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: