My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel Formulas / Excel Test if a Range Contains Text, Numbers or is Empty
Excel Test if a Range Contains Text, Numbers or is Empty
by

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:

Excel ISTEXT and ISNUMBER formulas

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:

=SUMPRODUCT(ISTEXT(A4:A10)+ISNUMBER(A4:A10))>0

In English it reads:

SUM the number of cells in the range A4:A10 that contain text + the number of cells that contain number values and if it is > 0 return TRUE, otherwise return FALSE.

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:

=SUMPRODUCT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE)+ISNUMBER(A4:A10))>0

Step 2 – ISNUMBER also returns an array of TRUE/FALSE for each cell in the range A4:A10 like so:

=SUMPRODUCT((FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE)+(FALSE,FALSE,TRUE,FALSE,TRUE,FALSE,FALSE))>0

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:

=SUMPRODUCT((0,1,0,0,0,1,1)+(0,0,1,0,1,0,0))>0

Step 4 – SUMPRODUCT then adds the 1’s and 0’s together:

=SUMPRODUCT(7)>0

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:

=SUM(ISTEXT(A4:A10)+ISNUMBER(A4:A10))>0

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:

=IF(SUMPRODUCT(ISTEXT(A4:A10)+ISNUMBER(A4:A10))>0,"Yes","No")

Test for Numbers Only

=SUMPRODUCT(ISNUMBER(A4:A10)*1)>0

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:

=SUMPRODUCT(ISTEXT(A4:A10)+ISNUMBER(A4:A10))>0

Another way to coerce TRUE/FALSE values is with the double unary like so:

=SUMPRODUCT(--ISNUMBER(A4:A10))>0

Test for Text Only

=SUMPRODUCT(ISTEXT(A4:A10)*1)>0

Or with the double unary:

=SUMPRODUCT(--ISTEXT(A4:A10))>0

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.

In English it reads: SUM the number of BLANK cells in the range A4:A10, if it equals the number of ROWS in the range A4:A10 return TRUE, otherwise return FALSE

Using the original data as our example:

Excel ISTEXT and ISNUMBER formulas

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:

=SUMPRODUCT(--ISBLANK(A4:A10))<>ROWS(A4:A10)

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.

And if you really want to master them consider my Advanced Excel Course where I cover array formulas in detail, among other advanced Excel techniques.

Thanks

Thank you to Bill for inspiring this tutorial.

Excel Test if a Range Contains Text, Numbers or is Empty

More Text Formulas Posts

Excel TEXT Function – handy but limited…or is it?
The Excel TEXT Function converts numbers to text in the format you specify. It's hand for joining numbers and text together in custom chart labels etc.
Extract Text from a Cell using MID Formulas
Excel CLEAN Formula
The Excel CLEAN Function can help you to remove unwanted characters that are often imported when you copy data from web pages, but it has some limitations
Excel SUBSTITUTE Formula
The Excel SUBSTITUTE function replaces new text for old in a text string. It's an alternative to using Find and Replace by retaining the original data.
Excel Factor Entry 3 Re-format Data Using Formulas
Use Excel's Text functions to re-format data imported from external sources
Excel SEARCH and You Will FIND
Excel SUBSTITUTE Function Trick
Excel TRIM Function Removes Spaces From Text
Excel TRIM function removes spaces from the start and end of text, while leaving spaces between words untouched.
Excel UPPER LOWER and PROPER Functions
Microsoft Excel’s T Function
The Excel T function checks whether a value is text, and returns the text if it is, or returns double quotes (empty text) if it isn’t.

More Excel Formulas Posts

top excel functions for data analysts
Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment
Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips
Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays
The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do
Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function
The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files
New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel
How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions
TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.
Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category: Tag:

Reader Interactions

