If cell contains word (from a list) then return value

[pqppqpqq]

Hi, in the following table I would like the code to return TRUE if the cell contains a word that refers to a fruit (i.e. apples, oranges, pears)

<TABLE style="WIDTH: 190pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=253 border=0><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3104" width=97><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 4992" width=156><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 73pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" width=97 height=19>I love apples</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 117pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle width=156>TRUE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>I like oranges</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>TRUE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>I bla bla cars</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>FALSE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>I hate books</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>FALSE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: transparent" height=19>I blabla pears</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=middle>TRUE</TD></TR></TBODY></TABLE>

I tried using Vlookups, but it does not work as there are other words in the cell. Is there another method?

Thanks

 

[Aladin Akyurek]

Hi, in the following table I would like the code to return TRUE if the cell contains a word that refers to a fruit (i.e. apples, oranges, pears)

<TABLE style="WIDTH: 190pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=253><COLGROUP><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3104" width=97><COL style="WIDTH: 117pt; mso-width-source: userset; mso-width-alt: 4992" width=156><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 73pt; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=19 width=97>I love apples</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 117pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=156 align=center>TRUE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=19>I like oranges</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=center>TRUE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=19>I bla bla cars</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=center>FALSE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=19>I hate books</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=center>FALSE</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=19>I blabla pears</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=center>TRUE</TD></TR></TBODY></TABLE>

I tried using Vlookups, but it does not work as there are other words in the cell. Is there another method?

Thanks

List first Apples, oranges, and pears in F2:F4...

In B2 enter and copy down:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($F$2:$F$4,A2)))

 

[pqppqpqq]

thats great!
would there be a way to implement this in VBA code?

Thanks again!
Mike

 

[VoG]

would there be a way to implement this in VBA code?

Try

Sub Fruit()
Dim LR As Long, i As Long, j As Long, Fruits
Fruits = Array("apple", "orange", "pear")
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        For j = LBound(Fruits) To UBound(Fruits)
            If .Value Like "*" & Fruits(j) & "*" Then
                .Offset(, 1).Value = True
                Exit For
            End If
        Next j
    End With
Next i
End Sub

You can add more fruits to the array.

 

[pqppqpqq]

you guys are great.
thank you for your help!

 

[Daveydoodles]

Is there a way to use this formula to not just return true or false, but to return the item found

i.e I love apples returns apples
I like pears returns pears

 

[Aladin Akyurek]

Is there a way to use this formula to not just return true or false, but to return the item found

i.e I love apples returns apples
I like pears returns pears

Something like:

LOOKUP(9.99999999999999E+307,SEARCH($F$2:$F$4,A2),$F$2:$F$4)

 

[Kikisinche]

Hi everyone, that helps a lot, what if I want to add 2 more categories, for example "Vegetables" that would include Lettuce, Tomato then print "Veggies" instead of True used for the Fruits condition, and the same for a 3rd category that could be called "Drink" that would include Coffee, Milk then print "Drink".

Would you be able to clarify, for some reason I don't seem to find the trick to make this work.

Thanks a lot, Kevin

Try

Sub Fruit()
Dim LR As Long, i As Long, j As Long, Fruits
Fruits = Array("apple", "orange", "pear")
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        For j = LBound(Fruits) To UBound(Fruits)
            If .Value Like "*" & Fruits(j) & "*" Then
                .Offset(, 1).Value = True
                Exit For
            End If
        Next j
    End With
Next i
End Sub

You can add more fruits to the array.