You may wonder why this tutorial about ISNUMBER is required as this function is so simple to use. Just wait! I will tell you how ISNUMBER can be a blessing in disguise on certain occasions. We can learn here not only the use of ISNUMBER formula in Google Sheets but also its practical use.
Syntax:
ISNUMBER(value)
Purpose:
The purpose of ISNUMBER function is to check whether a value is a number. If it’s a number, it returns TRUE else FALSE.
Practical Use of ISNUMBER Formula in Google Sheets
Normal use of ISNUMBER function:
In the above examples, only the value in Cell A1 is a number. So ISNUMBER returns TRUE. Now please take a note of the below two variations of ISNUMBER.
= – – isnumber(A2)
=isnumber(A2)*1
Either of the above two variations of ISNUMBER formula can return 1 instead of TRUE. If ISNUMBER returns FALSE, the above variations would return 0.
Why is this variation relevant here?
To make you understand this, let me take you to a practical use of Google Sheets ISNUMBER function.
In the below screenshot, you can see a FIND formula. It’s a case-sensitive formula and similar to Google Sheets Exact function.
You can use this formula to match two cells for identical or case-sensitive texts.
If there is a match, the FIND formula returns 1, else it returns ‘#VALUE!’. On the contrary, the Exact function returns TRUE or FALSE.
So what is the point? What I am trying to say is ISNUMBER with FIND can act as an EXACT function. That means the below formula would return FALSE instead of ‘#VALUE!’.
=isnumber(find(A1,B1))
Again the below variation can return 1 or 0.
=isnumber(find(A1,B1))*1
Why is this important?
You can use this combination as an alternative to Exact Function in Google Sheets.
Array functions won’t normally accept Exact function. There you can use the ISNUMBER_FIND combo or FIND alone for an exact match. Our tutorial on case-sensitive SUMIF can shed some light on this.
Also, I’ve used an ISNUMBER FIND combo in a case-sensitive COUNTIF operation in Google Sheets.
Other Use of ISNUMBER
The ISNUNBER function is also useful when using with a combined IF logical test. How?
See Cell D4 and D5.
The IF formula checks value in B and returns “Good Score” when the score is above 100. When the score is below 100 or the team “Not Played”, it should return “Bad Score”. But see the result is wrong in D4 and D5.
An ISNUMBER can solve this issue.
Now see the result in D4 and D5. Similarly, you can make the use of Google Sheets ISNUMBER Function in several cases. That’s all for now. Enjoy!