Rifat Hassan
Today we 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.
Actually, 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 the naked eye.
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.
Now, let’s talk about the ISTEXT function. For conducting the session, we’re going to use the Microsoft 365 version.
Download Practice Workbook
Here, you can download the Practice Workbook.
Excel ISTEXT Function: Syntax and Argument
In this section, we will explain the syntax and argument of the ISTEXT function.
ISTEXT Function of Excel (Quick View)
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. |
Return Value:
Returns a Boolean value (TRUE or FALSE). TRUE if the value is a text, FALSE otherwise.
8 Suitable Examples to Use ISTEXT Function in Excel
Here, we will describe 8 suitable examples of how to use an ISTEXT function in Excel. In addition, for your better understanding, we’re going to use some sample datasets.
1. Use of ISTEXT Function
We will use the ISTEXT function to test whether the cell values are text or not. So, follow the given steps.
Steps:
- Firstly, you have to select a new cell C5 where you want to keep the result.
- Secondly, you should use the formula given below in the C5 cell.
=ISTEXT(B5)- Subsequently, press ENTER.
- After that, you have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells C6:C9. Or you can double-click on the Fill Handle icon.
As a result, you will get the following output.
2. Checking whether an Answer Is Valid or Not
Now, look at the data set below. Here, we have five questions in a column and empty spaces left for the answers.
Furthermore, 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.
So, how to do that?
Actually, it’s very 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”.
- Now, write the following formula in the D5 cell.
=IF(ISTEXT(C5),"Valid","Invalid")- Then, press ENTER.
As you enter a text answer, it is showing as valid.
- Then, drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells C6:C9.
And if you enter anything other than text as the answer, it will show invalid.
Formula Explanation:
- Firstly, ISTEXT(C5) returns a TRUE if the value in cell C5 is a text, otherwise FALSE.
- Secondly, IF(ISTEXT(C5),”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”.
3. Employing ISTEXT Function to Count Cells Containing Text
Look at the data set below. Here, we have the joining dates and designations of some employees of a company named Dynamo Group.
Now, say, you want to know how many employees entered their valid names. Or suppose, we will find out the number of total enrolled employees. Here, to do so, we will use SUMPRODUCT and ISTEXT functions.
- So, write the following formula in the F12 cell.
=SUMPRODUCT(--ISTEXT(B5:B21))
Formula Explanation:
- Firstly, ISTEXT(B5:B21) will check whether the values of B5:B21 cells are text or not. If the cell contains text then it will return TRUE otherwise FALSE.
- Secondly, the Double Hyphen (–) converts the logical values into binary numbers.
- Thirdly, the SUMPRODUCT function will count all those numbers.
- Finally, press ENTER to get the result.
4. Using ISTEXT Function in Data Validation
Now, we will show another example using the ISTEXT function in Data Validation. So, let’s see the steps given below.
Steps:
- Firstly, select cells B5:B11 where you want to insert only one kind of cell value (text values).
- Secondly, from the Data tab >> go to the Data Tools option.
- Finally, from the Data Validation feature >> choose Data Validation… option.
At this time, a dialog box named Data Validation will appear.
- Now, from the Settings menu >> choose Custom in the Allow: box.
- Then, write the following formula in the Formula box.
=ISTEXT(D5:D11)
- After that, from the Error Alert menu.
- Then, write the following text in the Error message box.
Please Enter Name- Lastly, press OK to make the changes.
As a result, if you enter any number or date in the D5:D11 cells then you will get the following notice from Microsoft Excel.
5. Employing Conditional Formatting with ISTEXT Function
You can use the ISTEXT function with Conditional Formatting to highlight the cells which contain text as cell value.
- Firstly, you should select the B5 cell at which you want to apply the Conditional Formatting to color the cell containing text value.
- Then, from the Home tab >> you must go to the Conditional Formatting command.
- After that, choose the Highlight Cells Rules feature >> select More Rules…
At this time, a dialog box named New Formatting Rule will appear.
- Now, from that dialog box >> you have to select Use a formula to determine which cells to format.
- Then, you need to write down the following formula in the Format values where this formula is true: box.
=ISTEXT(B5)Here, the ISTEXT function will check whether the value of B5 cell is text or not. If the cell contains text then it will return TRUE otherwise FALSE. One thing, don’t use the absolute referencein the formula. Then, you won’t be able to use the same format in other cells.
- After that, go to the Format menu.
At this time, a dialog box named Format Cells will appear.
- Now, from the Fill option >> you have to choose any of the colors. Here, we have chosen Light Red. Also, you can see the sample instantly. In this case, try to choose any light color. Because the dark color may hide the inputted data. Then, you may need to change the Font Color.
- Then, you must press OK to apply the formation.
- After that, you have to press OK on the New Formatting Rule dialog box. Here, you can see the sample instantly in the Preview box.
As a result, you will see that the B5 cell is colored.
- Similarly, you should select the B5 cell at which you have applied the Conditional Formatting.
- Then, from the Home tab >> you must go to the Conditional Formatting command.
- After that, choose Manage Rules…
At this time, you will see another dialog box named Conditional Formatting Rules Manager.
- Then, select B5:B21 cells in the Applies to box.
- After that, press OK.
Lastly, you will see all the cells which contain text as cell values are highlighted. But, if you notice, then you will see that Excel counts the Hyphen as text. Furthermore, with numbers, if you insert any alphabet or symbol, Excel will also count them as text.
Similar Readings
- How to Use ISLOGICAL Function in Excel (4 Examples)
- Use ISNA function in Excel (4 Examples)
- How to Use ISEVEN Function in Excel (2 Examples)
6. Checking Whether Date Is Correctly Entered or Not
Now, if you want to do any calculation with the dates of the following dataset, 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.
Here, we can perform the check using the ISTEXT function along with the IF function of Excel.
- So, write this formula in the first cell of a new column and then press ENTER.
=IF(ISTEXT(C5),"Not Correctly Entered","Correctly Entered")
- Then, drag the Fill Handle icon.
See, a few of the dates are not correctly entered. In fact, they are text values.
Formula Explanation:
- Firstly, ISTEXT(C5) returns a TRUE if the value in cell C5 is a text, otherwise FALSE.
- Secondly, IF(ISTEXT(C5),”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.
7. Using IF, SUMPRODUCT & ISTEXT Functions
Here, let’s find out whether all the information is valid or not. Mainly, we will check if all 3 columns contain text as their values or not.
Steps:
- Firstly, you have to select a different cell E5 where you want to see the Status.
- Secondly, you should use the corresponding formula in the E5 cell.
=IF(SUMPRODUCT(--ISTEXT(B5:D5))=3, "Valid Info", "Invalid Info")- Thirdly, press ENTER.
Formula Explanation:
- Firstly, ISTEXT(B5:D5) will check whether the values of B5:D5 cells are text or not. If the cell contains text then it will return TRUE otherwise FALSE.
- Output: {TRUE,TRUE,TRUE}.
- Secondly, the Double Hyphen (–) converts the logical values into binary numbers.
- Output: {1,1,1}.
- Thirdly, the SUMPRODUCT function will count all those numbers.
- Output: 3.
- Fourthly, the IF function will check whether the above output is equal to 3 or not. If the logical test is true then it will return “Valid Info” otherwise “Invalid Info”.
- Output: “Valid Info”.
- Then, drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E21.
As a result, you will see all the status.
8. Using ISTEXT as Array Formula in Excel
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.
So, this time we will do it.
- First, write the following formula in the D5 cell.
=IF(ISTEXT(C5:C21),"Not Correctly Entered","Correctly Entered")(Do not forget to press CTRL + SHIFT + ENTER unless you are in Office 365.)
Formula Explanation:
- Actually, IF(ISTEXT(C5:C21),”Not Correctly Entered”,”Correctly Entered”) is a combination of 17 single formulas.
- So, at first, it breaks the formula into 17 single formulas.
- Then, it checks whether each value in the array C5 to C21 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”.
- Then, press ENTER and you will get the result.
How to Use Combined Functions to Identify Cells Containing Certain Text in Excel
In this section, we will use the IF and COUNTIF functions along with Wildcards to identify a cell that contains a specific text. Basically, we want to know who came from Texas in the following dataset.
- So, write this formula in the first cell of a new column and then press ENTER.
=IF(COUNTIF(D5, "*"&$D$23&"*")>0, "Assign to New Office", "Location Unchanged")
Formula Explanation:
- COUNTIF(D5, “*”&$D$23&”*”)—> here, D5 is the cell range and “*”&$D$23&”*” is the criteria.So, the COUNTIF function will check whether the text value is Texas. And if the cell hold Texas as value then it will count the cell.
- Output: 1.
- IF1>0, “Assign to New Office”, “Location Unchanged”)—> here, the IF function will check whether the previous value is greater than 0 or not. If the value is greater than 0 then the IF function will return “Assign to New Office”. Otherwise, it will return “Location Unchanged”.
- Output: Assign to New Office.
- Then, drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E21.
As a result, you will see all the status.
Common Errors with Excel ISTEXT Function
Here, we will explain the common errors of the ISTEXT function, and the reasons for occurring such errors.
- Basically, ISTEXT function itself doesn’t give an error. Actually, it always returns either TRUE or FALSE. Because the ISTEXT function just check is the cell value is a text or anything else.
- However, for this ISTEXT function, you may face other problems with other functions. Like, you are using a formula, and, in that formula, you are using a cell which contains text. So, the ISTEXT function will simply return TRUE and in Boolean it will convert to 1 but you will get error with that formula as your reference cell was text not number.
Practice Section
Now, you can practice the explained method by yourself.
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.
