Excel track · Exercise 27 of 86 Paid

ISBLANK, ISNUMBER, ISTEXT: reading a cell's type

Tell an empty cell from a text cell and a number, and read the answer as a logical value.

Advanced

One column mixing numbers, text, a logical value and a blank

Read-only data · screen 1 of 2 · answer in B2

In B2, answer TRUE when A3 is empty.

	A	B
1	Value	Answer
2	12
3	text
4
5	true
6	4.5
7	n/a
8	Empty?

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

In B2, answer TRUE when A7 holds a number.

	A	B
1	Value	Answer
2	12
3	text
4
5	true
6	4.5
7	n/a
8	A number?

Download this grid (.xlsx) Two tabs: the grid you see, and the answer.

Reference answer

What it does

The IS functions ask what kind of value a cell holds rather than how large it is. ISBLANK is TRUE only for a cell that was never filled. ISNUMBER is TRUE for a number or a date, since Excel stores both as serial numbers, and ISTEXT is its mirror. All three return a logical value, so IF and conditional formatting can read them directly.

A3 holds the word text, so ISBLANK answers FALSE on the first screen. The unfilled cell is A4, and pointing the formula there answers TRUE. The second screen tests A7, which holds n/a, and ISNUMBER answers FALSE while ISTEXT answers TRUE.

A cell holding an empty string is where the readings separate. A formula that returns "" leaves text of length zero, so ISBLANK says FALSE. COUNTIF with an empty criterion counts that cell and the unfilled one together, so a blank count can run higher than the cells you never touched.

Syntax

=ISBLANK(cell), =ISNUMBER(cell), =ISTEXT(cell) return a logical value

Worked example

On One column mixing numbers, text, a logical value and a blank, =ISBLANK(A3) returns false.

=ISBLANK(A3)

Common errors

Error codes for ISBLANK, ISNUMBER and ISTEXT as type tests that return logical values
Error	What it means and how to fix it
`#VALUE!`	The logical answer was used in arithmetic, as when a TRUE is divided by zero.
`#NAME?`	The function name was mistyped, and Excel returns #NAME?.
`Text answer`	The cell holds the characters FALSE typed in quotes, so a later IF reads a label where it expected a logical value.

At work

Finding the rows where a due date was never entered instead of comparing to an empty string.