ISNONTEXT Function
Checks if a cell contains text or not
Checks if a cell contains text or not
The ISNONTEXT Function is categorized under Information functions. The function helps to check if a cell contains text or not (is anything typed into a cell?). It will return TRUE if the value is text and if not, a FALSE value. For example, if the given value is a number, date, or time, ISNONTEXT will return a FALSE.
As a financial analyst, when dealing with large amounts of data, the function helps in testing if a given value is a text or not. It is also useful in some scenarios. For example, you performed AUTOSUM for a large amount of data. When the result gives you an error, we can check if there is text in between (or not).
=ISNONTEXT(value)
The ISNONTEXT function uses the following arguments:
The ISNONTEXT function will return a logical value, which is TRUE or FALSE.
To understand the uses of the ISNONTEXT function, let’s consider few examples:
Let’s first understand how the function behaves when given the following set of data:
Data | Formula given | Result | Remarks |
---|---|---|---|
TRUE | =ISNONTEXT(TRUE) | FALSE | The value is a text, so the function returned FALSE. |
=ISNONTEXT( ) | TRUE | The function returned TRUE for blank values. | |
“CFA” | =ISNONTEXT(“CFA”) | TRUE | Although the expression is text as it was in inverted commas, the function returned TRUE. |
#NAME? | =ISNONTEXT(#NAME?) | TRUE | The function returned TRUE for formula errors. |
32.00 | =ISNONTEXT(32) | TRUE | The function returned TRUE as it is a number. |
Suppose we imported data from a PDF file. The data is a set of numbers from 1-20,500, which we imported into column A, as shown below:
As you can see above, the data was zoomed to selection so it’s not understandable. We wish to do a summation of the numbers. When we did that, we got a sum that was different from what we are expecting. While going through the data, we noticed two things:
First, we need to remove the rows that contain text. To do it, we can use ISNONTEXT formula (to see is anything typed into a cell).
In column B, we will enter the formula shown below:
Now we will drag the formula until the end to cover all rows.
Once it is done, we will apply a filter. For the filter, we need to go to the DATA tab and click Filter, as shown below:
After selecting the filters, we can click on the downward arrow and click only on FALSE to get rows with FALSE on them.
The shortcut is ALT+A+T.
Now we see the culprits, which are the rows that contain text. We can now delete them and crosscheck with the original data. Here, there are 26 rows with text, meaning one row was imported as text. We can even correct the error and format it to a number.
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!