Checking cells with ISNUMBER, ISTEXT and ISBLANK
1 / 5
Some quantities were typed badly: words, gaps, and numbers stored as text. Find the ones stored as text, which SUM skips.
keyboard Typing formulas is easiest on a computer with a keyboard.
A1
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Quantity | Text? | |
| 2 | Monitor Stand | TBC | ||
| 3 | Headset | 21 | ||
| 4 | Office Chair | |||
| 5 | Bookshelf | 29 | ||
| 6 | USB-C Hub | 39 | ||
| 7 | Notebook A5 | 15 | ||
| 8 | Filing Cabinet | 10 | ||
| 9 | ||||
| 10 | ||||
| 11 | ||||
| 12 |
quantity
Text or OK
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
How ISNUMBER and ISTEXT and ISBLANK works
What do
ISNUMBER,ISTEXTandISBLANKdo?They ask what kind of value a cell holds, and answer
TRUEorFALSE:ISNUMBER(B2): is it a number?ISTEXT(B2): is it text?ISBLANK(B2): is it empty?
Why can a number be text?
A number typed with an apostrophe in front, or copied from another system, can be stored as text. It looks the same, but
ISNUMBERsaysFALSE,ISTEXTsaysTRUEandSUMskips it. In Excel it sits on the left of the cell, where real numbers sit on the right.How do you use them in
IF?Use one as
IF's question:=IF(ISNUMBER(B2),"OK","Check")flags anything that isn't a real number, blanks included.=IF(ISTEXT(B2),"Text","OK")finds words and numbers stored as text.=IF(ISBLANK(B2),"Missing","OK")finds the gaps.
Know someone fighting with a spreadsheet? Send them this page.