Finding Specific Text in a Text String

Does anybody know if there is a way to do this. Say I
have a list of words

Car
Apple
Fan
House
Fork


Say I have a text string in one cell such as <B>"My car is
in the house in my garage"</B>.


I want to put a formula into the cell next to this text
string and then do the following.

What I need is some sort of function or formula that will
look at my list of words and tell me which words on the
list are in the text string and then mark the cell with
what words it found.

In this example in my text string are the words "House"
and "Car", so I would want the formula to put into the
cell an "H" telling me it found the word "House" and
a "C" telling me it found the word "Car"

So the final result in my cell where the formula is would
be "HC".

Is this possible? Boy, if you could help me with this it
would be greatly appreciated. Thank You

"Peter Gundrum" <anonymous@discussions.microsoft.com> wrote in message
news:007401c53a07$9d04b280$a401280a@phx.gbl...
> Does anybody know if there is a way to do this. Say I
> have a list of words
>
> Car
> Apple
> Fan
> House
> Fork
>
>
> Say I have a text string in one cell such as <B>"My car is
> in the house in my garage"</B>.
>
>
> I want to put a formula into the cell next to this text
> string and then do the following.
>
> What I need is some sort of function or formula that will
> look at my list of words and tell me which words on the
> list are in the text string and then mark the cell with
> what words it found.
>
> In this example in my text string are the words "House"
> and "Car", so I would want the formula to put into the
> cell an "H" telling me it found the word "House" and
> a "C" telling me it found the word "Car"
>
> So the final result in my cell where the formula is would
> be "HC".
>
> Is this possible? Boy, if you could help me with this it
> would be greatly appreciated. Thank You
>
>
>

Not completed yet. I have to go away. but I 'll be back Should get you
started. The problem is that the search is case sensitive

Public Function Peter(ByVal r As Range, ByVal s As String) As String
Dim size As Long
Dim i As Long
Dim ret As Long
Dim sRet As String

size = r.Columns.Count * r.Rows.Count
s = ""

For i = 1 To size
ret = InStr(1, r(i), s, vbTextCompare)

If (ret > 0) Then
s = s + Left(s, 0)
End If
Next
Peter = s
End Function

/Fredrik

If you install the morefunc add-in from http://longre.free.fr/english/,
you can invoke:

=MCONCAT(IF(ISNUMBER(SEARCH(A2:A6,B2)),LEFT(UPPER(A2:A6)),""))

which you must confirm with control+shift+enter instead of just with enter.

Note that A2:A6 is assumed to house the search words and B2 the target
sentence.

Peter Gundrum wrote:
> Does anybody know if there is a way to do this. Say I
> have a list of words
>
> Car
> Apple
> Fan
> House
> Fork
>
>
> Say I have a text string in one cell such as <B>"My car is
> in the house in my garage"</B>.
>
>
> I want to put a formula into the cell next to this text
> string and then do the following.
>
> What I need is some sort of function or formula that will
> look at my list of words and tell me which words on the
> list are in the text string and then mark the cell with
> what words it found.
>
> In this example in my text string are the words "House"
> and "Car", so I would want the formula to put into the
> cell an "H" telling me it found the word "House" and
> a "C" telling me it found the word "Car"
>
> So the final result in my cell where the formula is would
> be "HC".
>
> Is this possible? Boy, if you could help me with this it
> would be greatly appreciated. Thank You
>
>
>

Peter Gundrum wrote...
>Does anybody know if there is a way to do this. Say I
>have a list of words
>
>Car
>Apple
>Fan
>House
>Fork
>
>Say I have a text string in one cell such as <B>"My car is
>in the house in my garage"</B>.
....
>What I need is some sort of function or formula that will
>look at my list of words and tell me which words on the
>list are in the text string and then mark the cell with
>what words it found.
>
>In this example in my text string are the words "House"
>and "Car", so I would want the formula to put into the
>cell an "H" telling me it found the word "House" and
>a "C" telling me it found the word "Car"
>
>So the final result in my cell where the formula is would
>be "HC".
....

Aladin's solution involving MOREFUNC.XLL is best if you can use such an
add-in. If not, and if you don't have too many words in your list, you
could use something like

=LEFT(A1,--(COUNTIF(A8,"*"&A1&"*")>0))
&LEFT(A2,--(COUNTIF(A8,"*"&A2&"*")>0))
&LEFT(A3,--(COUNTIF(A8,"*"&A3&"*")>0))
&LEFT(A4,--(COUNTIF(A8,"*"&A4&"*")>0))
&LEFT(A5,--(COUNTIF(A8,"*"&A5&"*")>0))

