Are you familiar with the ISNONTEXT function in Google Sheets? Although not commonly used, this function has some hidden gems that many users have yet to explore. In this article, we will uncover the practical uses of the ISNONTEXT function and how it can enhance your data analysis capabilities in Google Sheets.
The ISNONTEXT function is an info type function that checks whether a value is non-textual. When you use this function, it will either return TRUE or FALSE, depending on whether the value being referenced is text or not. This simple yet powerful function can be incredibly useful in logical tests and even in conjunction with the Query function in Google Sheets.
How to Use the ISNONTEXT Function
The syntax for the ISNONTEXT function is straightforward:
ISNONTEXT(value)
There isn’t much to mention about the arguments in this function. So let’s jump right into some examples to illustrate its practical applications.
Example 1: Checking Textual Values
Let’s say we want to determine if a value is non-textual. We can use the ISNONTEXT function to achieve this. Consider the following formula:
=ISNONTEXT("infoinspired")
In this example, the formula will return the Boolean value FALSE because the text “infoinspired” is a textual value.
Example 2: Checking Numeric Values
Now, let’s see how the ISNONTEXT function handles numeric values. Take a look at this formula:
=ISNONTEXT(105)
In this case, the formula will return the Boolean value TRUE because the number 105 is a non-textual value.
Practical Uses of the ISNONTEXT Function in Google Sheets
Now that we understand how the ISNONTEXT function works, let’s explore its practical applications in Google Sheets, particularly in IF logical tests and Query functions.
1. ISNONTEXT in IF Logical Tests
One useful application of the ISNONTEXT function is in IF logical tests. Let’s consider a scenario where you have a dataset, and you want to find the product of two columns, but you encounter text values in one of the columns. Here’s how you can solve this using the ISNONTEXT function:
First, use the following array formula to perform the multiplication:
=ArrayFormula(D2:D8*E2:E8)
This formula will produce two #VALUE! errors in cells F4 and F7 due to the presence of text values in column D.
To remove these errors, wrap the previous formula with an IFERROR function:
=IFERROR(ArrayFormula(D2:D8*E2:E8))
With this formula, the errors will be replaced with blank cells. However, if you want to replace the errors with a specific value, such as zero, use the following formula:
=IFERROR(ArrayFormula(D2:D8*E2:E8),0)
Now, let’s say you want to keep the text values instead of replacing them with blank cells. Here’s where the ISNONTEXT function comes into play:
=ArrayFormula(IF(ISNONTEXT(D2:D8),D2:D8*E2:E8,D2:D8))
With this formula, any text values in column D will be preserved, while the multiplication will be performed for the numeric values. The result will be displayed in column F, as shown in the image below:
2. The Use of the Function ISNONTEXT in Google Sheets Query
Mixed content columns can often pose a challenge when using the Query function in Google Sheets. In instances where you have a column with a mixed data type, such as text and numbers, the minority data types may be considered null values, leading to imperfect results.
Typically, users resort to using the TO_TEXT function to convert the entire column to text values. However, this approach may not be ideal in all situations since it can make calculations involving dates or numbers cumbersome. Fortunately, the ISNONTEXT function can provide an alternative solution.
Consider the following example:
=QUERY({A2:E8})
To incorporate the ISNONTEXT function into the Query formula, use the following array formula:
=ArrayFormula(QUERY({A2:C8,IF(ISNONTEXT(D2:D8),D2:D8,0),E2:F8}))
This formula replaces the text values in the mixed content column D with the value 0. If you wish to filter out the numbers and retain the text values, modify the formula as follows:
=ArrayFormula(QUERY({A2:C8,IF(ISNONTEXT(D2:D8),"-",D2:D8),E2:F8}))
By employing the ISNONTEXT function in combination with the Query function, you can manipulate the mixed content columns effectively in Google Sheets.
In conclusion, the ISNONTEXT function is a valuable tool in Google Sheets that can significantly enhance your data analysis capabilities. Its practical uses are particularly evident in IF logical tests and Query functions. By leveraging this function, you can take your data analysis skills to the next level and unlock new possibilities in Google Sheets.
So why wait? Start exploring the power of the ISNONTEXT function in Google Sheets today!
For more helpful tips and tricks, visit Crawlan.com.
