Page 1 of 2 12 LastLast
Results 1 to 10 of 13
Like Tree1Likes

Find a text within a range of cells

This is a discussion on Find a text within a range of cells within the Excel Questions forums, part of the Question Forums category; Hi, A B joe b elena c tony bob c joe a tom i need a formula if joe b ...

  1. #1
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Find a text within a range of cells

    Hi,

    A B

    joe b elena c
    tony bob c
    joe a tom

    i need a formula if joe b exists in A1:B3 return "yes" other wise "NO"

    Thank you

    Gaftalik

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    75,026

    Default Re: Find a text within a range of cells

    =--(COUNTIF(A1:B3,E1)>0)

    where E1 houses a value like "joe b".

    which will give 1 if success, otherwise 0.
    1968rm likes this.

  3. #3
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Re: Find a text within a range of cells

    Dear Aladin,

    Thank you for your prompt answer, i am afraid i couldnt solve that problem , i will try to be clear :

    in a sheet i have a set of products say: PL small, PL med, PL large, i want to check if they exist in the other sheet table A1:A50 and then return "EXIST" or "NOT"

    Thank you ,
    Gaftalik

  4. #4
    Legend just_jon's Avatar
    Join Date
    Sep 2002
    Location
    Alabama/State of Disarray
    Posts
    10,473

    Default Re: Find a text within a range of cells

    Quote Originally Posted by gaftalik
    Dear Aladin,

    Thank you for your prompt answer, i am afraid i couldnt solve that problem , i will try to be clear :

    in a sheet i have a set of products say: PL small, PL med, PL large, i want to check if they exist in the other sheet table A1:A50 and then return "EXIST" or "NOT"

    Thank you ,
    Gaftalik
    Aladin's formula gets you what you need. What you want would be --

    =IF(COUNTIF('Other Sheet'!A1:A50,A1),"EXISTS","NOT")

    where A1 in this sheet houses your target value such as PL small
    just_jon
    Book of the Month: I'm Not Really an MVP, I Just Play One on TV [j. jon, 2004]

  5. #5
    Board Regular
    Join Date
    Feb 2003
    Posts
    508

    Default Re: Find a text within a range of cells

    Yeah you was right , the first formula worked as well as the second one , sorry but it was my mistake, thanks to both of you !

    gaftalik

  6. #6
    New Member
    Join Date
    Sep 2011
    Posts
    28

    Default Re: Find a text within a range of cells

    hey aladin,
    is there any solution if i would like to search a approx text from a text string... where should be spel mismatchs

  7. #7
    New Member
    Join Date
    Sep 2015
    Posts
    1

    Default Re: Find a text within a range of cells

    What about if you want to find a text string that is returned by a formula?

    i have a few cells with formulae of this ilk:

    if(a1<>a2*1.2,"boo boo here: vat error","")
    if(and(b1>0,b2>0,"boo boo here: can't both be true","")

    and i want to search the document for "boo boo" and return each one...

    so countif works fine, but that just counts them
    I wouldn't mind using match (column by column as it can't do multiple ones at once), but it counts cells with "*boo boo*" in the formulae, rather than just those that output the text string in question...
    Can you put conditions inside the match function that discludes formulae
    for example (though this doesn't work):

    =match(and("boo boo*",<>",?boo boo"),a1:a999,0)

    then as a secondary issue, once i've got that working, how do you use match or another function to return the second, third or fourth instance of a reoccurring text string..?

    I'll be quite impressed with the person who solves this.
    Cheers,
    thegurumonkey

  8. #8
    New Member
    Join Date
    Sep 2015
    Posts
    16

    Exclamation Re: Find a text within a range of cells

    Quote Originally Posted by thegurumonkey View Post
    What about if you want to find a text string that is returned by a formula?

    i have a few cells with formulae of this ilk:

    if(a1<>a2*1.2,"boo boo here: vat error","")
    if(and(b1>0,b2>0,"boo boo here: can't both be true","")

    and i want to search the document for "boo boo" and return each one...

    so countif works fine, but that just counts them
    I wouldn't mind using match (column by column as it can't do multiple ones at once), but it counts cells with "*boo boo*" in the formulae, rather than just those that output the text string in question...
    Can you put conditions inside the match function that discludes formulae
    for example (though this doesn't work):

    =match(and("boo boo*",<>",?boo boo"),a1:a999,0)

    then as a secondary issue, once i've got that working, how do you use match or another function to return the second, third or fourth instance of a reoccurring text string..?

    I'll be quite impressed with the person who solves this.
    Cheers,
    thegurumonkey

    what you are exactly looking for ?
    if you want the text to be printed then its not possible because if u select the range then it will return more than one value at that time how it can be placed in one cell think of it...

    cheers.
    Darshan

  9. #9
    New Member
    Join Date
    Apr 2016
    Posts
    3

    Default Re: Find a text within a range of cells

    I have a little bit complicated question....

    I have a large XLSX with 80 sheets of mixed data imported from different collaborators. A real mess.


    I want find a text inside a text string in a table (or in a full sheet).

    example

    A B

    joe b elena c
    tony bob c
    joe a tom

    I want know how many joe there are, or if at least 1 joe exist in the table (I don't need to find the number of occurence or the coordinates...just nwws to know if at least one exist in that sheet) .

    Possible ?

  10. #10
    New Member
    Join Date
    May 2016
    Posts
    10

    Default Re: Find a text within a range of cells

    Quote Originally Posted by Paolo66 View Post
    I have a little bit complicated question....

    I have a large XLSX with 80 sheets of mixed data imported from different collaborators. A real mess.


    I want find a text inside a text string in a table (or in a full sheet).

    example

    A B

    joe b elena c
    tony bob c
    joe a tom

    I want know how many joe there are, or if at least 1 joe exist in the table (I don't need to find the number of occurence or the coordinates...just nwws to know if at least one exist in that sheet) .

    Possible ?
    hi Paolo,

    COUNTIF was mentioned earlier in this thread, it can also be used to search string within another string using * wildcard in the criteria parameter.

    you can search for appearances of Joe in a range of cells using
    =COUNTIF(B1:D4;"*Joe*") --> you'd still find it even if it's in the middle of the string in a cell

    you can also dynamically defince the text you're looking for, in case you have Joe in F13, the search formula would be
    =COUNTIF(B1:D4;"*"&F13&"*")

    to check an entire sheet, for example a sheet named 'notes' just specify a large enough range that sure contains all data, like entire columns from A to ZZ:
    =COUNTIF(notes!A:ZZ;"*"&F13&"*")

    as far as i know excel doesn't allow adding an entire sheet as range as a function parameter

    For a boolean TRUE/FALSE result about Joe's existence just add an IF function around it, that would verify whether COUNTIF if result is 0 or anything else

Page 1 of 2 12 LastLast

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