Excel Practice
Lessons Lesson 50

ISNUMBER, ISTEXT, ISBLANK and ISERROR

About this lesson

The IS functions ask a yes-or-no question about a cell: ISNUMBER, ISTEXT, ISBLANK, ISERROR and ISNA. They return TRUE or FALSE. So a formula can act on what a cell really holds, not what it looks like.

By the end you can

  • tell whether a cell holds a number, text or nothing with the IS functions.
  • find a number stored as text with ISNUMBER.
  • build a data check with IF and an IS function.

Practises ISNUMBER ISTEXT ISBLANK ISERRORISNA IF

The idea

A cell can look like a number and be text. It can look empty and hold an empty string. It can look fine and be an error waiting to spread. The IS functions are how a formula tells. Each takes one cell and returns TRUE or FALSE. That is exactly what IF wants as its test. IF(ISNUMBER(cell), ...) is the shape of nearly every data check on a sheet.

The one to learn first is ISNUMBER. A number stored as text is the most common silent fault in imported data. SUM skips it, COUNT ignores it, and a lookup for the real number never finds it. The one to be careful with is ISBLANK. A formula returning an empty string is not blank. Neither is a cell with a single space in it.

The mistake to watch for

Trusting ISBLANK. A cell holding a formula that returns an empty string is not blank. Neither is a cell with a single space in it. So a check built on ISBLANK misses both. And the fault ISNUMBER exists for is the most common one in imported data: a number stored as text. SUM skips it and a lookup never finds it, while it looks exactly like a number.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In C2, ask whether the amount in B2 is a real number, with ISNUMBER.

To begin, type it exactly:

=ISNUMBER(B2)

C2
Row A B C D E F G
1 Ref Amount Is a number? Is text? Is blank?
2 1001 120
3 1002 85
4 1003
5 1004 n/a
6 1005 42
7
8 Check on 1004

Every step

  1. 1

    In C2, ask whether the amount in B2 is a real number, with ISNUMBER. To begin, type it exactly: =ISNUMBER(B2).

    Hint. One argument.

  2. 2

    B3 looks like 85 but it arrived from another system as text. In C3, ask ISNUMBER about it.

    Hint. Same shape as C2.

  3. 3

    In D3, confirm it the other way round with ISTEXT.

    Hint. One argument.

  4. 4

    Ref 1003 has no amount at all. In E4, ask ISBLANK about B4.

    Hint. One argument.

  5. 5

    In B8, write a formula using IF and ISNUMBER. Show ok if the amount for ref 1004 in B5 is a number, and check if it is not.

    Hint. IF, with ISNUMBER as its test.

  6. 6

    ISERROR asks whether something came back as an error. Read =ISERROR(B2/0) and say what it returns.

    Hint. Is 120 divided by zero an error?