“Numbers stored as text” is the silent cause of broken SUMs and failed lookups. ISNUMBER and ISTEXT reveal what a cell really holds, so you can find and fix the offenders.
The example
Spot the value that looks numeric but is text.
| A | B | |
|---|---|---|
| 1 | Value | ISNUMBER |
| 2 | 42 | TRUE |
| 3 | 42 (as text) | FALSE |
| 4 | Apple | FALSE |
The formula
The type test in B2:
How it works
These functions read the cell’s actual type:
ISNUMBER(A2)is TRUE only for real numbers — values you can add, average, or look up numerically.- A number typed with a leading apostrophe, imported as text, or left-aligned is text, so ISNUMBER returns FALSE even though it reads “42.”
ISTEXT(A2)is the opposite — TRUE for text.- Use them to audit a column:
=SUMPRODUCT(--ISTEXT(A2:A100))counts how many “numbers” are secretly text.
Fix text-numbers fast: multiply by 1 or add 0 (=A2*1), use VALUE(A2), or select the column and Data → Text to Columns → Finish to convert the whole lot at once.
Try it: interactive demo
Type a value; see whether Excel would treat it as a number or text.
Variations
Is it text?
The mirror test:
Count text-numbers in a column
Find the offenders:
Convert text to a number
Coerce with math or VALUE:
Pitfalls & errors
Left-aligned “numbers” are the tell. Real numbers right-align by default; text-numbers left-align. ISNUMBER confirms it.
ISNUMBER is also handy for “contains” tests. ISNUMBER(SEARCH(...)) uses it to convert “found a position” into TRUE/FALSE.
Dates and times are numbers. ISNUMBER returns TRUE for them, since Excel stores them as serial numbers — not a bug.
Practice workbook
Frequently asked questions
How do I check if a cell contains a number in Excel?
How do I find numbers stored as text?
How do I convert text that looks like a number into a real number?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class