How to Use ISTEXT Function in Excel (3 Examples)

Quick View of Excel ISTEXT Function

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)

Quick View of Excel ISTEXT Function

 

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

Syntax of the ISTEXT Function

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.

ISTEXT Function with Single Value

  • Or it can be an array of values (Have to press Ctrl + Shift + Enter) in that case.

ISTEXT Function with Array

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.

Data Set for ISTEXT Function

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")

ISTEXT Function wih Dates

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.

ISTEXT Function with Questions

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.

ISTEXT Function with Validity Check of Answers

See, all the answers are showing invalid, because they are left blank.

Once you enter a text answer, it will be shown as valid.

ISTEXT Function with Valid Answer

And if you enter anything other than text as the answer, it will show invalid.

ISTEXT Function with Invalid Answer

Explanation of the Formula

  • ISTEXT(C4) returns a TRUE if the value in cell C4 is a text, otherwise FALSE.
  • IF(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.)

ISTEXT Array Formula

Explanation of the Formula

IF(ISTEXT(C4:C20),"Not Correctly Entered","Correctly Entered") is a combination of 17 single formulas.

  1. IF(ISTEXT(C4),"Not Correctly Entered","Correctly Entered")
  2. IF(ISTEXT(C5),"Not Correctly Entere","Correctly Entered")
  3. IF(ISTEXT(C6),"Not Correctly Entered","Correctly Entered")

  1. 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

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo