ISTEXT function
Quick answer
ISTEXT returns TRUE when a value is text, including numbers that were stored as text and the empty text "" that some formulas return.
- Syntax
=ISTEXT(value)- Most common use
=SUMPRODUCT(--ISTEXT(B2:B6))returns 2 (Two ages were entered as text)
Use it to find entries like "forty" or "sick" in a column that should hold numbers, before they skew an average.
- Available in
- Every current version
- Category
- Information functions
- Returns
- TRUE if the value is text. FALSE for numbers, dates, blank cells, logical values and errors.
- Google Sheets
- Available in Google Sheets with the same argument.
Syntax
=ISTEXT(value)
| Argument | Required? | What it is |
|---|---|---|
value | Required | The value or cell to test. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =SUMPRODUCT(--ISTEXT(B2:B6)) | 2 | Two ages were entered as text |
| E2 | =IF(ISTEXT(B3),"Fix entry",B3) | Fix entry | "forty" is text, so the row is flagged |
| F2 | =AVERAGE(B2:B6) | 34 | AVERAGE skips the text, so it averages only three of five ages |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Respondent | Age | Text entries | Row 3 status | Average age | |
| R-101 | 34 | 2 | Fix entry | 34 | |
| R-102 | forty | ||||
| R-103 | 27 | ||||
| R-104 | 29 | ||||
| R-105 | 41 |
Practice ISTEXT
Graded instantly, then re-checked on changed data.
Payable hours
C2, use ISTEXT to return the hours from B2, or 0 when the entry is text such as "sick". Fill down to C7.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| TRUE for a number | The number is stored as text, such as the "29" above. VALUE(B5) converts it. |
| TRUE for a cell that looks empty | The cell holds a formula that returns "", which counts as text. Test =B2<>"" first if those should be skipped. |
Tips
- Highlight text in a numbers column with conditional formatting: =ISTEXT($B2).
Questions
How do I check if a cell contains text?
=ISTEXT(A2). It is TRUE for any text, including digits stored as text, and FALSE for empty cells.
How do I count cells that contain text?
=SUMPRODUCT(--ISTEXT(A2:A50)) counts them, including numbers that were stored as text.
Answers that use ISTEXT
In other languages
ISTEXT is ESTEXTO in Spanish Excel and ISTTEXT in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .