Over 1.8 million professionals use CFI to learn accounting, financial analysis, modeling and more. Start with a free account to explore 20+ always-free courses and hundreds of finance templates and cheat sheets.
The ISNONTEXT Function is categorized under Excel 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, FALSE. 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 other 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).
The ISNONTEXT function uses the following arguments:
Value (required argument) – This is the expression or value that needs to be tested. It is generally provided as a cell address.
The ISNONTEXT function will return a logical value, which is TRUE or FALSE.
How to use the ISNONTEXT Function in Excel?
To understand the uses of the ISNONTEXT function, let’s consider a few examples:
Let’s first understand how the function behaves when given the following set of data:
The value is a text, so the function returned FALSE.
The function returned TRUE for blank values.
Although the expression is text as it was in inverted commas, the function returned TRUE.
The function returned TRUE for formula errors.
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, the number of rows filled with data is 20,529, whereas the data imported should show 20,500 rows filled.
While going through the data, we realized that there are a few rows that contain text.
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.
A few notes about the ISNONTEXT Function
The ISNONTEXT function is part of IS functions that return the logical values TRUE or FALSE.
It doesn’t return an error such as #NAME!, #N/A!, etc., as it just evaluates data.
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 CFI resources:
Take your learning and productivity to the next level with our Premium Templates.
Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.
Already have a Self-Study or Full-Immersion membership? Log in
Access Exclusive Templates
Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.