Results 1 to 10 of 10

Count if cell contains text string

This is a discussion on Count if cell contains text string within the Excel Questions forums, part of the Question Forums category; Hi guys Probably an easy one for you lot but here goes. I have 1000 rows of text containing different ...

  1. #1
    Board Regular
    Join Date
    Dec 2007
    Location
    Newcastle upon Tyne
    Posts
    236

    Default Count if cell contains text string

    Hi guys

    Probably an easy one for you lot but here goes.

    I have 1000 rows of text containing different things (text). I want to count the cell if it contains a text string.

    eg Cell contains "Microsoft Navision". I want to search to see if the cell contains "nav" (just the string as opposed to the whole product)

    Can anyone help?

    Thanks in advance
    Stu
    Never underestimate the power of stupid people in large groups.

  2. #2
    Board Regular
    Join Date
    May 2003
    Posts
    1,037

    Default Re: Count if cell contains text string

    =COUNTIF(A1:A1000,"*" & "nav" & "*")

  3. #3
    Board Regular
    Join Date
    Dec 2007
    Location
    Newcastle upon Tyne
    Posts
    236

    Default Re: Count if cell contains text string

    Thanks - that gives me what I asked for - can't ask for more than that. I think I phrased the question wrong though :P

    Using similar logic, how can I incorperate that into an IF statement? i.e I want the line to say "TRUE" if that line contains my criteria and then drag the formula down.

    Thanks!
    Never underestimate the power of stupid people in large groups.

  4. #4
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,678

    Default Re: Count if cell contains text string

    =ISNUMBER(SEARCH("nav",A1))
    Richard Schollar

    Using xl2013

  5. #5
    Board Regular
    Join Date
    May 2003
    Posts
    1,037

    Default Re: Count if cell contains text string

    You can modify it to:

    =IF(COUNTIF(A1:C1,"*" & "nav" & "*"),"True","False")

    and change the range to suit.

  6. #6
    Board Regular
    Join Date
    Dec 2007
    Location
    Newcastle upon Tyne
    Posts
    236

    Default Re: Count if cell contains text string

    Spot on.

    Thanks to you both

    Stu
    Never underestimate the power of stupid people in large groups.

  7. #7
    New Member
    Join Date
    Nov 2008
    Posts
    2

    Default Re: Count if cell contains text string

    Quote Originally Posted by BAlGaInTl View Post
    You can modify it to:

    =IF(COUNTIF(A1:C1,"*" & "nav" & "*"),"True","False")

    and change the range to suit.
    How should I modify above string if I'd like to know how many times the phrase "xyz." is found from the "Sheet1!$A:$A"?

    Phrase "xyz" can be anywhere in the word so "=COUNTIF(Sheet1!$a:$a;xyz)" won't work.
    Last edited by finlogo; Nov 19th, 2008 at 11:47 AM.

  8. #8
    Board Regular
    Join Date
    Dec 2007
    Location
    Newcastle upon Tyne
    Posts
    236

    Default Re: Count if cell contains text string

    I think...

    =IF(COUNTIF(Sheet1!$A:$A,"*" & "xyz" & "*"),"True","False")

    I thought the "*" bits would denote a wildcard search?
    Last edited by stuartw; Nov 19th, 2008 at 11:57 AM.
    Never underestimate the power of stupid people in large groups.

  9. #9
    New Member
    Join Date
    Nov 2008
    Posts
    2

    Default Re: Count if cell contains text string

    Quote Originally Posted by stuartw View Post
    I think...

    =IF(COUNTIF(Sheet1!$A:$A,"*" & "xyz" & "*"),"True","False")

    ??
    Ooops, I just realise that it's without TRUE/FALSE:

    =COUNTIF(Sheet0!A:A;"*" & "xyz" & "*")


  10. #10
    Board Regular
    Join Date
    Feb 2007
    Location
    Queensalnd
    Posts
    80

    Default Re: Count if cell contains text string

    Quote Originally Posted by BAlGaInTl View Post
    You can modify it to:

    =IF(COUNTIF(A1:C1,"*" & "nav" & "*"),"True","False")

    and change the range to suit.
    How could you do this between dates? And would it be possible to sum another range based on the text string (between dates as well)?
    Last edited by tazeo; Jan 30th, 2012 at 02:07 AM. Reason: extra question bit
    If failure did not have consequences;
    Success would not be such a prize!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com