🚀The world's best VBA AI has evolved. ExcelMaster is now an autonomous Agent.Read more →
Back to Blog

Excel IS Functions — ISNUMBER, ISTEXT, ISBLANK, ISERROR & ISNA (Guard a Formula Before It Breaks)

|

Excel IS Functions — ISNUMBER, ISTEXT, ISBLANK, ISERROR & ISNA (Guard a Formula Before It Breaks)

TL;DR — The IS… functions each answer one yes/no question about a cell: ISNUMBER, ISTEXT, ISBLANK, ISERROR, ISNA (and friends). They classify, they never change the value — their whole job is to guard a formula before it breaks. Three things to know: ISBLANK is stricter than "looks empty" — a cell holding ="" or a formula that returns "" is not blank; ISERROR swallows every error type (hiding real #REF!/#NAME? bugs) while ISNA catches only #N/A; and ISNUMBER(SEARCH(...)) is the cleanest way to test a partial text match. Reach for an IS-function only when a built-in guard (IFERROR, XLOOKUP's 4th argument) doesn't already do the job. Works in all versions.

=IF(ISNUMBER(SEARCH("apple", A2)), "Match", "No")   ' partial, case-insensitive
=IF(ISBLANK(A2), "Missing", A2)                      ' true-blank check

The IS family are the diagnostic tools of Excel — small functions that report what a cell is rather than compute a new value. Used well, they stop a formula from erroring on the one bad row. Used carelessly, they hide the very bugs you needed to see. The difference is knowing what each one really tests.

What you'll learn

  • The mental model: inspectors that return a verdict, never a new value
  • Why ISBLANK is stricter than "empty-looking" — the "" trap
  • ISERROR (catches everything) vs ISNA (only #N/A) — and why it matters
  • The hidden cost of IF(ISERROR(x), fallback, x): it computes x twice
  • ISNUMBER's killer app — turning SEARCH into a clean partial-match test
  • Where IS-functions shine without an IF at all

The mental model: inspectors, not transformers

Every IS… function takes a value and returns TRUE or FALSE — a verdict about what's in the cell. That's all. ISNUMBER asks "is this a number?"; ISTEXT asks "is this text?"; ISBLANK asks "is this truly empty?"; ISERROR asks "did this blow up?".

=ISNUMBER(A2)    ' TRUE if A2 holds a number (not text that looks numeric)
=ISTEXT(A2)      ' TRUE if A2 holds text
=ISERROR(A2)     ' TRUE if A2 is any error value

Because they only ever report, an IS-function is never the final answer — like AND/OR, its verdict has to feed something: an IF, a conditional-formatting rule, a data-validation formula, or an array mask. And that framing gives you the governing principle for the whole family: use an IS-function to guard a formula before it errors — but only when a cleaner, purpose-built guard doesn't already exist.

ISBLANK is stricter than "looks empty"

ISBLANK is the one everybody misreads. It returns TRUE only for a cell that is genuinely, physically empty — nothing typed, no formula. A cell that looks empty because a formula returned an empty string is not blank:

=ISBLANK(A2)    ' A2 contains  =""  or  =IF(...,"")  ->  FALSE  (it holds a formula)
=ISBLANK(A2)    ' A2 is a leftover space " "         ->  FALSE  (it holds a space)

This is the source of "but the cell IS empty, why does my check fail?" The honest test for "no visible content" is usually a value comparison, not ISBLANK:

=IF(A2="", "Empty", "Filled")       ' treats "" from a formula as empty too
=IF(LEN(A2)=0, "Empty", "Filled")   ' same idea, explicit about zero length

Use ISBLANK when you specifically mean "nobody has put anything here" — for example, telling a truly untouched input cell apart from one a formula filled with "". Use ="" or LEN()=0 when you mean "nothing shows." They are not interchangeable, and picking the wrong one is a classic silent bug. (This is the mirror of the IF habit of returning "" — that "" is exactly what fools ISBLANK.)

ISERROR catches everything; ISNA catches only #N/A

ISERROR returns TRUE for any error — #N/A, #REF!, #DIV/0!, #NAME?, #VALUE!, the lot. That sounds convenient and is usually a mistake. When you wrap a lookup in IF(ISERROR(...)), you don't just catch "not found" — you also silence a #REF! from a deleted column or a #NAME? from a typo, so a genuine formula bug disappears behind your fallback and you never find out:

=IF(ISERROR(VLOOKUP(A2, T, 2, 0)), "Not found", VLOOKUP(A2, T, 2, 0))
' also hides #REF!/#NAME? — a real bug now shows "Not found" and you never know

The precise tool is ISNA, which catches only #N/A — the "no match" error — and lets every other error surface loudly, exactly where you want a real bug to stay visible:

=IF(ISNA(MATCH(A2, List, 0)), "New", "Existing")   ' only "no match" is handled

This is the same lesson as IFERROR vs IFNA: catch the one error you expect, not all of them. Match the width of your net to the fish you're actually trying to catch.

The hidden cost: IF(ISERROR(x), fallback, x) computes x twice

Even when you do want to handle an error, the old IF(ISERROR(...)) pattern has a real drawback: it evaluates the expensive part twice — once to test it, once to return it. On a heavy lookup across thousands of rows that doubles the work:

' Legacy — VLOOKUP runs twice per cell
=IF(ISERROR(VLOOKUP(A2,T,2,0)), "n/a", VLOOKUP(A2,T,2,0))

' Modern — computed once
=IFERROR(VLOOKUP(A2,T,2,0), "n/a")
=IFNA(VLOOKUP(A2,T,2,0), "n/a")            ' only #N/A, keeps real errors visible
=XLOOKUP(A2, Keys, Vals, "n/a")            ' built-in if_not_found, no wrapper at all

So the honest hierarchy is: prefer IFERROR/IFNA or XLOOKUP's fourth argument, and keep IF(ISERROR(...)) only for the rare case where the error branch returns something different from recomputing the value. ISERROR/ISNA still earn their place when you need the boolean itself — inside a SUMPRODUCT, a conditional format, or a count of how many rows errored.

ISNUMBER's killer app: a clean partial-match test

The single most useful thing in the family isn't a type check for its own sake — it's pairing ISNUMBER with SEARCH/FIND. SEARCH returns a position when it finds your substring and a #VALUE! error when it doesn't. Wrapping it in ISNUMBER converts that "number-or-error" into a clean TRUE/FALSE "contains this text?":

=ISNUMBER(SEARCH("apple", A2))   ' TRUE if A2 contains "apple" (case-insensitive)
=ISNUMBER(FIND("APPLE", A2))     ' FIND is case-sensitive

That's the idiomatic "does this cell contain X" test, and it drops straight into conditional formatting (highlight every row mentioning a keyword), a FILTER mask, or a SUMPRODUCT count — none of which want an error value in the middle of them.

ISNUMBER also diagnoses "numbers stored as text"

ISNUMBER is the fastest way to confirm the number-one data-import headache: values that look numeric but are actually text, so they refuse to sum or sort. If =ISNUMBER(A2) returns FALSE for something that looks like 1234, you've found your culprit:

=ISNUMBER(A2)   ' FALSE for a number-stored-as-text  ->  fix with VALUE / *1

Once diagnosed, convert it with VALUE/NUMBERVALUE or a *1 / -- coercion. ISNUMBER is the test; those are the cure.

Where IS-functions shine without an IF

Because they already return TRUE/FALSE, IS-functions are a perfect fit for the places that want a boolean and no label — no IF needed:

  • Conditional formatting — a rule of =ISERROR(A2) shades every error cell red; =ISNUMBER(SEARCH("urgent", A2)) highlights matching rows.
  • Data validation — a custom rule of =ISNUMBER(A1) blocks anyone typing text into a numeric input.
  • Counting — =SUMPRODUCT(--ISNUMBER(A2:A100)) counts how many cells are genuine numbers; =SUMPRODUCT(--ISERROR(Range)) counts errors before you clean.

In all three the verdict is the answer — wrapping it in an IF(..., TRUE, FALSE) would just be noise.

The judgment call: which IS-function, or a cleaner guard

  • "Is nothing here at all?" → ISBLANK for a truly untouched cell; ="" or LEN()=0 if a formula's "" should also count as empty.
  • "Did a lookup miss?" → IFNA / XLOOKUP's 4th argument first; ISNA when you need the boolean; never ISERROR (it hides real bugs).
  • "Handle any error at all?" → IFERROR (computes once) over IF(ISERROR(...)) (computes twice).
  • "Does this text contain X?" → ISNUMBER(SEARCH(...)).
  • "Is this a real number?" → ISNUMBER, then fix with VALUE/*1.

How ExcelMaster helps

The IS-functions are where "make it not error" quietly turns into "hide the bug I needed to see." ExcelMaster picks the guard that fits the intent — ISNA or IFNA when only a missing lookup should be handled, IFERROR when any failure should fall back, a true-blank ISBLANK versus a LEN()=0 when "empty" is ambiguous — so your safety net catches the case you meant and lets the genuine errors surface where you can fix them.

Frequently asked questions

Why does ISBLANK return FALSE on an empty-looking cell?

Because the cell isn't truly empty — it holds a formula that returns "", a space, or an invisible character. ISBLANK is TRUE only for a physically empty cell. To treat a formula's "" as empty too, test =A2="" or =LEN(A2)=0 instead.

What is the difference between ISERROR and ISNA?

ISERROR returns TRUE for any error value; ISNA returns TRUE only for #N/A. Use ISNA (or IFNA) for lookups so a genuine #REF! or #NAME? still shows up instead of being hidden behind your fallback.

How do I check if a cell contains specific text?

Wrap SEARCH in ISNUMBER: =ISNUMBER(SEARCH("apple", A2)) is TRUE when A2 contains "apple" (case-insensitive). Use FIND instead of SEARCH for a case-sensitive test.

Should I use IF(ISERROR(...)) or IFERROR?

Prefer IFERROR — it evaluates the formula once, while IF(ISERROR(x), fb, x) computes x twice. Keep IF(ISERROR(...)) only when the error branch returns something other than recomputing the value.

How do I know if a number is really text in Excel?

Test it with =ISNUMBER(A2). If it returns FALSE for something that looks numeric, the value is stored as text — convert it with VALUE, NUMBERVALUE, or a *1 / -- coercion.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-04.

Related guides: Excel IFERROR · Excel AND & OR · Excel NOT & XOR · Excel FIND & SEARCH · Excel VALUE