Comments

  2. M

    Question:
    Is there a way to then have the formula give the data in the cell that is not blank in that array? Ideally, it would give the intersection value between that cell and a cell in column B.

    I have a set of data recording the results of a test. The participant receives a stimuli of a certain level and gives an answer. The cell for that level is then filled in with a y= yes or n = no. I want to translate these responses into a string of levels administered.

    Reply

    • Catalin Bombea

      Hi,
      Can you please upload a sample file on our forum, with some manual examples of the expected result? It will be much easier to understand your situation and provide quality help.
      Catalin

      Reply

  3. Kumar

    Plz help on this below, need formula as we want is “2”
    A418IN082486
    A418IN082486
    A418IN082486
    A418IN082486
    A418IN082487
    A418IN082487
    A418IN082487
    A418IN082487
    A418IN082487
    A418IN082487
    A418IN082487
    in excel need formula

    Reply

    • Mynda Treacy

      Hi AZ,

      Formatting to General has no impact on whether a cell contains text or now. That said, ISTEXT should only return TRUE if the cell contains a text string. I’d have to see the file to troubleshoot the cause in your case. If you want to upload your file and question on our Excel forum we’ll take a look.

      Mynda

      Reply

  8. Dave

    I love your examples and guidance it really helps.
    I have a unique question that is becoming complicated.

    I have a spread sheet where one cell has multiple different words and I need to count the number of times each word is used, to come up with a number for each.

    Course #
    # Started With
    # Finished With
    # Graduated With
    Released / Resigned/ Transferred 1 released 1 resigned 2 transferred
    New Training Officer
    # Transferred
    Week Changed

    Any suggestions:

    Reply

    • Catalin Bombea

      Hi Dave,
      Can you please upload a sample workbook with your data structure, and more details on what words you want to count?
      Use our Help Desk to upload the file.
      Cheers,
      Catalin

      Reply

  9. Rosa

    Hi Mynda,

    Your explanation helps me to understand how to look for a first numeric cell in a row.
    However, after finding the first numeric cell, I need to continue looking for a second and a third numeric cell in a row. Is it possible to do this in a table where the first numeric cell is different from row to row?

    Thank you!

    Reply

    • Catalin Bombea

      Hi Rosa,
      You can download this sample workbook for an example on finding the numeric cells: My Online Training Hub – Shared Files
      The formula: 

      =IFERROR(SMALL(IF(ISNUMBER($A2:$C2),COLUMN($A2:$C2),""),COLUMN()-9),"")

      is an array formula, confirmed with CSE.
      The formula is in cell J2, can be copied down and to right as needed. The COLUMN()-9 is meant to return the number of item from array of columns to be returned, to SMALL function. To return the first numeric cell column number, the second argument of SMALL function, -k-, must be 1. For column J, COLUMN() will return 10; COLUMN()-9 is 1 for column J, 2 for column K, and so on. This is a way of creating a dynamic parameter.
      If you need to adjust the formula to your sheet, make sure to change the correction value to return 1 for the first numeric cell:COLUMN()-9 : for example, if you want to start from column E, this column number is 5, so this argument must be COLUMN()-4 to get the first cell…
      Hope it’s clear 🙂
      Catalin

      Reply

  10. emmanuel

    Hi Mynda!! U r rocking…..its just beautiful….nice preparation with explanation…. No one can present more than this! awesome. U know this question is asked in interview written test paper recently which i attended.

    Reply

      • emmanuel

        S…Mynda! finally i found a very good techie cum teacher to clarify my doubts. Pls help me to know more about “Report Generation – Using Macros in Excel”. kindly send me some samples or show me your blog or lessons which you teached earlier.

        Reply

  11. Jon Acampora

    Hi Mynda,

    This is a great explanation of how to use the sumproduct function and how it calculates. It is nice that you explained all the various possibilities of writing the formula to find the same result.

    In regards to Ranjeet’s question, the double hyphen is actually a double negative. You can think of it as multiplying the values by negative 1 twice, resulting in a positive value.

    -1 *-1 * value = positive numeric value

    It is really just a shortcut to multiplying by one. I guess that pressing the hyphen key twice is faster than pressing asterisk and one keys.

    The answers will be the same either way, but it is good to understand this when you work with someone else’s model.

    Thanks again Mynda for presenting all the alternatives!

    Reply

    • Mynda Treacy

      Hi Jon,

      Thanks for your kind words…and for elaborating on the double unary/hyphen for Ranjeet 🙂

      Cheers,

      Mynda.

      Reply

    • Mynda Treacy

      Hi Ranjeet,

      The double hyphen, or double unary as it’s called, coerces the TRUE/FALSE values into their numeric equivalents of 1 and 0. Without this coercion the function cannot apply the mathematical equation to the values.

      e.g. if it’s the SUM function we need 1’s and 0’s in the array, not TRUE/FALSE. SUM cannot add TRUE/FALSE as these are not numbers.

      Another way to coerce TRUE/FALSE values into their numeric equivalents is to multiply them by 1.

      I hope that helps.

      Kind regards,

      Mynda.

      Reply

  13. Bryan

    This is why I enjoy reading your posts, even when it’s something I would have been able to figure out on my own. I would have thought about it more literally, and wouldn’t have come up with the sumproduct answer.

    Here’s what I would have done (all are array-entered formulas):
    Check if any cells contain text or numbers: =OR(ISTEXT(A4:A10),ISNUMBER(A4:A10))
    Test for numbers only: =OR(ISNUMBER(A4:A10))
    Test for text only: =OR(ISTEXT(A4:A10))
    Test if range is empty: =AND(ISBLANK(A4:A10)) or =NOT(OR(ISTEXT(A4:A10),ISNUMBER(A4,A10)) or the simplest, =AND(A4:A10=””) (bonus — this one works with formulas that return an empty string; the rest will only work for truly blank formulas).

    I think my way reads more logically to a layman, but your way is faster, plus it is safer if said laymen decides to mess around with the sheet…. I used Excel for over 10 years before I learned about the Ctrl+Shift+Enter combo!

    Reply

    • Kristen

      I am having trouble and hoping you can help. I am trying to determine if any cell in a single row range has text and if so state yes otherwise no. For example:

      Cells A2-E2 (range) and D2 has text, the output would be yes otherwise no. Likewise for multiples like text in A2, C2, and E2 would also output yes.

      No matter what formula I try, it is not outputting correctly.

      Reply

      • Mynda Treacy

        Hi Kristen,

        You can use COUNTA to count instances of text/numbers in a row. If the count is > 0 then the row is not empty. e.g.

        =IF(COUNTA(A1:D1)>0,"Yes","No")

        Mynda

        Reply

  14. Tom D

    Nicely Done! My head is still spinning, but I was able to follow it all.

    Loved your “English Explanations” — very helpful.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Current ye@r *