+ Reply to Thread
Results 1 to 7 of 7

Record if there is any text in a group of cells

  1. #1
    Registered User
    Join Date
    10-14-2012
    Location
    UK
    MS-Off Ver
    Excel 2000
    Posts
    3

    Record if there is any text in a group of cells

    I know that the ISTEXT command will register a true response if there is any text in a particular cell.

    Does anyone know of a way to do a similar thing for a range of cells so it will record a true response if there is any text in any cell in a range of cells?

    My initial thought was to try =ISTEXT(B3:IL3) but that only seems to record if there is text in B3 and ignores what's going on with C3 through to IL3 - I figured it was worth a try though

    I'm sure there is a really obvious solution but it's completely passing me by at the moment.

    Many Thanks

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,968

    Re: Record if there is any text in a group of cells

    Try COUNTA.


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    10-14-2012
    Location
    UK
    MS-Off Ver
    Excel 2000
    Posts
    3

    Re: Record if there is any text in a group of cells

    Of course that works with text as well! - Thank you for that, you've possibly just saved my sanity

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: Record if there is any text in a group of cells

    COUNTA() will count any cell that has a formula that returns "" (Null String) or numbers.

    Try instead ...
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by Marcol; 10-14-2012 at 09:06 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,968

    Re: Record if there is any text in a group of cells

    I guess it depends on what, exactly, you are checking for. Marcol's formula returns a count of alphabetic text and formulae that return alphabetic text. It ignores numbers and formulae that return blank. In that respect, it may be more in keeping with what you originally had.

    You could also use: =SUMPRODUCT(--ISTEXT(B3:IL3)) ... that will include cells where the formula returns blank which is a more accurate representation of your initial formula.

    So, again, your choice of what you want to count

  6. #6
    Registered User
    Join Date
    10-14-2012
    Location
    UK
    MS-Off Ver
    Excel 2000
    Posts
    3

    Re: Record if there is any text in a group of cells

    Wow while I was away putting the first solution into practice there's even more alternatives - thank you all for taking the time to share your answers as well

    Next time I'm scratching my head trying to work out how to do something in Excel I know where I'm heading first for inspiration!

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    35,968

    Re: Record if there is any text in a group of cells

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1