Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Peter Gundrum
 
Posts: n/a
Default 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



  #2   Report Post  
Fredrik Wahlgren
 
Posts: n/a
Default


"Peter Gundrum" wrote in message
...
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


  #3   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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



  #4   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #5   Report Post  
Biff
 
Posts: n/a
Default

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.

.



  #6   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #7   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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)),""))
  #8   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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

  #9   Report Post  
Harlan Grove
 
Posts: n/a
Default

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.

  #10   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing a specific character type in text string olasa Excel Discussion (Misc queries) 0 March 21st 05 12:35 AM
fixed column width with text string Sarah Excel Discussion (Misc queries) 1 February 8th 05 10:09 PM
Finding Partial Text in a Cell bob Excel Worksheet Functions 6 December 18th 04 06:03 AM
Formating a text string? METCO1 Excel Discussion (Misc queries) 2 November 30th 04 07:31 PM
Newbie: How to search a text string from right Frank Krogh Excel Worksheet Functions 5 November 26th 04 08:16 PM


All times are GMT +1. The time now is 02:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"