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"


--
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945
View this thread: http://www.excelforum.com/showthread...hreadid=467251

maybe:

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

Regards,
KL

"JemyM" wrote in
message ...

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

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

--
HTH

Bob Phillips

"JemyM" wrote in
message ...

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

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);"*Dance r*");"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

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" wrote in
message ...

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);"*Dance r*");"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

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

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

Regards,
KL


"JemyM" wrote in
message ...

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

KL Wrote:
....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.


--
JemyM
------------------------------------------------------------------------
JemyM's Profile: http://www.excelforum.com/member.php...o&userid=26945
View this thread: http://www.excelforum.com/showthread...hreadid=467251