SharePoint: ISNONTEXT function

Following the “IS” functions theme, we have the ISNONTEXT function. This function doesn’t check for errors, but if the value is of a specific type. In this case, if the value is “Text” or not. As with all the other “IS” functions, this one will return only “Yes” or “No”.

You can also use this function’s alter ego, the “ISTEXT” function.

Usage

It follows a simple pattern.

Value to check

To demonstrate how this works, let’s look at the following table:

The “text” column is defined by “single line of text” where a number column is a number.

Let’s create a new column “calculated,” defined as follows:

=ISNONTEXT([text])

To test it correctly, the first row has the text value as empty while the second has spaces so that we can see the difference.

Let’s check the result:

Since the column is defined with the Type “single line of text”, even if you store an apparent number, it will be stored as text, so the function will return “No”, meaning that it is indeed a text. The only return as “Yes” is if the “single line of text” column is empty, which makes sense.

Now let’s do the same with the number column.

All values are as “TRUE” since it’s defined as a “number” column, meaning that regardless of the value (even empty), it will never be a text.

Limitations

It’s not a limitation but something to know and be aware of. If the column is empty, the “ISNONTEXT” function will return “No”, as seen above. This happens even if the column is defined as “single line of text”. Besides empty, the function will always return “Yes”.

Recommendations:

Some things to take into consideration while using this function.

Don’t use this function to check whether the column is empty.

Although the result may be the same, if the result is empty, it returns “Yes” otherwise returns “No”; you may find edge cases where they differ. And you have the “ISBLANK” function to do this. It will confuse people checking the formula if they see this function instead of the “ISBLANK” function.

“Hidden” Validation Columns

Since SharePoint works with columns, you can create hidden “validation” columns with the functions validating the data. After that, you can expose to the user the “message” columns with helpful error messages or actions they can take to solve the problem.

Always deal with errors.

Leaving them for the user to see is horrible, but also you can bring trouble to your code. If you don’t validate a column, it can be used after in other calculations, and the error will cascade. You can have an error in one column, and the issue could be in different columns, so deal with errors where they happen.

Don’t nest it.

There’s no real reason to do it, but if you find yourself in a situation where you have nested “ISNONTEXT” functions in a formula, then the result will always be TRUE since the return value will always be a “Yes”/”No” result. If you see nested “ISNONTEXT” functions, then something is not entirely correct.