Many times, while working with data in Excel, you may as well feel a need to find the cell which is not in a text format cell in excel. In that case, the ISNONTEXT function in Excel becomes useful, to find non-text cell in excel.
In this tutorial, we would learn how to use the ISNONTEXT formula in excel and see how it works with the help of some classic examples.
- When to Use ISNONTEXT Function in Excel
- Syntax and Arguments
- Points To Remember About ISNONTEXT Function
- How to Change Cell Format to Text
- Examples to Learn About ISNONTEXT Formula
Let’s deep dive. Here we go 😎
When to Use ISNONTEXT Function in Excel
The expression ISNONTEXT represents “Is the cell, a non-text value”. The excel ISNONTEXT formula returns a logical TRUE or FALSE based on the condition if the cell format is Non-text (i.e. a number, currency, accounting, date, time, percentage, fraction, scientific, etc.) or a text.
This function belongs to the Information functions group of excel.
The excel ISNONTEXT function works exactly opposite to the ISTEXT excel formula.
Syntax and Arguments
The below points explain the required function argument of the ISNONTEXT function.
- value – In this argument specify the a cell reference or range of cells for which we want to check if the cell format is nontext (not the text format).
Points To Remember About ISNONTEXT Function
The following points should be kept in mind before using the ISNONTEXT formula:
- Not only the text strings but the numbers, dates, or times formatted as the text will return a logical FALSE when passed as ISNONTEXT Function argument.
- The ISNONTEXT function never returns any error, whatever be the function argument.
- An array is a collection of similar elements. Whenever we pass a range of multiple cells, the ISNONTEXT function becomes an array formula. The function will return an array of elements containing Logical TRUE/FALSE only.
- The formula returns a Logical TRUE for the blank cells. Therefore, it considers the blank cell to be a non-text value. (see example 1 below)
How to Change Cell Format to Text
Before using the ISNONTEXT function, let us gain some knowledge about changing the cell format of the cells in Excel.
Let’s say, we have a date, time, text string, and a numerical value in the cells A1, A2, A3, A4 respectively.
To change the format of the non-text cell values to a text format, use the following steps:
- Select the range (A1:A4) and hit Ctrl + 1. The format cells dialog box will appear.
- Choose the category as ‘Text’ format. The ‘Sample’ box shows the preview of the text result.
- Finally click on the ‘OK’ button.
As a result, excel converts all the non-text values to text values.
The default left alignment indicates the text format. The green arrow at the top of A4 cell points the number formatted as text
Examples to Learn About ISNONTEXT Formula
In this section of the blog, we will do some practical examples to learn the usage of the ISNONTEXT Formula of Excel.
Ex. 1 – Simplest Example for ISNONTEXT Formula
The below image from excel shows a list of different types of values in column A.
To check if the cell value is in text format or not, simply type the following formula in cell C2.
As a result, the formula returns FALSE. Use the fill handle tool to copy the formula to the remaining cells.
Explanation – The value contained in cell A2 is a text string. A text string has the text format so the formula returned a Logical FALSE for the text formatted cell.
The cells A3, A6, A8, and A10 contain the values formatted as text so they also returns FALSE when checked for ISNONTEXT formula.
On the other hand, the cells A4, A5, and A7 contain the values that have formats other than text format. Therefore the ISNONTEXT Formula returns a logical TRUE for them.
The blank cell A11 does not contain any text value, therefore the function returns a TRUE for it.
Ex. 2 ISTEXT vs ISNONTEXT Formula in Excel
The ISTEXT Formula works completely opposite to the ISNONTEXT Function. It checks if the reference cell contains is a text formatted value. If it is, then the function returns TRUE, otherwise FALSE.
See the comparison between ISTEXT and ISNONTEXT function in excel below:
Explanation – The value contained in Cell A2 is a text string. So for the Text formatted cell, the ISTEXT formula returns a logical TRUE
The cells A3, A6, A8, and A10 contain the values formatted as text. When we use the ISTEXT formula, they return the logical TRUE because the values have text format.
The cells A4, A5, A7 do not have text format. Therefore the ISTEXT function returns a logical FALSE for them.
A11is a blank cell, the formula returns a logical FALSE because the blank cell does not have text.
Ex. 3 – Applying Data Validation to Enter Only Non-Text Values
Let us say there is a maths and science quiz in a school. We want to make a spreadsheet such that, the marks scored in the quiz must not be in words (e.g. Three or Four).
Below are the columns for entering the marks.
We do not want that, at the time of the quiz, the one who notes down the marks, write the marks in words. We do not want the marks to be written in Text format.
To achieve this, we can use the ISNONTEXT formula along with data validation in excel.
Below are the steps to apply data validation for the same. Follow them to get a proper understanding of the topic.
- Select the range of cells (B2:D5). Go to Data Tab > Data tools Group > Data Validation Button. Choose Data Validation option from the drop down menu.
- From the Data Validation dialog box, choose custom from the drop down menu.
- Enter the below ISNONTEXT formula in the provided box.
So now, only those values will be valid for the range B2:D5 that make the ISNONTEXT formula to return a logical TRUE (will not accept text values)
- To display a message, go to input message tab and enter the message you want to get displayed. Here we put the message “Enter the marks in Numerical Format i.e 4”
- Now as soon as you select the cell, you will see this message as a reminder for data validation.
- And even if, someone fills the marks in words, for example ‘Three’, the excel will pop out an error message box and not accept the textual value at any cost.
Do you wonder how did I split the cell A1 diagonally (from top left corner to bottom right corner)? Here is the trick 😉
This brings us to the end of the ISNONTEXT Function blog.
Thank you for reading 🙂