Skip to main content

Validating Data

Check data types and values with ISBLANK, ISNUMBER, ISTEXT, and other validation formulas.

Relevant Formulas​

ISBLANK​

Returns TRUE if the value is blank/NULL.

View ISBLANK Formula Reference →

ISNUMBER​

Returns TRUE if the value is a number.

View ISNUMBER Formula Reference →

ISTEXT​

Returns TRUE if the value is text.

View ISTEXT Formula Reference →

Additional Validation Formulas​

  • ISNA - Check for #N/A error
  • ISEVEN - Check if number is even
  • ISODD - Check if number is odd
  • TRUE - Logical TRUE
  • FALSE - Logical FALSE

Basic Usage​

ISBLANK​

=ISBLANK([field])
Result: TRUE if NULL, FALSE otherwise

=IF(ISBLANK([email]), "Missing", "Present")

ISNUMBER​

=ISNUMBER([value])
Result: TRUE if numeric

=IF(ISNUMBER([quantity]), "Valid", "Invalid")

ISTEXT​

=ISTEXT([field])
Result: TRUE if text

=IF(ISTEXT([code]), "Text", "Not Text")

Common Use Cases​

Required Field Validation​

// Check if all required fields present
=IF(OR(ISBLANK([name]), ISBLANK([email]), ISBLANK([phone])),
    "Incomplete",
    "Complete")

Data Type Validation​

// Validate numeric input
=IF(ISNUMBER([age]) AND [age] > 0, "Valid", "Invalid Age")

// Ensure text field
=IF(ISTEXT([name]), [name], "Invalid Name")

Data Quality Flags​

// Flag incomplete records
=IF(ISBLANK([required_field]), "MISSING", "OK")

// Validation status
=IF(AND(NOT(ISBLANK([email])), FIND("@", [email]) > 0),
    "Valid Email",
    "Invalid Email")

Conditional Processing​

// Process only if valid
=IF(AND(ISNUMBER([amount]), [amount] > 0),
    [amount] * [rate],
    0)

Advanced Techniques​

Multiple Field Validation​

// Check multiple required fields
=AND(
    NOT(ISBLANK([field1])),
    NOT(ISBLANK([field2])),
    NOT(ISBLANK([field3]))
)

Type-Specific Processing​

// Different handling by type
=IF(ISNUMBER([value]),
    [value] * 2,
    IF(ISTEXT([value]),
        UPPER([value]),
        "Invalid"))

Completeness Scoring​

// Calculate completeness percentage
=(
    (IF(NOT(ISBLANK([field1])), 1, 0) +
     IF(NOT(ISBLANK([field2])), 1, 0) +
     IF(NOT(ISBLANK([field3])), 1, 0)) / 3
) * 100

Common Patterns​

Email Validation (Basic)​

=IF(AND(
    NOT(ISBLANK([email])),
    FIND("@", [email]) > 0,
    FIND(".", [email]) > FIND("@", [email])
), "Valid", "Invalid")

Numeric Range Validation​

=IF(AND(
    ISNUMBER([value]),
    [value] >= [min],
    [value] <= [max]
), "Valid Range", "Out of Range")

Required vs. Optional​

// Mark fields
=IF(ISBLANK([optional_field]),
    "Not Provided",
    [optional_field])

Validation Checklist Pattern​

// Create validation checklist
=CONCAT(
    IF(ISBLANK([name]), "❌ Name missing\n", "✓ Name\n"),
    IF(ISBLANK([email]), "❌ Email missing\n", "✓ Email\n"),
    IF(ISBLANK([phone]), "❌ Phone missing\n", "✓ Phone\n")
)

Best Practices​

  1. Check Before Processing - Validate before calculations
  2. Provide Clear Messages - Descriptive validation messages
  3. Handle All Cases - Consider NULL, empty, invalid
  4. Combine Validations - Use AND/OR for complex rules
  5. Document Rules - Explain validation logic

Error Handling​

Safe Operations​

// Safe division
=IF(AND(ISNUMBER([a]), ISNUMBER([b]), [b] != 0),
    [a] / [b],
    NULL)

// Safe text operation
=IF(ISTEXT([text]), UPPER([text]), "")

Additional Formula References​

Additional examples will be added soon.