What Is ISNONTEXT Function In Google Sheets?
The ISNONTEXT function in Google Sheets checks if the selected value is text or not. It returns TRUE, if the specified value is not a text and FALSE, if the value is a text. The Google Sheets ISNONTEXT function finds it use when we must conditionally format cells based on non-text values, for analyzing large datasets that include a mix of values such as, numbers, dates or other non-text values.
For example, the dataset given below consists of values to check if they are not text, using the ISNONTEXT function.
Select cell B2, enter the formula =ISNONTEXT(A2), press “Enter” and drag the formula from cell B2 to B3 using the fill handle, as shown below.
The output is shown above. For the cell value in A2 the result is FALSE, because it is not a non-text instead it is a text. However, the cell value of A3 is a non-text or a numeric value, so the result is TRUE, as it satisfies the functions requirement.
Key Takeaways
- The ISNONTEXT function in Google Sheets 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 helps facilitating efficient decision-making processes based on specific criteria rather than sifting through vast amounts of irrelevant information.
- Unlike some functions that returns #NAME! or #N/A! errors, the ISNONTEXT function solely evaluates data and does not generate any errors.
- We can use the ISNONTEXT with other inbuilt functions, such as IF and SUMPRODUCT.
Syntax
The syntax of the ISNONTEXT Formula in Google Sheets is,
The one and only mandatory argument of the ISNONTEXT Formula in Google Sheets is,
- value: It is the cell value to check if it is not a text, which must be in the correct data format, cell reference, array, or formula.
How To Use ISNONTEXT Function In Google Sheets?
We can use the ISNONTEXT Google Sheets Function in two ways, as follows:
- Access from the Google Sheets ribbon.
- Enter the formula in the worksheet manually.
Method #1 – Access from the Google Sheets ribbon
Step 1: Choose an empty cell for the output à select the “Insert” tab – click the “Function” option right arrow – click the “Info” option right arrow – select the “ISNONTEXT” function, as shown below.
Step 2: The “ISNONTEXT” formulaappears, as shown below. Enter the argument as cell reference.
Method #2 – Enter the formula in the worksheet manually
Step 1: Select an empty cell for the output.
Step 2: Type =ISNONTEXT( in the cell, as shown below. [Alternatively, type =IS or =ISNON and double-click the ISNONTEXT function from the Google Sheets suggestions.]
Step 3: Enter the arguments as cell values or cell references and close the brackets.
Step 4: Press Enter to view the outcome.
Examples
Let us consider some of the ISNONTEXT function in Google Sheets examples.
Example #1 – Detect Text Values from a list of financial products.
Let us detect the text values using the ISTEXT function, and the use the ISNONTEXT function to detect the non-text values. The dataset below consists of financial products and two columns where we will insert the two functions and find the results.
The steps to Detect Text and Non-Text Values using the ISTEXT and the ISNONTEXT functions, respectively, are as follows:
Step 1: To detect text values, select cell A2. Enter the formula =ISTEXT(B2) and press “Enter”, as shown below.
Step 2: Drag the formula from cell A2 to A9 using the fill handle. It is to detect the text values, as shown below.
Step 3: Next, to detect non-text values. Select cell C2, enter the formula =ISNONTEXT(B2) and press “Enter”, as shown below.
Step 4: Drag the formula from cell C2 to C9 using the fill handle. It is0 to detect the non-text values, as shown below.
Output Observation: Column A displays the results of the detected text values and Column C displays the results of the non-text values. We notice that the results are the opposite to each other, because ISTEXT is the reverse function of the ISNONTEXT.
Example #2 – Detect Non-Text Values from sales figures
The dataset given below has the smartphones Jan and Feb Sales data with their individual and cumulative total which is calculated using the SUM function. We will Detect Non-Text Values using the array formula method.
The steps to Detect Non-Text Values using the array formula method are,
Step 1: First, in cells F1:I8, let us create a result table with the same headers and copy only the formatting and not the values, as shown below.
Step 2: Next, in cell F1, enter the formula =ISNONTEXT(A1:D8) andpress the “Ctrl+Shift+Enter” keys, because we are executing as an array formula.
Now the formula looks like this =ArrayFormula(ISBLANK(B2:D7))
Step 3: Press “Enter”.
The output is shown above. All the sales value cells have returned the results as TRUE, that indicates the cells are non-text, including the total calculated using the SUM formula. Rest all are FALSE as they are textual, including the headers and the smartphone names.
Example #3 – Using A Formula That Returns A Non- Text for loans availed
The data shows the customers who have availed loans and those who have not. We will use A Formula That Returns A Non-Text Value for loans availed using the IF function.
The steps to Detect Text Values using the ISNONTEXT and the IF functions are,
Step 1: Select cell C2 and enter the formula =IF(ISNONTEXT(B2)=True,”12345″,””)
Step 2: Press “Enter” and drag the formula from cell C2 to C7 using the fill handle, to get the following output.
Output Observation: If the cell values are non-textual, then, the result is TRUE. When the result is TRUE, according to the function, it is a not a text. Hence, the output clearly displays the non-text cells, or the loan availed customers i.e. cells B2, B4, B5 and B7, as 12345 (a non-text value) and the text cells, i.e. cells B3 and B6, as blank or empty or the loan not availed customers.
Example #4
Let us check different data type value in given in the below table for textual or non-textual valuesusing the ISNONTEXT() formula in Google Sheets.
The steps to find the text and the non-text values using the ISNONTEXT function are as follows:
Step 1: Select cell C2 and enter the formula =ISNONTEXT(A2), as shown below.
Step 2: Press “Enter”. We notice that we get the Suggested autofill option is given by google sheets.
- We can opt to check-tick the autofill or use the fill handle method.
- If we do not want to select the autofill, then, we can press the “x” mark to close the suggestion.
Step 3: Drag the formula from cell C2 to C7 using the fill handle, to get the final output, as shown below.
Output Observations: By now we are aware of the working of the ISNONTEXT function.
- The alpha-numeric value in cell A6 and the special characters value in cell A7 are considered as text and the result is FALSE.
- The function, by default, considers the blank or empty cells as non-text values. That is the reason the result of cell A5 is TRUE.
Important Things To Note
- The ISNONTEXT Google Sheets function assesses data. So, it does not return an error value, such as #N/A, #DIV/0!, etc.
- If a cell value is an error or the result of a formula is an error like #VALUE! or #DIV/0!, the function will return FALSE, assuming it as a text value. Therefore, we must be careful when using the formula to avoid misleading outcomes.
- The function considers an empty cell as a non-text value.
- If the cell value entered in the formula is within double-quotes, then, regardless of the value, the function considers it as text.
Frequently Asked Questions (FAQs)
The ISNONTEXT isn’t working because of the following reasons:
The argument valuescontain hidden characters or additional space characters or is of incorrect data type.
We executed a cell range normally instead of executing as an array formula.
We often forget in which category a function falls, here, the “ISNONTEXT” function. Then, we can insert the function as follows:
Choose an empty cell – select the “Insert” tab – click the “Function” option right arrow – click the “All” option right arrow -select the “ISNONTEXT” function, as shown below.
However, as always, entering the function manually is the best way to avoid confusion.
Alternatively, we can find the Functions icon to insert the ISNONTEXT function in Google Sheets by following the path shown below.
Choose an empty cell – click the “More” option represented by the three vertical dots at the end of the toolbar, as shown below.
A list of icons appears when we click the “More” option. Here, click the “Functions” icon, as shown below.
Here, click the “Functions” option – click the “All” option right arrow – select the “ISNONTEXT” function, as shown below.
Download Template
This article must be helpful to understand the ISNONTEXT Function in Google Sheets, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What Is ISNONTEXT Function In Google Sheets. We learn syntax, how to use, to check for non text values, examples, working template. You can learn more from the following articles.
Leave a Reply