This post will guide you on how to check if a number is an integer in Excel using different methods. Excel provides built-in functions such as the INT and MOD functions that you can use to determine if a number is an integer.
Additionally, you can create a user-defined function using VBA code to check if a number is an integer. By using these methods, you can easily determine if a number is an integer and use it in your calculations or data analysis in Excel.
Table of Contents
1. Check If Value is Integer Using INT Function
Assuming that you have a list of data in range B1:B5, in which contain numeric values. And you want to test each cell value if it is an integer, if true, returns TRUE, otherwise, returns FALSE. How can I do it. You can use a formula based on the INT function to achieve the result. Like this:
Type this formula into a blank cell, such as: Cell C1, and press Enter key on your keyboard. Then copy this formula from cell C1 to range C2:C5 to apply this formula to check values.
Let’s see how this formula works:
The INT function try to extract the integer portion from a given cell value, if the returned value is equal to the default cell value, it indicated that numeric value is an integer.
2. Check If a Number is Integer Using MOD Function
=IF(MOD(B1,1)=0, "TRUE", "FALSE ")
Select a cell where you want to display the result, then enter this formula, press Enter.
The MOD function in Excel is used to return the remainder after dividing one number by another. If a number is divided by 1 and the remainder is 0, then the number is an integer.
3. Check If a Number is Integer with User Defined Function (VBA Code)
You can create a user-defined function in VBA (Visual Basic for Applications) to check if a number is an integer in Excel. Here’s how you can do it:
Step1: Open Excel and press Alt + F11 to open the Visual Basic Editor.
Step2: In the editor, click on Insert > Module to create a new module.
Step3: Type the following code into the module:
Function IsInteger_ExcelHow(ByVal num As Double) As Boolean If num = Int(num) Then IsInteger_ExcelHow = True Else IsInteger_ExcelHow = False End If End Function
Step4: Save the module and return to the Excel worksheet.
Step5: In the cell where you want to display the result, enter the formula:
where B1 is the cell containing the number you want to check.
If the number in cell B1 is an integer, the formula will return TRUE. If it is not an integer, the formula will return FALSE.
4. Video: Check If Cell Value is Integer
This video will guide you how to check if a number is integer in Cells in Excel with different methods.
5. Related Functions
- Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…