How to Check If a Number is Integer in Excel

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.

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:

=INT(B1)=B1

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.

check if number is integer1

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

You can also use the MOD function in combination with IF function to check if a cell value is integer in Microsoft Excel Spreadsheet. Just use the following MOD formula:

=IF(MOD(B1,1)=0, "TRUE", "FALSE ")

Select a cell where you want to display the result, then enter this formula, press Enter.

How to Check If a Number is Integer in Excel 10.png

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:

How to Check If a Number is Integer in Excel 11.png
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:

 =IsInteger_ExcelHow(B1)

 where B1 is the cell containing the number you want to check.

How to Check If a Number is Integer in Excel 12.png

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)…

Leave a Reply