ISBLANK, ISNUMBER and ISTEXT in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
ISBLANK returns TRUE when a cell is completely empty, ISNUMBER returns TRUE when it holds a real number, and ISTEXT returns TRUE when it holds text. Each takes one cell, like =ISBLANK(C2). They are most useful inside IF, for example =IF(ISNUMBER(C2),"OK","Check"), to catch bad data before you calculate with it.
Key takeaways
- ISBLANK, ISNUMBER and ISTEXT each take one value and return TRUE or FALSE.
- ISBLANK is FALSE for a cell holding a formula that returns "" or a single space; use =C2="" to treat those as blank.
- A number stored as text, like '40, is TRUE for ISTEXT and FALSE for ISNUMBER, and SUM skips it.
- ISNUMBER(SEARCH("word",A2)) is the standard way to test whether a cell contains some text.
- Dates are numbers in Excel, so ISNUMBER returns TRUE for a real date.
On this page+
- What do the IS functions do?
- Excel ISBLANK: how to check for empty cells
- Why does ISBLANK say FALSE for a cell that looks empty?
- How to use ISNUMBER and ISTEXT to find numbers stored as text
- How to check if a cell contains certain text
- How to combine IS functions with other logic
- What are the common mistakes?
- Practise it
Excel ISBLANK tells you whether a cell is empty. Its partners ISNUMBER and ISTEXT tell you whether a cell holds a number or text. Together they let you check imported or typed data before a formula uses it, which is how you catch missing values and numbers stored as text. This guide covers each function, the traps, and how to use them with IF.
What do the IS functions do?#
Each IS function takes one value and answers a yes-or-no question about it, with TRUE or FALSE:
| Function | Returns TRUE when the value is | Example |
|---|---|---|
| ISBLANK | An empty cell | =ISBLANK(C2) |
| ISNUMBER | A number, including a date | =ISNUMBER(C2) |
| ISTEXT | Text | =ISTEXT(C2) |
| ISNONTEXT | Anything that isn't text, including an empty cell | =ISNONTEXT(C2) |
| ISERROR | Any error value | =ISERROR(D2) |
| ISNA | The #N/A error | =ISNA(D2) |
| ISLOGICAL | TRUE or FALSE | =ISLOGICAL(F2) |
Give each one a single cell, not a range: =ISBLANK(C2), then fill down. All of them work in every current version
of Excel. There are a few more in the family: ISEVEN and ISODD check whole numbers, and ISFORMULA (Excel 2013 and
later) checks whether a cell holds a formula rather than a typed value.
They also work as conditional formatting rules. Select C2:C8, add a rule that uses the formula =ISTEXT(C2), and
every quantity stored as text lights up, so you can fix them before anyone totals the column.
Here is an order list from a web form, with a messy Qty column:
| Order | Qty | ISBLANK | ISNUMBER | ISTEXT |
|---|---|---|---|---|
| 2041 | 12 | FALSE | TRUE | FALSE |
| 2042 | (empty) | TRUE | FALSE | FALSE |
| 2043 | TBC | FALSE | FALSE | TRUE |
| 2044 | '40 | FALSE | FALSE | TRUE |
| 2045 | 25 | FALSE | TRUE | FALSE |
Excel ISBLANK: how to check for empty cells#
ISBLANK returns TRUE only when a cell is completely empty:
=ISBLANK(C2)On its own, it shows TRUE or FALSE. Inside IF, it becomes a clear label:
=IF(ISBLANK(C2),"Missing","OK")You can also flip it. =NOT(ISBLANK(C2)) is TRUE when the cell has been filled in, and
=IF(ISBLANK(C2),"",C2*2) only calculates when there is something to calculate with. If IF is new to you, see
the Excel IF function explained simply.
Why does ISBLANK say FALSE for a cell that looks empty?#
Because the cell isn't really empty. Three things look blank but aren't:
- A formula that returns "", such as
=IF(B2>0,B2,""). The cell holds empty text. - A space, often left behind when someone "cleared" a cell by typing a space.
- An apostrophe on its own, which some exports add.
ISBLANK returns FALSE for all three. If you want them treated as blank, use one of these instead:
| Formula | TRUE for an empty cell | TRUE for "" | TRUE for a space |
|---|---|---|---|
=ISBLANK(C2) | Yes | No | No |
=C2="" | Yes | Yes | No |
=LEN(TRIM(C2))=0 | Yes | Yes | Yes |
For counting, COUNTBLANK treats cells with "" as blank, but ISBLANK doesn't. The differences between the counting functions are in COUNT vs COUNTA vs COUNTBLANK.
How to use ISNUMBER and ISTEXT to find numbers stored as text#
Look at order 2044 above. Its quantity shows 40, but ISNUMBER says FALSE. The cell holds '40: the apostrophe stores
the number as text. Imported data often does this. It looks right, but SUM skips it, so totals come out too low,
and =C5*2 may still work, which hides the problem.
Signs of a number stored as text:
- It sits on the left of the cell. Real numbers align right by default.
- A small green triangle appears in the top-left corner of the cell.
=ISNUMBER(C5)returns FALSE, and=ISTEXT(C5)returns TRUE.
To fix it, retype the number, use the warning triangle's Convert to Number option, or convert with a formula such
as =VALUE(C5). Then use ISNUMBER as a check in IF:
=IF(ISNUMBER(C2),"OK","Check")This one test catches the empty cell, "TBC" and '40, because none of them is a real number. ISBLANK alone would miss the last two. Remember that dates are numbers in Excel, so ISNUMBER is also a quick test for whether a date is real or just text.
How to check if a cell contains certain text#
Combine ISNUMBER with SEARCH. SEARCH returns the position of some text inside a cell, or #VALUE! if it isn't there. ISNUMBER turns that into TRUE or FALSE:
=ISNUMBER(SEARCH("cafe",B2))For "Green Leaf Cafe" this is TRUE. SEARCH ignores capital letters; use FIND instead if case matters. Wrap it in IF
for a label: =IF(ISNUMBER(SEARCH("cafe",B2)),"Food","Other"). The two text functions are compared in
FIND vs SEARCH in Excel.
How to combine IS functions with other logic#
IS functions work well with AND, OR and the IF family:
| Goal | Formula |
|---|---|
| A real number above 0 | =AND(ISNUMBER(C2),C2>0) |
| Missing or not a number | =IF(NOT(ISNUMBER(C2)),"Check","OK") |
| Label several cases | =IFS(ISBLANK(C2),"Missing",ISTEXT(C2),"Not a number",TRUE,"OK") |
The IFS version gives a different message for each problem. It needs Excel 2019 or later; for older versions, write the same thing as a nested IF. See the Excel IFS function and nested IF for bands and tiers.
To check for errors rather than data types, ISERROR and ISNA are the IS functions behind IFERROR and IFNA. For most reports, IFERROR or IFNA is shorter. The IF function guide has more on building the tests.
What are the common mistakes?#
| Mistake | What happens | Fix |
|---|---|---|
A range instead of one cell: =ISBLANK(C2:C8) | Only one result, or a spill of results | One cell, then fill down |
| Expecting ISBLANK to catch "" | FALSE for formula blanks | Use =C2="" |
| ISBLANK to check for valid quantities | Text like "TBC" passes | Use ISNUMBER |
Quotes around the cell: =ISTEXT("C2") | Always TRUE, because "C2" is text | Write =ISTEXT(C2) |
Practise it#
Try the ISBLANK exercise on this page, then practise the IS functions and SWITCH in a live spreadsheet. You'll check a messy imported order list, find a number stored as text and fix it, and turn status codes into labels.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Order 2042 has no quantity. In E3, show Missing if the quantity in C3 is empty, and OK if it isn't. Use ISBLANK as the test inside IF.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Qty check | |||||
| 2 | S | |||||
| 3 | Green Leaf Cafe | P | ||||
| 4 | D | |||||
| 5 | S | |||||
| 6 | D | |||||
| 7 | ||||||
| 8 | ||||||
| 9 |
Tip: ISBLANK takes one cell. Put it where IF's test goes, then the two labels in double quotes.
Practise this in a lesson
Frequently asked questions
How do I use ISBLANK in Excel?
Type =ISBLANK(C2). It returns TRUE if C2 is empty and FALSE if it holds anything. Use it inside IF for a label, like =IF(ISBLANK(C2),"Missing","OK").
Why does ISBLANK return FALSE for a cell that looks empty?
The cell isn't truly empty. It may hold a formula that returns "", a space, or an apostrophe. Use =C2="" to treat empty text as blank too, or =LEN(TRIM(C2))=0 to also ignore spaces.
How do I check if a cell is not blank?
Use =NOT(ISBLANK(C2)) or =C2<>"". Both return TRUE when C2 holds something; the second also treats a formula returning "" as blank.
How do I check if a cell contains a number?
Use =ISNUMBER(C2). It is TRUE for real numbers and dates, and FALSE for text, including numbers stored as text.
How do I check if a cell contains certain text?
Use =ISNUMBER(SEARCH("cafe",B2)). SEARCH returns a position number when it finds the text, and an error when it doesn't, so ISNUMBER turns that into TRUE or FALSE.