You are reading the article Excel Test If A Range Contains Text, Numbers Or Is Empty updated in September 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Excel Test If A Range Contains Text, Numbers Or Is Empty
I received an email from Bill this week asking how he can check if a range of cells contains text or numbers, as opposed to being empty.
We can use the ISTEXT function to test for text, and ISNUMBER function to test for numbers, but these only work on one cell at a time.
Here’s the syntax:
=ISTEXT(value)
=ISNUMBER(value)
Where ‘value’ is the reference of the cell you want to test.
We can see them in action in the image below:
But Bill wants to test the whole range, A4:A10, to see if any cells contain text or numbers.
To check a range we can combine the ISTEXT and ISNUMBER functions with SUMPRODUCT like this:
So, how does this Beauty work?
Although this isn’t strictly an array formula, in that you don’t have to enter it with CTRL+SHIFT+ENTER, the SUMPRODUCT function behaves just like an array formula.
Let’s look at how it evaluates:
Step 1 – ISTEXT returns an array of TRUE/FALSE for each cell in the range A4:A10 like so:
Step 2 – ISNUMBER also returns an array of TRUE/FALSE for each cell in the range A4:A10 like so:
Step 3 – Now, since TRUE/FALSE have equivalent number values of 1 for TRUE, and 0 for FALSE when we apply the + to the two arrays inside the SUMPRODUCT function it converts them to their numeric equivalent like so:
Step 4 – SUMPRODUCT then adds the 1’s and 0’s together:
Step 5 – it tests whether the result of the SUMPRODUCT formula is greater than 0. If it is it returns TRUE, if not FALSE.
Array Formula Option
Of course we could achieve the same result from this array formula:
Although this is slightly shorter to write, remember because it’s an array formula we need to enter it with CTRL+SHIFT+ENTER.
Return a Different Message
If you want to return a ‘Yes’ or ‘No’, as opposed to the TRUE/FALSE outcomes, we can do this with an IF function like so:
Test for Numbers Only
Note the difference in this formula is the *1 after the ISNUMBER test. This is to coerce the TRUE/FALSE values into their numeric equivalents of 1 and zero.
We didn’t have to do this in the original formula because the + in the SUMPRODUCT function did that for us:
Another way to coerce TRUE/FALSE values is with the double unary like so:
Test for Text Only
Or with the double unary:
Test if a Range is Empty
We can use the ISBLANK function to test if a cell is empty, but like ISTEXT and ISNUMBER, it only works on one cell at a time. The solution is to use SUMPRODUCT to test a range of cells and then compare the result to the number of cells in the range like so:
=SUMPRODUCT(--ISBLANK(A4:A10))=ROWS(A4:A10)
The ISBLANK function returns a TRUE for every blank cell, which we then coerce into the numeric equivalent using the double unary – -.
The ROWS function returns the number of cells (or rows) in a range.
Using the original data as our example:
The ISBLANK formula evaluates like this:
=SUMPRODUCT({1,0,0,1,0,0,0})=7 =2=7 =FALSE
If the range A4:A10 were empty it would evaluate like this:
=SUMPRODUCT({1,1,1,1,1,1,1})=7 =7=7 =TRUE
Now, if you’ve read this far and are keeping up with me then you might be thinking, ‘can I use ISBLANK to test if any cells contain text or numbers instead of the original ISTEXT/ISNUMBER formula?’
The answer is yes, like this:
In English: if the number of BLANK cells in the range A4:A10 is not equal to the number of rows in the range A4:A10 you will get TRUE, meaning there is at least one cell in the range that isn’t blank.
The downside of this option is the double negatives can do your head in, whereas interpreting the ISTEXT and ISNUMBER formula is a bit less draining on your brain power 🙂
Want to Learn Array Formulas?
Enter your email address below to download the sample workbook.
By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.
Thanks
Thank you to Bill for inspiring this tutorial.
You're reading Excel Test If A Range Contains Text, Numbers Or Is Empty
Update the detailed information about Excel Test If A Range Contains Text, Numbers Or Is Empty on the Khongconthamnam.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!