Find word in list

[moscott75]

i have two columns. If a word or words in the world list is found in the text string in column A, I want to put found in the adjacent cell or not found. I want to be able to drag cursor down column C.

	A	B	C
1	Statement		Word List
2	Dog jumped over the moon.		Jumped
3	It is a good day.		Good
4	200 Mountain Street		street
5	I am glad it's Friday.		am glad
6	I love pizza.		I love
7	Dell laptop		Sonya
8
9	Results
10	Dog jumped over the moon.	found
11	It is a good day.	found
12	200 Mountain Street	found
13	I am glad it's Friday.	found
14	I love pizza.	found
15	Dell laptop	Not found

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[JoeMo]

Here's a UDF you can try. In B2 enter: =FindWord(A2) and copy down.

To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Function FindWord(S As String) As String
Dim Wrds As Variant, Parts As Variant, i As Long, j As Long

If S = "" Then
       FindWord = ""
       Exit Function
End If
Wrds = Split(Join(Application.Transpose(Range("C2:C" & Cells(Rows.Count, "C").End(xlUp).Row)), " "))
S = Replace(S, ".", "") 'if there will be other punctuation marks, add remove them in similar fashion
Parts = Split(S, " ")
For i = LBound(Parts) To UBound(Parts)
       For j = LBound(Wrds) To UBound(Wrds)
              If LCase(Parts(i)) = LCase(Wrds(j)) Then
                     FindWord = "found"
                     Exit Function
              End If
       Next j
Next i
FindWord = "Not found"
End Function

 

[palaeontology]

wouldn't you simply use ....

=IF(ISNUMBER(SEARCH(C2,A2))=TRUE,"Found","Not Found")

Kind regards,

Chris

 

[JoeMo]

wouldn't you simply use ....

=IF(ISNUMBER(SEARCH(C2,A2))=TRUE,"Found","Not Found")

Kind regards,

Chris

That works for the OP's example, but the request is" "If a word or words in the world list is found in the text string ...." so seems any word in the word list might appear in any cell in col A in the general case.

 

[Peter_SSs]

That works for the OP's example,

To be honest, I don't think the post #3 formula even works for that. For example, change the C2 value to the word "on". The suggested formula returns "Found" when the word 'on" does not appear in A2.

@moscott75
It would be good to clarify further just what you want. What would be the expected results for the below sample, column D, column E or something else?
Could you also please clarify whether I am right or wrong about the "on" example above.

Book1
	A	B	C	D	E
1	Statement		Word List	Check Column	Check Row
2	Dog jumped over the moon.		Good	Found	Not found
3	It is a good day.		moon	Found	Not found
4	200 Mountain Street		street	Found	Found
5	I am glad it's Friday.		I love	Found	Not found
6	I love pizza.		am glad	Found	Not found
7	Dell laptop		Sonya	Not found	Not found
Word in list (2)

 

[moscott75]

Column D is correct. If the words in the word list are found in column A string, I wanted the results to say "found".

 

[JoeMo]

Column D is correct. If the words in the word list are found in column A string, I wanted the results to say "found".

Did you try the UDF in post #2 ?

 

[Rick Rothstein]

Here is another UDF (user defined function) that you can try...

[table="width: 500"]
[tr]
	[td]Function FindWord(S As String) As String
  Dim W As Variant, Words As Variant
  [B][COLOR="#FF0000"]Application.Volatile[/COLOR][/B]
  If S = "" Then Exit Function
  FindWord = "Not Found"
  Words = Range("C2", Cells(Rows.Count, "C").End(xlUp))
  For Each W In Words
    If " " & UCase(S) & " " Like "*[!A-Z]" & UCase(W) & "[!A-Z]*" Then
      FindWord = "Found"
      Exit For
    End If
  Next
End Function[/td]
[/tr]
[/table]

Note: I made the function volatile so that it will react to changes in the word list. If the list is fixed and won't ever be changed, you can comment out the red highlighted line of code.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use FindWord just like it was a built-in Excel function. For example,

=FindWord(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Peter_SSs]

Here is a standard worksheet formula that seems to do what you need for the type of sample data provided so far.

Book1
	A	B	C	D
1	Statement		Word List	Result
2	Dog jumped over the moon.		Good	Found
3	It is a good day.		moon	Found
4	200 Mountain Street		street	Found
5	I am glad it's Friday.		I love	Found
6	I love pizza.		am glad	Found
7	Dell laptop		Sonya	Not found
Sheet1

Cell Formulas
Range	Formula
D2	=IF(IFERROR(AGGREGATE(14,6,SEARCH(" "&$C$2:$C$7&" "," "&SUBSTITUTE(A2,".","")&" "),1),0),"Found","Not found")

However, you did not answer my question about the "on" example discussed above. To clarify, what should the results be for this sample data, given that neither item in column C exists as a whole "word" in column A but both exist within the column A "text"?

Book1
	A	B	C
2	Dog jumped over the moon.		it
3	I am glad it's Friday.		on
Sheet2