+ Reply to Thread
Results 1 to 8 of 8

If "text" exist within "cell" then TRUE

  1. #1
    Registered User
    Join Date
    09-03-2005
    Posts
    23

    Question If "text" exist within "cell" then TRUE

    I cant find a way to look up if a string of text exist within a cell and reply with TRUE or FALSE, such as "if "text" exist somewhere within "cell" then TRUE".


    I have a list that looks something like:

    Archetype Skill
    Dancer Acrobatics, Dance, Seduction
    Repairman Mechanics, Technology, Security

    And a cell where I can define an Archetype, such as:
    A1=Dancer

    I am trying to create a formula that's something like:
    If "Dance" exist somewhere in "1 column from the archetype declared in A1", then type the text "Good at dancing" here, else "Cannot Dance"

  2. #2
    KL
    Guest

    Re: If "text" exist within "cell" then TRUE

    maybe:

    =IF(CONTIF(A:A,"*Dancer*"),"Good at dancing","Cannot Dance")

    Regards,
    KL

    "JemyM" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I cant find a way to look up if a string of text exist within a cell and
    > reply with TRUE or FALSE, such as "if "text" exist somewhere within
    > "cell" then TRUE".
    >
    >
    > I have a list that looks something like:
    >
    > Archetype Skill
    > Dancer Acrobatics, Dance, Seduction
    > Repairman Mechanics, Technology, Security
    >
    > And a cell where I can define an Archetype, such as:
    > A1=Dancer
    >
    > I am trying to create a formula that's something like:
    > If "Dance" exist somewhere in "1 column from the archetype declared in
    > A1", then type the text "Good at dancing" here, else "Cannot Dance"
    >
    >
    > --
    > JemyM
    > ------------------------------------------------------------------------
    > JemyM's Profile:
    > http://www.excelforum.com/member.php...o&userid=26945
    > View this thread: http://www.excelforum.com/showthread...hreadid=467251
    >




  3. #3
    Bob Phillips
    Guest

    Re: If "text" exist within "cell" then TRUE

    =IF(COUNTIF(A1:B100,"*Dancer*"),"Good at dancing","Cannot Dance")

    --
    HTH

    Bob Phillips

    "JemyM" <[email protected]> wrote in
    message news:[email protected]...
    >
    > I cant find a way to look up if a string of text exist within a cell and
    > reply with TRUE or FALSE, such as "if "text" exist somewhere within
    > "cell" then TRUE".
    >
    >
    > I have a list that looks something like:
    >
    > Archetype Skill
    > Dancer Acrobatics, Dance, Seduction
    > Repairman Mechanics, Technology, Security
    >
    > And a cell where I can define an Archetype, such as:
    > A1=Dancer
    >
    > I am trying to create a formula that's something like:
    > If "Dance" exist somewhere in "1 column from the archetype declared in
    > A1", then type the text "Good at dancing" here, else "Cannot Dance"
    >
    >
    > --
    > JemyM
    > ------------------------------------------------------------------------
    > JemyM's Profile:

    http://www.excelforum.com/member.php...o&userid=26945
    > View this thread: http://www.excelforum.com/showthread...hreadid=467251
    >




  4. #4
    Registered User
    Join Date
    09-03-2005
    Posts
    23

    Unhappy

    Thanks both of you for your reply...

    I am using FINDROW to detect the cell that need to be compared with.

    A36 contains the word "Dancer"
    $A$542:$I$564 is the field where all data exist. First column contains the profession, and the 9th column from that contains the skills.

    When I type:
    =FINDROW($A$36;$A$542:$I$564;9)
    in cell J551, then I get the skills, such as Dance etc, listed in that cell. That works.

    If I use
    =IF(COUNTIF(J551;"*Dancer*");"Is a Dancer";"Is not a Dancer")
    then I get "Is a Dancer.

    So far everything works.

    However, when I try to replace J551 with the FINDROW line I am met with an error:

    =IF(COUNTIF(FINDROW($A$36;$A$542:$I$564;9);"*Dancer*");"Is a Dancer";"Is not a Dancer")

    I only get "Error in formula" and Excel refuses to accept it.
    Last edited by JemyM; 09-13-2005 at 06:26 PM.

  5. #5
    Bob Phillips
    Guest

    Re: If "text" exist within "cell" then TRUE

    I have no idea what FINDROW is, but it looks to return a cell value, whereas
    COUNTIF wants the cell reference. So it won't work. As FINDROW seems to be a
    UDF, maybe you could change it to do it all.

    --
    HTH

    Bob Phillips

    "JemyM" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Thanks both of you for your reply...
    >
    > I am using FINDROW to detect the cell that need to be compared with.
    >
    > A36 contains the word "Dancer"
    > $A$542:$I$564 is the field where all data exist. First column contains
    > the profession, and the 9th column from that contains the skills.
    >
    > When I type:
    > =FINDROW($A$36;$A$542:$I$564;9)
    > in cell J551, then I get the skills, such as Dance etc, listed in that
    > cell. That works.
    >
    > If I use
    > =IF(COUNTIF(J551;"*Dancer*");"Is a Dancer";"Is not a Dancer")
    > then I get "Is a Dancer.
    >
    > So far everything works.
    >
    > However, when I try to replace J551 with the FINDROW line I am met with
    > an error:
    >
    > =IF(COUNTIF(FINDROW($A$36;$A$542:$I$564;9);"*Dancer*");"Is a
    > Dancer";"Is not a Dancer")
    >
    > I only get "Error in formula" and Excel refuses to accept it.
    >
    >
    > --
    > JemyM
    > ------------------------------------------------------------------------
    > JemyM's Profile:

    http://www.excelforum.com/member.php...o&userid=26945
    > View this thread: http://www.excelforum.com/showthread...hreadid=467251
    >




  6. #6
    Registered User
    Join Date
    09-03-2005
    Posts
    23

    Post

    Oh.
    FINDROW is a direct translation of what the function is called in swedish "LETARAD". It starts to be very annoying with the swedish version so next time I will go for an english one...

    But ohwell, I might have an alternative.

    Thanks anyway

  7. #7
    KL
    Guest

    Re: If "text" exist within "cell" then TRUE

    ....or rather install this little thingy called TranslateIT:
    http://members.chello.nl/keepitcool/download.html

    Regards,
    KL


    "JemyM" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Oh.
    > FINDROW is a direct translation of what the function is called in
    > swedish "LETARAD". It starts to be very annoying with the swedish
    > version so next time I will go for an english one...
    >
    > But ohwell, I might have an alternative.
    >
    > Thanks anyway
    >
    >
    > --
    > JemyM
    > ------------------------------------------------------------------------
    > JemyM's Profile:
    > http://www.excelforum.com/member.php...o&userid=26945
    > View this thread: http://www.excelforum.com/showthread...hreadid=467251
    >




  8. #8
    Registered User
    Join Date
    09-03-2005
    Posts
    23

    Talking

    Quote Originally Posted by KL
    ....or rather install this little thingy called TranslateIT:
    http://members.chello.nl/keepitcool/download.html

    Regards,
    KL
    Oh this is so awesome. :D
    Thanks! Just what I needed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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