Part of the free Module 5: Excel Formulas and Functions · Function 48 of 105 · Full Excel course
The Excel ISNONTEXT function returns TRUE when a value is anything other than text: numbers, dates, logical values, errors and even empty cells all qualify. It returns FALSE only for text strings. ISNONTEXT in Excel is handy for validating that a numeric column contains no stray labels and for counting non-text entries in mixed data.
ISNONTEXT syntax
=ISNONTEXT(value)Arguments
| Argument | Required | Meaning |
|---|---|---|
| value | Required | The cell, expression or formula result to test. |
ISNONTEXT is the exact opposite of ISTEXT: for any value, one returns TRUE and the other FALSE. Numbers stored as text (left-aligned digits) count as text, so ISNONTEXT returns FALSE for them.
Step-by-step example
=ISNONTEXT(A1)- Excel reads A1. Suppose it contains 10.
- 10 is a number, not text.
- ISNONTEXT returns TRUE. If A1 contained the word Excel, the result would be FALSE; if A1 were empty or held a date, TRUE.
Practical use cases
1. Validate a numeric column
=IF(ISNONTEXT(B2), "OK", "Text found")Flags rows where someone typed “N/A” or “TBC” into an amount column.
2. Count non-text cells
=SUMPRODUCT(--ISNONTEXT(E1:E10))3. Exclude blanks from the count
=SUMPRODUCT(ISNONTEXT(E1:E10)*(E1:E10<>""))Because ISNONTEXT treats empty cells as non-text, add a second condition when blanks should not count.
4. Data validation that blocks text
=ISNONTEXT(C2)Note that this still allows the cell to be left blank; pair with a required-field check if needed.
5. Sum only real numbers in a mixed range
=SUMPRODUCT(--ISNONTEXT(B2:B100), --ISNUMBER(B2:B100), B2:B100)ISNUMBER alone is usually enough here; the example shows how the tests combine.
6. Highlight text in a numbers-only region
=NOT(ISNONTEXT(B2))Common mistakes and errors
- Blanks count as non-text – ISNONTEXT of an empty cell is TRUE. Add an ISBLANK or <>”” test if blanks should be excluded.
- Errors count as non-text – #N/A and #DIV/0! return TRUE. Use ISNUMBER when you specifically want numbers.
- Numbers stored as text – “123” imported as text returns FALSE. Convert with VALUE or Text to Columns before relying on ISNONTEXT.
- Range in a single cell (legacy Excel) – only the same-row cell is tested unless wrapped in SUMPRODUCT.
- Using it where ISNUMBER is meant – ISNONTEXT is broad; if you need “is a real number”, ISNUMBER is the precise test.
Tips and best practices
- Use ISNONTEXT as a first-pass filter on imported columns, then ISNUMBER for the precise numeric check.
- Handle blanks explicitly because ISNONTEXT counts them as non-text; add <>”” when they should be excluded.
- Combine with data validation to block text in amount columns, and pair with a required-field rule.
- Convert numeric text once with Text to Columns rather than adjusting every formula.
- Prefer ISTEXT for the positive test; NOT(ISNONTEXT(x)) is just ISTEXT(x).
Related functions
- ISTEXT – the exact opposite.
- ISNUMBER – TRUE only for numbers and dates.
- ISBLANK – separate blanks from other non-text values.
- SUMPRODUCT – counts and sums across arrays of test results.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
Does ISNONTEXT return TRUE for blank cells?
Yes. An empty cell is not text, so ISNONTEXT returns TRUE. Combine with ISBLANK or a <>”” test when blanks should be treated separately.
Does ISNONTEXT recognise error values?
Yes. Errors such as #N/A or #VALUE! are non-text, so ISNONTEXT returns TRUE for them rather than propagating the error.
What is the difference between ISNONTEXT and ISNUMBER?
ISNONTEXT is TRUE for everything except text, including blanks, Booleans and errors. ISNUMBER is TRUE only for genuine numbers and dates. Use ISNUMBER when you need numeric data specifically.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.