Rifat Hassan
While working in Excel, we have to come across a lot of data types, like text values, numbers, boolean values, blank cells, etc. Most of the time they can be identified while looking through naked eyes.
Yet Microsoft Excel provides functions to identify the data types for the sake of our convenience, and also for the sake of using them in formulas.
Today I will be talking about the uses of the ISTEXT function in Excel, which will help you to identify whether a given value is a text value or not.
ISTEXT Function of Excel (Quick View)
Download Practice Workbook
Excel ISTEXT Function: Syntax and Argument
Summary
- Checks whether a value is text or not, if text, returns a TRUE. Otherwise FALSE.
- Works for both Array and Non-Array values.
- Available from Excel 2003.
Syntax
The Syntax of the ISTEXT function is:
=ISTEXT(value)Argument
| Argument | Required or Optional | Value |
| value | Required | The value which we want to know is whether text or not. Can be any value or an array of values. |
Note:
- The argument can be a single value.
- Or it can be an array of values (Have to press Ctrl + Shift + Enter) in that case.
Return Value
Returns a Boolean value (TRUE or FALSE). TRUE if the value is a text, FALSE otherwise.
Excel ISTEXT Function: 3 Examples
1. Checking whether a Date is Correctly Entered or Not
Look at the data set below. We have the joining dates of some employees of a company named Dynamo Group.
Now if you want to do any calculation with the dates, we first have to know whether they are correctly entered as dates or not.
Otherwise, we will encounter errors while calculating if any date is not entered as a date correctly.
We can perform the check using the ISTEXT function of Excel.
Enter this formula in the first cell of a new column and then drag the Fill Handle.
=IF(ISTEXT(C4),"Not Correctly Entered","Correctly Entered")
See, a few of the dates are not correctly entered. In fact, they are text values.
Explanation of the Formula
ISTEXT(C4)returns a TRUE if the value in cell C4 is a text, otherwise FALSE.IF(ISTEXT(C4),"Not Correctly Entered","Correctly Entered")returns “Not Correctly Entered” if it sees a TRUE, and for a FALSE it returns “Correctly Entered”.- Same for the rest of the cells.
- Thus, if any date is entered as a text, a “Not Correctly Entered” is returned.
| Formula | Output | Explanation |
| =IF(ISTEXT(C4),”Not Correctly Entered”,”Correctly Entered”) | Correctly Entered | First checks whether the value is a text value or not. If it is a text value, returns “Not Correctly Entered”, otherwise returns “Correctly Entered”. |
2. Checking whether an Answer is Valid or Not
Look at the data set below. We have five questions in a column and empty spaces left for the answers.
We have kept an additional column called Validity Check. Each time you enter the answer to a question, it will show whether the answer is valid or invalid.
How to do that? Simple. The answers must be some text values, not any number.
So if you enter any number as the answer, rather than any text value, it will show “Invalid”.
So the formula will be:
=IF(ISTEXT(C4),"Valid","Invalid")Enter this formula in the first cell and then drag the Fill Handle.
See, all the answers are showing invalid, because they are left blank.
Once you enter a text answer, it will be shown as valid.
And if you enter anything other than text as the answer, it will show invalid.
Explanation of the Formula
ISTEXT(C4)returns a TRUE if the value in cell C4 is a text, otherwise FALSE.- I
F(ISTEXT(C4),"Valid","Invalid")returns “Valid” if it sees a TRUE, and for a FALSE it returns “Invalid”. - Same for the rest of the cells.
- Thus, if any answer is entered as anything other than a text, it returns “Invalid”. And when the answer is a text value, it returns “Valid”.
| Formula | Output | Explanation |
| =IF(ISTEXT(C4),”Valid”,”Invalid”) | Valid | First checks whether the value is a text value or not. If it is a text value, returns “Valid”, otherwise returns “Invalid”. |
3. ISTEXT Array Formula
Up to now, we have applied the formula on a single cell and then dragged the Fill Handle to copy the formula to the rest of the cells.
But we can apply the formula simultaneously on all the cells using the Array Formula.
This time we will do it.
In example 1, the Array Formula to check whether the dates have been entered correctly or not simultaneously in all the cells will be:
=IF(ISTEXT(C4:C20),"Not Correctly Entered","Correctly Entered")(Do not forget to press Ctrl + Shift + Enter unless you are in Office 365.)
Explanation of the Formula
IF(ISTEXT(C4:C20),"Not Correctly Entered","Correctly Entered") is a combination of 17 single formulas.
IF(ISTEXT(C4),"Not Correctly Entered","Correctly Entered")IF(ISTEXT(C5),"Not Correctly Entere","Correctly Entered")IF(ISTEXT(C6),"Not Correctly Entered","Correctly Entered")
…
…
…
IF(ISTEXT(C20),"Not Correctly Entered","Correctly Entered")
Each of them executes the task mentioned in Example 1.
| Formula | Output | Explanation |
| IF(ISTEXT(C4:C20),”Not Correctly Entered”,”Correctly Entered”) | Correctly Entered
Correctly Entered Correctly Entered … … … Not Correctly Entered |
First breaks the formula into 17 single formulas. Then checks whether each value in the array C4 to C20 is a text or not. For those which are texts, it returns “Not Correctly Entered”. And for those which are not texts, it returns “Correctly Entered”. |
Conclusion
Thus you can use the ISTEXT function of Excel to check whether a value is a text value or not in Excel. This is pretty useful? Isn’t it? Do you have any questions? Feel free to ask us.
Further Readings
- How to Use ISLOGICAL Function in Excel (4 Examples)
- How to use ISNA function in Excel (4 Examples)
- How to Use ISEVEN Function in Excel (2 Examples)
- How to Use ISODD Function in Excel (With 4 Examples)
- How to Use ISNUMBER Function in Excel (7 Examples)
- How to Use ISBLANK Function in Excel (2 Examples)
- How to Use ISERROR Function in Excel (With 5 Examples)