where A8 is the cell containing your string, and A1:A5 holds your list
of words.

The OP should be made aware that neither the COUNTIF nor
the ISNUMBER SEARCH formulas are "bullet proof". I haven't
tried the UDF.

Biff

>-----Original Message-----
>Peter Gundrum wrote...
>>Does anybody know if there is a way to do this. Say I
>>have a list of words
>>
>>Car
>>Apple
>>Fan
>>House
>>Fork
>>
>>Say I have a text string in one cell such as <B>"My car
is
>>in the house in my garage"</B>.
>....
>>What I need is some sort of function or formula that will
>>look at my list of words and tell me which words on the
>>list are in the text string and then mark the cell with
>>what words it found.
>>
>>In this example in my text string are the words "House"
>>and "Car", so I would want the formula to put into the
>>cell an "H" telling me it found the word "House" and
>>a "C" telling me it found the word "Car"
>>
>>So the final result in my cell where the formula is would
>>be "HC".
>....
>
>Aladin's solution involving MOREFUNC.XLL is best if you
can use such an
>add-in. If not, and if you don't have too many words in
your list, you
>could use something like
>
>=LEFT(A1,--(COUNTIF(A8,"*"&A1&"*")>0))
>&LEFT(A2,--(COUNTIF(A8,"*"&A2&"*")>0))
>&LEFT(A3,--(COUNTIF(A8,"*"&A3&"*")>0))
>&LEFT(A4,--(COUNTIF(A8,"*"&A4&"*")>0))
>&LEFT(A5,--(COUNTIF(A8,"*"&A5&"*")>0))
>
>where A8 is the cell containing your string, and A1:A5
holds your list
>of words.
>
>.
>

Biff wrote...
>The OP should be made aware that neither the COUNTIF nor
>the ISNUMBER SEARCH formulas are "bullet proof". I haven't
>tried the UDF.
....

If you mean that both are case-insensitive and, e.g., 'Cat' would match
'category' and 'duplicate', then you've got a point. I'll guess
case-insensitivity isn't a big deal for the OP, but false matches to
substrings in longer words could be a problem. If that's the case, no
good alternative to UDFs interfacing to VBScript regular expression
objects, which provide a noncapturing word break token, \b.

Biff wrote:
> The OP should be made aware that neither the COUNTIF nor
> the ISNUMBER SEARCH formulas are "bullet proof". I haven't
> tried the UDF.
>
> Biff
[...]

Indeed. Atleast...

=MCONCAT(IF(ISNUMBER(SEARCH(" "&A2:A6&" "," "&TRIM(B2)&"
")),LEFT(UPPER(A2:A6)),""))

For this to work, the sentence in B2 must be "de-punctuated".

Aladin Akyurek wrote:
> Biff wrote:
>
>> The OP should be made aware that neither the COUNTIF nor the ISNUMBER
>> SEARCH formulas are "bullet proof". I haven't tried the UDF.
>>
>> Biff
>
> [...]
>
> Indeed. Atleast...
>
> =MCONCAT(IF(ISNUMBER(SEARCH(" "&A2:A6&" "," "&TRIM(B2)&"
> ")),LEFT(UPPER(A2:A6)),""))

Aladin Akyurek wrote...
>For this to work, the sentence in B2 must be "de-punctuated".
....

And that requires? The OP showed HTML-like tags, so in addition to the
usual English language punctuation characters,

.. , ; : - ! ? ( ) [ ] '

you'd need to add < and >. That doesn't include &, " or /, all of which
appear from time to time in prose, and * and _, which appear in
newsgroup postings to add emphasis (possibly irrelevant). Anyway,
that's 14 punctuation characters that'd need to be replaced by spaces.
Not possible with built-in functions even with MOREFUNC.XLL since it'd
require at least 14 nested function calls.

I'll repeat: the *ONLY* practical approach to doing this (text
processing, not number crunching) is using a udf wrapper around
VBScript regular expressions.

Harlan Grove wrote:
> Aladin Akyurek wrote...
>
>>For this to work, the sentence in B2 must be "de-punctuated".
>
> ...
>
> And that requires?

I wasn't proposing (put otherwise: intended to propose) de-punctuation
of the target string by means of formulas using Excel's current functions.

[...]

>
> I'll repeat: the *ONLY* practical approach to doing this (text
> processing, not number crunching) is using a udf wrapper around
> VBScript regular expressions.
>

Quite so.