Just to add tweak, can anyone suggest a way to enter all the target words into just one cell? I'd like to build a sheet that allows users to enter a series of keywords (seperated by a comma). If the keyword is matched a list (of all the sentences) will appear.
Feel free to point me in the direction of any relevant webpages, I've not been able to find a soloution that meets my needs.
Thanks again everyone.
Add the following code (
FastExcel aka Charles Williams) to your workbook...
_________________________
Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If Not IsEmpty(theInput) Then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(CStr(theInput))
Else
vEval = Application.Evaluate(CStr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function
_________________________
Given EVAL, consider:
[TABLE="width: 409"]
<colgroup><col style="width: 149pt; mso-width-source: userset; mso-width-alt: 7054;" width="198"> <col style="width: 48pt;" span="3" width="64"> <col style="width: 116pt; mso-width-source: userset; mso-width-alt: 5518;" width="155"> <tbody>[TR]
[TD="class: xl63, width: 198, bgcolor: transparent"]
go home find and discover[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]
Yes[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 155, bgcolor: transparent"]
search,find,hunt,discover[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]
None looked for this[/TD]
[TD="class: xl63, bgcolor: transparent"]
No[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
E1 houses a set of relevant search tokens, each separated with a comma.
A1:A2 houses some target strings in which to look for the occurrence of any in E1 specified token.
In B1 enter and copy down:
Rich (BB code):
=IF(ISNUMBER(LOOKUP(9.9999999999999E+307,
SEARCH(eval("{"&CHAR(34)&SUBSTITUTE($E$1,",",
CHAR(34)&","&CHAR(34))&CHAR(34)&"}"),A1))),"Yes","No")
