ISNONTEXT Function

Updated on April 29, 2024
Article bySuganthi S
Reviewed byDheeraj Vaidya, CFA, FRM

What Is ISNONTEXT Function?

The ISNONTEXT function is a built-in Excel Information function. It accepts a value and returns TRUE if the specified value is not text data. Otherwise, the function output is FALSE.

Users can utilize the ISNONTEXT function while validating and conditionally formatting cells based on non-text values. The function is also useful for analyzing Excel data based on non-text and text values.

For example, the dataset below lists values and their types.

ISNONTEXT function Intro

The aim is to check each value in column A if it is a non-text value and display the output as a logical TRUE or FALSE in the corresponding cells in column C.

Then, considering the ISNONTEXT function explained above, we can apply the ISNONTEXT() in each target cell to obtain the required output.

ISNONTEXT function Intro - Output

In the above ISNONTEXT formula example, the ISNONTEXT() in each target cell accepts the reference to the corresponding column A cell containing the value to check.

Next, the ISNONTEXT function assesses whether the specified column A cell value is not a text. If the specified value is not a text, the function output is the logical TRUE. Otherwise, the function returns the logical FALSE.

Please note that the argument value in the ISNONTEXT() in cell C6 is a logical value, FALSE. The function considers it a non-text value and returns TRUE as the output. On the other hand, had we supplied the cell A6 value in double quotes to the function, it would have considered it a text value and returned FALSE as the output.

Key Takeaways

  • The ISNONTEXT function accepts a value and returns TRUE if the specified value is not a text value. Otherwise, the function output will be FALSE.
  • Users can utilize the ISNONTEXT() for locating cells containing non-text values in massive datasets, which must contain only text values. The function thus helps update the datasets with the data of the correct type.
  • The ISNONTEXT function in Excel accepts one compulsory argument, value.
  • While the ISNONTEXT Excel function works well on its own, combining it with other inbuilt functions, such as IF and SUMPRODUCT, can help achieve practical outcomes.

Syntax

The ISNONTEXT function syntax is the following:

ISNONTEXT function

Where,

  • value: The value we aim to check if it is not a text value using the ISNONTEXT function formula.

The ISNONTEXT function argument is mandatory, and it can be a valid value in the correct data format, cell reference, array, or formula.

Furthermore, if the function argument value is a reference to an empty cell, the function considers the input a non-text value and returns TRUE as the output. But if the value supplied to the function is an empty string, the function considers it text and returns FALSE as the output.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use ISNONTEXT Function In Excel?

While we can use the ISNONTEXT function VBA, the following two methods are more straightforward ways to use ISNONTEXT().

  1. Access the function from the Excel ribbon.
  2. Enter the function into the worksheet manually.

Method #1 – Access The Function From The Excel Ribbon

Select a cell to display the output – The Formulas tab – The More Functions down arrow – The Information function group right arrow – ISNONTEXT function.

ISNONTEXT Function - Method 1

The Function Arguments window will appear. Here, we can enter the function argument value in the Value field according to the ISNONTEXT function definition and syntax discussed in the previous sections.

ISNONTEXT Function - Method 1 - Function Argument

Next, click OK in the Function Arguments window to obtain the ISNONTEXT() output as a logical value TRUE or FALSE in the target cell.

