ISNUMBER and ISBLANK in Excel: how to use it
ISNUMBER gives TRUE if a cell contains a number, ISBLANK if it's empty. You usually use them inside IF, to check before you calculate.
Try the first exercises for freeIn the lesson Catching errors with IFERROR
What do ISNUMBER and ISBLANK do?
ISNUMBER and ISBLANK answer a question about a cell with TRUE or FALSE. ISNUMBER asks: is there a number here? ISBLANK asks: is this cell empty?
So on their own, they only show TRUE or FALSE. Usually you put them in IF, to check first and only then calculate. The family has more members: ISTEXT, ISERROR and ISEVEN work the same way.
When do you use ISNUMBER and ISBLANK?
- Only calculating once a cell has been filled in: =IF(ISBLANK(B2),"To fill in",B2*C2).
- Catching text before it causes a #VALUE!: =IF(ISNUMBER(B2),B2*C2,"Not a number").
- Checking whether a word is in a cell: =ISNUMBER(SEARCH("quick",A2)).
- Marking empty cells in a list that has to be complete: =IF(ISBLANK(D2),"Missing","").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
TRUE if there's a number in the cell.
Filled in: =ISNUMBER(A2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
ISNUMBER | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | ISNUMBER |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
value | The cell you check, or a formula whose outcome you check. | A2 |
ISNUMBER and ISBLANK step by step
C1 contains =ISNUMBER(A2). 125 is a number, so TRUE. Type a text such as 125 kg into A2, and you get FALSE. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Click C1 and type ISNUMBER with a bracket.
=ISNUMBER( - Click the cell you want to check, A2.
=ISNUMBER(A2 - Close the bracket and press Enter: 125 is a number, so you see TRUE.
=ISNUMBER(A2)
More examples
Calculating after a check
D2 contains =IF(ISNUMBER(B2),B2*C2,"Not a number"). B2 contains the word two instead of the number 2. Without ISNUMBER, you'd get #VALUE!. Type 2 in B2, and the total appears.
Dealing with an empty cell
C2 contains =IF(ISBLANK(B2),"Missing","OK"). The email address is still missing. Fill in B2, and the status becomes OK.
Checking whether a word is in it
C1 contains =ISNUMBER(SEARCH("quick",A2)). SEARCH gives the position of quick, and that's a number. If the word isn't there, SEARCH gives an error, so ISNUMBER gives FALSE.
Together with IF
=IF(ISBLANK(B2),"Still to fill in",B2*C2) only calculates once B2 is filled in. =IF(ISNUMBER(B2),B2*C2,"Not a number") catches text before #VALUE! appears.
The other IS functions
ISTEXT checks for text, ISERROR for an error and ISEVEN for an even number. They all give TRUE or FALSE.
Checking whether a word is in a cell
SEARCH gives a position or an error. =ISNUMBER(SEARCH("fast",A2)) gives TRUE if the word is in the cell, and FALSE otherwise.
Where it often goes wrong
- A cell with a formula that returns "" looks empty, but ISBLANK gives FALSE.
- A number stored as text gives FALSE with ISNUMBER.
Practise this in the lesson Catching errors with IFERROR. The first exercises are free.
Frequently asked questions
What is the difference between ISBLANK(A2) and A2=""?
A2="" also gives TRUE for a cell with a formula that returns "". ISBLANK is stricter: only a truly empty cell gives TRUE.