Searching for non-consecutive words in cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ESA - 12 Jul 2007 22:21 GMT

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

Reply to this Message

JE McGimpsey - 12 Jul 2007 23:34 GMT

> I am trying to do a somewhat complicated search of cells that contain
> text. The cells often contain several sentences of text. (The
[quoted text clipped - 6 lines]
>
> 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

Reply to this Message

ESA - 14 Jul 2007 06:13 GMT

> In article <1184275300.325511.125...@22g2000hsm.googlegroups.com>,
>
[quoted text clipped - 48 lines]
>
> 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

Reply to this Message

JE McGimpsey - 15 Jul 2007 05:52 GMT

> 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")

Reply to this Message

ESA - 17 Jul 2007 00:58 GMT

> In article <1184390026.043581.249...@j4g2000prf.googlegroups.com>,
>
[quoted text clipped - 10 lines]
>
> = --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

Reply to this Message

JE McGimpsey - 17 Jul 2007 16:14 GMT

> > = --InSameSentence(A1,"expire","year")
>
[quoted text clipped - 5 lines]
> This doesn't seem to work. But is there a simple command that would
> make this work?

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.

Reply to this Message

Mac Forum / Applications / Excel / July 2007

Searching for non-consecutive words in cells