Method #2 – Enter The Function Into The Worksheet Manually

  1. Choose a cell to show the output.
  2. Type =ISNONTEXT( in the cell. [ Alternatively, enter =IS or =ISN and double-click the ISNONTEXT Excel function from the listed suggestions to choose it.]
  3. Enter the argument as a value, reference, or formula and close the brackets.
  4. Press Enter to obtain the ISNONTEXT() output in the chosen cell.

Examples

The following ISNONTEXT function examples will help utilize the function more effectively.

Example #1 – Using A Text Value

The source dataset contains employee names in column A.

ISNONTEXT function - Example 1

The aim is to check if each employee name is a non-text value and display the output as a logical TRUE or FALSE in the corresponding column B target cells.

While we can use ISNONTEXT function VBA to achieve the required output, we shall see the more straightforward methods.

Step 1: Choose cell B2, enter the ISNONTEXT function, and press Enter.

=ISNONTEXT(A2)

ISNONTEXT Function - Example 1 - Step 1

The ISNONTEXT() accepts the reference to the column A cell A2 value and returns FALSE as the output since the specified employee name is a text value.

Please ensure the input value’s data type is correct. Otherwise, when supplying the specific cell reference as an argument to the function, the function might return an incorrect output.

We can also directly supply the specific employee name in double quotes as the argument value to the ISNONTEXT() to obtain the same output. Please note that we supply the value in double quotations because the employee name is indeed a text value.

=ISNONTEXT(“Allen Reed”)

ISNONTEXT Function - Example 1 - Step 1 - Allen

On the other hand, supplying the specific employee name directly to the ISNONTEXT() but without double quotes will make the function consider the value as non-text. So, the function will return TRUE as the output.

=ISNONTEXT(Allen Reed)

ISNONTEXT Function - Example 1 - Step 1 - no quotes

Thus, when supplying a value directly as an argument value to the function, we must ensure to supply it correctly according to the data type to achieve the correct output.

[Alternatively, choose the target cell and select Formulas More Functions Information ISNONTEXT.

ISNONTEXT Function - Example 1 - alt - Formulas

The Function Arguments window will open. Enter the reference to the cell that has the data we aim to check.

ISNONTEXT Function - Example 1 - alt - Argument

Finally, click OK to view the ISNONTEXT() output in the chosen cell.]

Step 2: Using the Excel fill handle, enter the formula in cell B3.

enter the formula in cell B3

Thus, the output in the target cells suggests that the employee names are text values.

Example #2 – Using A Date Value

The dataset below contains a list of products and their order dates in Excel-recognized date formats.

ISNONTEXT Function - Example 2

The task is to check if the order dates are non-text values and display the output as logical TRUE or FALSE in the column C target cells.

Then, we can use the ISNONTEXT function in the target cells to achieve the required information.

Step 1: Choose cell C2, enter the ISNONTEXT(), and press Enter.

=ISNONTEXT(B2)

ISNONTEXT Function - Example 2 - Step 1

The function accepts the reference to the column B cell B2 date value and returns TRUE as the output since the date value is non-text data.

Likewise, we can supply the specific date value directly as input to the function to obtain the same output.

=ISNONTEXT(15-Sep-2023)

ISNONTEXT Function - Example 2 - Step 1 - specific date

However, supplying the date value within double quotes as input to the function will make the function consider the value as a text, and it will return FALSE as the output.

=ISNONTEXT(“15-Sep-2023”)

ISNONTEXT Function - Example 2 - Step 1 - Quotes

Step 2: Using the fill handle, enter the ISNONTEXT() in the remaining target cells.

Using the fill handle

The output shows that if the input value is a date in an Excel-recognized date format, the function will return TRUE as a date is a non-text value.

Furthermore, consider we add a new product and its order date in row 6, as depicted below. Please note that the order date is not in a valid date format.

ISNONTEXT Function - Example 2 - Date format

Next, we select cell C6, enter the ISNONTEXT function, and press Enter.

=ISNONTEXT(B6)

we select cell C6

The supplied cell B6 date value as input to the ISNONTEXT() is not in an Excel-recognized date format. Thus, the function considers the input data as a text value, leading to it returning FALSE as the output.

Example #3 – Using A Formula That Returns A Non-Text Value

The dataset below shows the formulas applied in column B cells, with their return values being non-text.

Example 3

Here is how we can confirm the formulas’ return values are non-text and display the output in the column C target cells.

Step 1: Choose cell C2, enter the ISNONTEXT function-based formula, and press Enter.

=IF(ISNONTEXT(B2),”Yes”,”No”)

Example 3 - Step 1

Step 2: Using the fill handle, apply the formula in the remaining target cells.

apply the formula in the remaining target cells

The ISNONTEXT() is the Excel IF function condition. It checks if the value in the cell, supplied as the function argument value, is a non-text value. Since the input value is a formula output, which is not a text value, the ISNONTEXT() returns TRUE as the output.

Thus, the IF() condition holds, leading to it returning the TRUE value, Yes, as the output.

Important Things To Note

  • Ensure the ISNONTEXT function argument value is a valid value of the correct data type, a valid cell reference, or an error-free formula. Otherwise, the function output may be incorrect.
  • The ISNONTEXT Excel function assesses data. So, it does not return an error value, such as #N/A and #DIV/0! Excel Error, as output.
  • The ISNONTEXT function in Excel considers an empty cell as a non-text value.

Frequently Asked Questions (FAQs)

Choose cell B8
1. Can you use ISNONTEXT function in multiple cells without VBA?

You can use ISNONTEXT function in multiple cells without VBA, as explained below with an example.
The following dataset lists values in column A.

The following dataset lists values in column A

The task is to check if the column A values are non-text and display the output in the corresponding column B target cells. Next, we must calculate the total number of non-text values in column A and show the count in cell B8.

Then, here is how to use the ISNONTEXT function in multiple cells without VBA and obtain the required outputs.

Step 1: Select the range B2:B5 and enter the ISNONTEXT().

=ISNONTEXT(A2:A5)

Select the range B2 to B5

Step 2: Press Ctrl + Shift + Enter to implement the function as an array formula.

Press Ctrl + Shift + Enter

Thus, applying the ISNONTEXT() as an array formula implements the function in multiple cells in one go. The ISNONTEXT() in each target cell accepts the corresponding column A cell reference as the input. It returns TRUE if the specified value is non-text, else FALSE.

Step 3: Choose cell B8, enter the SUMPRODUCT() containing the ISNONTEXT(), and press Enter.

=SUMPRODUCT(–(ISNONTEXT(A2:A5)))

Choose cell B8

First, the ISNONTEXT(), with a range as the argument value, returns an array of TRUE and FALSE values, {TRUE;TRUE;TRUE;FALSE}. The TRUEs and FALSEs are based on whether the corresponding column A cell values are non-text or text values.
Next, the double unary operator translates the array of TRUE and FALSE values into an array of ones and zeros, {1;1;1;0}. Finally, the SUMPRODUCT() adds the ones and zeros in the resulting array to return the sum value of 3 as the required count of non-text values in column A.

2. What are the common mistakes when using ISNONTEXT?

The common mistakes when using ISNONTEXT Excel function are as follows:

• We may misinterpret the ISNONTEXT() output and consider the function will return TRUE for text values and FALSE for non-text values.
• We may use ISTEXT() instead of the ISNONTEXT() and interpret the function output incorrectly.

3. Why isn’t my ISNONTEXT working?

Your ISNONTEXT isn’t working because of the following reasons:

• The value supplied as input to the ISNONTEXT() contains hidden characters or additional space characters.
• The value supplied as input to the ISNONTEXT() is of the incorrect data type.
• The cell reference supplied as input to the ISNONTEXT() is incorrect or invalid.
• The function contains syntax or typo errors.

Download Template

This article must be helpful to understand the ISNONTEXT Function, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to What Is ISNONTEXT Function. Here we learn the ISNONTEXT function syntax & how to use it in Excel with examples & points to remember. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *