• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

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

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
August 14, 2013 by Mynda Treacy

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.
Please enter a valid email address.

Download the Excel Workbook and play around with it. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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?

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

Extract Text from a Cell using MID Formulas

Excel CLEAN Formula

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

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

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 SEARCH and You Will FIND

Excel SUBSTITUTE Function Trick

Excel SUBSTITUTE Function Trick

Excel TRIM Function Removes Spaces From Text

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

Excel UPPER LOWER and PROPER Functions

Microsoft Excelโ€™s T Function

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

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

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

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

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

New Array Shaping Excel Functions

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 and 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

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

Excel VSTACK and HSTACK Functions

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

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.


Category: Excel FormulasTag: text formulas
Previous Post:Excel Quintile ChartExcel Quintile Chart
Next Post:Calculate Elapsed Days, Hours and Minutes in ExcelCalculate Elapsed Days, Hours and Minutes in Excel

Reader Interactions

Comments

  1. David Cahill

    April 9, 2021 at 4:29 pm

    Thank you so much, solved my problem.
    Cheers,
    Dave.

    Reply
  2. M

    November 9, 2019 at 1:04 am

    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

      November 9, 2019 at 2:41 pm

      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

    February 25, 2019 at 11:38 pm

    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
    • Philip Treacy

      February 26, 2019 at 8:49 am

      Hi Kumar,

      What exactly do you want to do? It’s not clear.

      Phil

      Reply
  4. AZ

    November 16, 2018 at 1:27 am

    ISTEXT(A1) shows TRUE but the Number Format is GENERAL and cell is empty
    What could be the reason

    Reply
    • Mynda Treacy

      November 16, 2018 at 9:44 am

      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
  5. Rogaciano Fernandes

    July 27, 2017 at 2:05 am

    Works great. thank You.

    Reply
  6. Anna

    October 18, 2016 at 12:51 am

    Thank you!!! Finally found the correct formula to tell me if a range of cells had a value!

    Reply
    • Philip Treacy

      October 18, 2016 at 7:49 am

      You’re welcome ๐Ÿ™‚

      Reply
  7. Chris O'Neal

    July 28, 2016 at 12:19 am

    This was spot on for my project today… thank you and God bless

    Reply
    • Philip Treacy

      July 28, 2016 at 11:36 am

      You’re welcome.

      Regards

      Phil

      Reply
  8. Dave

    March 28, 2015 at 12:50 am

    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

      March 28, 2015 at 2:03 am

      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

    May 22, 2014 at 4:40 am

    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

      May 22, 2014 at 1:52 pm

      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

    September 12, 2013 at 9:38 pm

    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
    • Mynda Treacy

      September 12, 2013 at 9:41 pm

      Aw, thanks Emmanuel ๐Ÿ™‚ Thanks for your kind words. I’m glad you like my tutorials.

      Reply
      • emmanuel

        September 13, 2013 at 12:23 am

        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
        • Mynda Treacy

          September 13, 2013 at 8:35 am

          Hi Emmanuel,

          You can read all the past Excel tutorials on the blog.

          Kind regards,

          Mynda.

          Reply
  11. Jon Acampora

    August 16, 2013 at 12:01 am

    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

      August 16, 2013 at 10:19 am

      Hi Jon,

      Thanks for your kind words…and for elaborating on the double unary/hyphen for Ranjeet ๐Ÿ™‚

      Cheers,

      Mynda.

      Reply
  12. Ranjeet

    August 15, 2013 at 3:48 am

    why we use double hyphen “–” in formula

    Reply
    • Mynda Treacy

      August 15, 2013 at 9:10 am

      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

    August 14, 2013 at 9:54 pm

    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
    • Mynda Treacy

      August 14, 2013 at 9:58 pm

      Love it! Thanks for sharing, Bryan ๐Ÿ™‚

      Reply
    • Kristen

      August 16, 2018 at 8:14 am

      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

        August 16, 2018 at 11:28 am

        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

    August 14, 2013 at 1:03 pm

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

    Loved your “English Explanations” — very helpful.

    Reply
    • Mynda Treacy

      August 14, 2013 at 1:04 pm

      Cheers, Tom ๐Ÿ™‚ Sorry about the head spinning!

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.