Searching for non-consecutive words in cells

[ESA]

I am trying to do a somewhat complicated search of cells that contain
text. The cells often contain several sentences of text. (The
sentences have the standard period at the end each.) I would like to
do a search where I look for combinations of words (for example, the
words "expire" and "year") that appear in the same sentence, not just
the same cell. That is, I am happy to ignore cells where the two
words are not in the same sentence, but would like to identify those
where they appear in the same sentence.

Is this possible?

Thanks

 

[JE McGimpsey]

I am trying to do a somewhat complicated search of cells that contain
text. The cells often contain several sentences of text. (The
sentences have the standard period at the end each.) I would like to
do a search where I look for combinations of words (for example, the
words "expire" and "year") that appear in the same sentence, not just
the same cell. That is, I am happy to ignore cells where the two
words are not in the same sentence, but would like to identify those
where they appear in the same sentence.

Is this possible?

One way, using a User Defined Function (UDF):

Public Function InSameSentence(ByVal sIn As String, _
Optional ByVal sText1 As String = vbNullString, _
Optional ByVal sText2 As String = vbNullString _
) As Variant
Dim vArr As Variant
Dim i As Long
Dim bResult As Boolean

If Len(sIn) = 0 Then
InSameSentence = CVErr(xlErrValue)
Else
vArr = Evaluate("{""" & _
Application.Substitute(sIn, ".", """,""") & """}")
For i = LBound(vArr) To UBound(vArr)
bResult = (InStr(vArr(i), sText1) > 0) And _
(InStr(vArr(i), sText2) > 0)
If bResult Then Exit For
Next i
InSameSentence = bResult
End If
End Function

Call from the worksheet as

=InSameSentence(A1,"expire","year")

Note that omitting an argument will treat the result of that argument as
TRUE, so

=InSameSentence(A1,"expire")

will return true if 'expire' appears anywhere in the cell. Modify to
suit.

If you're not familiar with using UDFs, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[ESA]

One way, using a User Defined Function (UDF):

Public Function InSameSentence(ByVal sIn As String, _
Optional ByVal sText1 As String = vbNullString, _
Optional ByVal sText2 As String = vbNullString _
) As Variant
Dim vArr As Variant
Dim i As Long
Dim bResult As Boolean

If Len(sIn) = 0 Then
InSameSentence = CVErr(xlErrValue)
Else
vArr = Evaluate("{""" & _
Application.Substitute(sIn, ".", """,""") & """}")
For i = LBound(vArr) To UBound(vArr)
bResult = (InStr(vArr(i), sText1) > 0) And _
(InStr(vArr(i), sText2) > 0)
If bResult Then Exit For
Next i
InSameSentence = bResult
End If
End Function

Call from the worksheet as

=InSameSentence(A1,"expire","year")

Note that omitting an argument will treat the result of that argument as
TRUE, so

=InSameSentence(A1,"expire")

will return true if 'expire' appears anywhere in the cell. Modify to
suit.

If you're not familiar with using UDFs, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

JE

This seems pretty straightforward (after I spent a few minutes
familiarizing myself with UDFs).

However, what would be the best way to put this into a formula that
returns in a different cell a 1 if the two words are found in the same
sentence and a 0 otherwise. (I know this should be easy, but I'm not
very experienced with these formulas in Excel).

Thank you for your help.

ESA

 

[JE McGimpsey]

However, what would be the best way to put this into a formula that
returns in a different cell a 1 if the two words are found in the same
sentence and a 0 otherwise. (I know this should be easy, but I'm not
very experienced with these formulas in Excel).

One could either rewrite the UDF to return a numeric value (1/0), or,
leave the result as a boolean (TRUE/FALSE) and convert the result to
1/0. Since XL coerces TRUE/FALSE to 1/0 in arithmetic operations, the
most efficient way would be to use the negation operator to coerce
TRUE/FALSE to -1/0, then apply a second negation to return 1/0:

= --InSameSentence(A1,"expire","year")

 

[ESA]

One could either rewrite the UDF to return a numeric value (1/0), or,
leave the result as a boolean (TRUE/FALSE) and convert the result to
1/0. Since XL coerces TRUE/FALSE to 1/0 in arithmetic operations, the
most efficient way would be to use the negation operator to coerce
TRUE/FALSE to -1/0, then apply a second negation to return 1/0:

= --InSameSentence(A1,"expire","year")

Thanks again for all your help. Could I just simply stick the
"InSameSentence" UDF into a formula in another cell -- something like:

=IF( ISERROR( InSameSentence(A1,"expire","year") )=TRUE, 0, 1 )

This doesn't seem to work. But is there a simple command that would
make this work?

I truly appreciate your assistance.

ESA

 

[JE McGimpsey]

= --InSameSentence(A1,"expire","year")

Thanks again for all your help. Could I just simply stick the
"InSameSentence" UDF into a formula in another cell -- something like:

=IF( ISERROR( InSameSentence(A1,"expire","year") )=TRUE, 0, 1 )

This doesn't seem to work. But is there a simple command that would
make this work?[/QUOTE]

I'm not sure what could be simpler than using two math operators to
coerce the boolean TRUE/FALSE to 1/0.

If you want to add the additional overhead of a function, you could use

=IF(InSameSentence(A1,"expire","year"),1,0)

which will return the same result.