The ISNUMBER function returns TRUE if a value is a number, and FALSE otherwise. It correctly identifies numeric values including integers, decimals, dates, times, and Boolean values (TRUE/FALSE), but returns FALSE for text representations of numbers, errors, blank cells, or other data types.
Master the fundamentals of Excel ISNUMBER function
The ISNUMBER function is essential for type checking and data validation in Excel. Understanding what ISNUMBER considers a "number" is crucial, as Excel's type system treats dates, times, and Booleans as numeric types. This function is fundamental for building robust formulas that handle mixed data types safely.
ISNUMBER returns TRUE for: integers (1, 2, 3), decimals (1.5, 3.14), negative numbers (-10), zero (0), dates (Excel stores as serial numbers), times (Excel stores as decimal fractions), Boolean values (TRUE=1, FALSE=0), and formulas that evaluate to numbers. It returns FALSE for: text (including "123"), text that looks like numbers, errors (#N/A, #VALUE!, etc.), blank cells, and text formulas.
A critical aspect of ISNUMBER is that Excel internally stores dates as serial numbers (e.g., January 1, 1900 = 1) and times as decimal fractions (0.5 = noon). ISNUMBER returns TRUE for both, which is useful for validating date/time inputs but can be surprising if you expect FALSE. Use ISNUMBER with date-specific functions for comprehensive date validation.
The distinction between text numbers (like "123") and actual numbers (like 123) is important. ISNUMBER returns FALSE for text numbers. This is common when importing data, using TEXT functions, or receiving data from external sources. Use VALUE to convert text numbers before checking with ISNUMBER, or combine: =IFERROR(ISNUMBER(VALUE(A1)), FALSE).
ISNUMBER is extensively used in data validation rules to ensure cells contain numeric data before calculations. It's essential in forms where numeric input is required, financial models where calculations depend on numeric data, and data cleaning operations to identify non-numeric values that need conversion.
ISNUMBER is very efficient and works seamlessly with array formulas and dynamic arrays. When validating user input, combine ISNUMBER with IF to provide clear feedback. For complex validation, combine with AND/OR functions and other IS functions (ISBLANK, ISERROR) to create comprehensive data validation rules.
Function-specific parameters
Function-specific return type
ISNUMBER returns TRUE for: integers (1, 2, 3), decimals (1.5, 3.14), negative numbers (-10), zero (0), dates (Excel stores as serial numbers), times (Excel stores as decimal fractions), Boolean values (TRUE=1, FALSE=0), and formulas that evaluate to numbers. It returns FALSE for: text (including "123"), text that looks like numbers, errors (#N/A, #VALUE!, etc.), blank cells, and text formulas.
A critical aspect of ISNUMBER is that Excel internally stores dates as serial numbers (e.g., January 1, 1900 = 1) and times as decimal fractions (0.5 = noon). ISNUMBER returns TRUE for both, which is useful for validating date/time inputs but can be surprising if you expect FALSE. Use ISNUMBER with date-specific functions for comprehensive date validation.
The distinction between text numbers (like "123") and actual numbers (like 123) is important. ISNUMBER returns FALSE for text numbers. This is common when importing data, using TEXT functions, or receiving data from external sources. Use VALUE to convert text numbers before checking with ISNUMBER, or combine: =IFERROR(ISNUMBER(VALUE(A1)), FALSE).
ISNUMBER is extensively used in data validation rules to ensure cells contain numeric data before calculations. It's essential in forms where numeric input is required, financial models where calculations depend on numeric data, and data cleaning operations to identify non-numeric values that need conversion.
Exact matching required
Returns numeric position
Handles missing text gracefully
=ISNUMBER(value)The value, cell reference, or expression to check. This can be any value type that Excel might contain.
TRUE if the value is a number (including dates, times, and Booleans), FALSE if the value is text, an error, blank, or any non-numeric type
Description: ISNUMBER(value)
Basic ISNUMBER check
The most straightforward use. Checks if cell A1 contains a numeric value. Returns TRUE for actual numbers, dates, times, and Boolean values (TRUE=1, FALSE=0).
VBA implementation for ISNUMBER.
Sub ISNUMBERExample()
' Method 1: Using IsNumeric (most common)
Dim result As Boolean
result = IsNumeric(Range("A1").Value)
MsgBox "Is A1 a number (IsNumeric): " & result
' Method 2: Using TypeName
Dim cellValue As Variant
cellValue = Range("A1").Value
Dim valueType As String
valueType = TypeName(cellValue)
If valueType = "Double" Or valueType = "Integer" Or valueType = "Long" Or _
valueType = "Single" Or valueType = "Currency" Or valueType = "Date" Then
MsgBox "Cell contains a numeric type: " & valueType
Else
MsgBox "Cell contains non-numeric type: " & valueType
End If
' Method 3: Using WorksheetFunction.IsNumber
Dim wsResult As Boolean
wsResult = Application.WorksheetFunction.IsNumber(Range("A1").Value)
MsgBox "Is A1 a number (WorksheetFunction): " & wsResult
' Method 4: Comprehensive validation with conversion
Dim inputValue As Variant
inputValue = Range("A1").Value
If IsNumeric(inputValue) Then
' Safe to perform calculations
Range("B1").Value = inputValue * 2
MsgBox "Calculation successful: " & Range("B1").Value
Else
' Try to convert text to number
If IsNumeric(Trim(inputValue)) Then
Range("B1").Value = CDbl(Trim(inputValue)) * 2
MsgBox "Converted and calculated: " & Range("B1").Value
Else
MsgBox "Cannot convert to number: " & inputValue
Range("B1").Value = "Invalid"
End If
End If
' Method 5: Check multiple cells
Dim cell As Range
For Each cell In Range("A1:A10")
If Not IsNumeric(cell.Value) Then
MsgBox "Non-numeric value found at " & cell.Address & ": " & cell.Value
End If
Next cell
End SubData validation to ensure cells contain numeric values before calculations
Type checking before performing mathematical operations
Conditional calculations that depend on numeric input
Data cleaning to identify and convert text numbers
Form validation requiring numeric input fields
Financial modeling to validate numeric data integrity
Data import validation to check numeric columns
Building robust formulas that handle mixed data types safely
ISNUMBER returns FALSE for values that look like numbers
Solution: The cell likely contains text formatted as a number. Check the formula bar - if it shows "123" with quotes or a leading apostrophe, it's text. Use VALUE to convert: =ISNUMBER(VALUE(A1)), or clean the data source.
ISNUMBER returns TRUE for dates when expecting FALSE
Solution: Excel stores dates as numbers internally. ISNUMBER will return TRUE for dates. If you need to distinguish dates from regular numbers, use ISNUMBER combined with ISDATE or check the cell format, or use date-specific validation functions.
ISNUMBER not working with imported data
Solution: Imported data often contains text numbers with hidden characters or formatting. Use VALUE with TRIM and CLEAN: =ISNUMBER(VALUE(TRIM(CLEAN(A1)))). Also check for non-breaking spaces or special characters.
ISNUMBER returns different results for similar values
Solution: Cells may contain invisible formatting, leading/trailing spaces, or special characters. Use TRIM, CLEAN, and VALUE functions to normalize values before checking with ISNUMBER. Inspect cells individually using LEN to detect hidden characters.
ISNUMBER with formulas showing unexpected results
Solution: ISNUMBER checks the formula's result, not the formula itself. If a formula like =TEXT(123,"0") returns text "123", ISNUMBER will return FALSE. Verify what the formula actually returns, not what it looks like.