The Excel ISNONTEXT function lets you check whether the given cell contains non-text values. It returns TRUE or FALSE as the output:
- TRUE when the given cell includes numbers, dates, times, etc.
- FALSE when the given cell contains a text string
For example, suppose you are an HR Manager. Your task is to identify which cell in the following column includes a valid employee ID.
Our formula would be as follows:
=ISNONTEXT(A2)Where the A2 is the cell reference, it holds the first employee ID in the given column.
The results are displayed in the following image,
Here, I am using conditional formatting to highlight the cells. This helps us visually analyse invalid employee names.
Syntax
The ISNONTEXT is a simple function with the following syntax. It helps us validate the contents of the given cell.
=ISNONTEXT(value)Where the value argument needs to be replaced with a cell reference, a direct value, or the result of another formula.
Important Notes:
- The function will treat errors (such as #VALUE!, #NULL!, etc.) as text if returned by the formula.
- The results will be messed up if the cell contains text strings stored in other formats, such as dates and numbers.
How to use the ISNONTEXT Function in Excel
Download the example spreadsheet used to demonstrate the ISNONTEXT function in the upcoming section. Practice is the key to success!
Example 1: ISNONTEXT Function Basics
Suppose you own an e-commerce store and have collected customer feedback through an online form. It was supposed to be a numeric value between 1 and 5.
- 1 means the least satisfied
- 5 means the most satisfied
But as you can see in the following image, a few mistakenly entered their feedback as a text string.
Our task is to identify valid feedback from above table.
Here are the steps:
- Select the cell C2
- Type =ISNONTEXT
- Choose the first option from the popup
- Specify the cell reference B2
(Cell B2 includes the feedback submitted by the first customer) - Close the bracket using )
- Press the Enter key
Our final formula would be as follows:
=ISNONTEXT(B2)Where B2 includes the value to be checked if it is a number or not.
The outputs are displayed in the following image.
Example 2: Count the Cells with Non-Text Strings across the given Array or Range
Assume that you are organizing an event for your team members. You have come up with the five venues. But before finalizing one, you wish to collect feedback from your team members. The final selection will be based only on their preference. You created an online form to collect this feedback.
After everyone submitted their feedback, you imported the data from the online tool. Here’s how it looks:
The feedback should be on a scale of 1 to 5, where 1 means least preferred and 5 means most preferred. Somehow, a few of your colleagues entered their opinions in text format, as shown in the image above.
Now, your task is to count valid feedback. Here, you can combine the SUM and ISTEXT functions with the Double Unary Operator (–) in Excel to find the valid feedback.
Here are the steps,
- Select the cell C13
- Type =SUM
- Choose the first option from the popup
- Type —
- Next, type ISNONTEXT
(As the ISNONTEXT is a built-in function in Excel, you will see the following popup) - Specify the cell reference B2:F11
- Complete the bracket for the ISNONTEXT function using )
- Complete the bracket for the SUM Function using )
- Hit the Enter key
Our final formula would be as follows:
=SUM(--ISNONTEXT(B2:F11))Where,
- B2:F11 is the range that includes employee feedback
- ISTEXT(B2:F11) is the formula applied to each cell in the given array or range to check if it contains a non-text value
- — is the double unary operator which converts Boolean values (TRUE or FALSE) into numbers (1 or 0)
- The SUM function adds all the results obtained by the –ISNONTEXT(B2:F11) formula
The results are displayed in the following image.
Example 3: Highlight the Cells with Non-Text Values across the given Array or Range
We will consider the same scenario as before.
We have imported the data from an online tool, which includes employee feedback on venues selected for a team event. A few employees didn’t rate (between 1 to 5) the corresponding venues. Instead, they directly entered their feedback in text format.
Now, our task is to highlight the cells that include valid feedback.
We will use the Conditional Formatting feature to execute this task. Let’s begin.
- Select the entire range
- Hover over the main menu
- Click the Home tab from the ribbon
- Click the Conditional Formatting icon as shown below
- Choose the New Rule option
- You will see the following popup
- Choose the Use a formula to determine which cells to format option
- Enter the formula =ISMUMBER(B2) as shown below
- Click the Format button
- You will see the following popup
- Choose the desired color
- Click the OK button to close the popup
- You will be redirected to the conditional formatting dialog box as shown below
- Click the OK button
Make sure to specify the first cell in the given range instead of the entire range while you enter the ISNONTEXT formula to highlight the cell.
The results are displayed in the following image.
Takeaway
ISNONTEXT is one of the rarely used functions in Excel. You can use it to identify whether a given cell contains anything other than a text string, such as a number, date, time, etc. The function is pretty straightforward, as it accepts a single argument.
I hope this article taught you all the bells and whistles of the ISNONTEXT function. Please comment below if you are stuck or encounter any particular error while using it. I will answer your questions as soon as possible.
Additional Resources:
- Learn All Excel Information Functions (With Examples)
- ISTEXT Function in Excel
- ISNUMBER Function in Excel
- IFERROR Function in Excel
- IF Function in Excel
- SEARCH Function in Excel
- Guide to Conditional Formatting in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.