Find an exact word in a cell with many words

[Adam_needs_help]

I have a column that contains a sentence in each cell. I want to look for an
exact word in each sentence in the entire column. How can I do this in a
macro?

Example:

Two cells contain:
A1: She is walking the dog
A2: It is a he

I want to find only the cells containing "he" which means I should not get
A1 back even though "he" is found within the words "she" and "the." I can't
find any discussions exactly like this anywhere.

 

[Ron Coderre]

With
A1: (a sentence)
D1: (a word or phrase to find......eg he)

This formula returns 1 if the ref cell contains the word, otherwise 0
B1: =MAX(COUNTIF(A1,{"","","* ","* "}&$D$1&{""," *"," *",""," *"}))

This formula returns "Yes" if the ref cell contains the word, otherwise "No"
B1: =IF(MAX(COUNTIF(A1,{"","","* ","* "}&$D$1&{""," *"," *","","
*"})),"Yes","No")

Copy either formula down as far as you need.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

Unfortunate break point for the text wrap:

Here is the second formula in segments:
B1: =IF(MAX(COUNTIF(A1,{"","","* ","* "}&$D$1&
{""," *"," *",""," *"})),"Yes","No")

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Don Guillett]

Sub findword()
For Each c In Range("i1:i3")
x = InStr(c, "he")
If Mid(c, x - 1, 1) = " " And Mid(c, x + 2, 1) = " " Then MsgBox c.Row
Next c
End Sub

 

[Adam_needs_help]

can "he" be replaced with a variable?

Sub findword()
For Each c In Range("i1:i3")
x = InStr(c, "he")
If Mid(c, x - 1, 1) = " " And Mid(c, x + 2, 1) = " " Then MsgBox c.Row
Next c
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Adam_needs_help]

Also, this seems to only work if the word is not the first or last word in
the cell, correct? Since it identifies the word being exact by looking for a
space before and after the word.

If that is not true I am misunderstanding the code.

This is close though.

-Adam

 

[Don Guillett]

Yes, if you used the length of the variable and modified the code for that
length

mylen=len("he")

 

[Don Guillett]

Tested using these lines
if she is walking the dog
it is a he don
it is heavy

 

[Adam_needs_help]

Don, it works weel for the line you listed, and it is a big step in the right
directiong. However, it does not work for:

a man is he
he is a man

Any ideas?

-Adam

 

[MP]

regexp ?
(regular expressions)

Don, it works weel for the line you listed, and it is a big step in the
right
directiong. However, it does not work for:

a man is he
he is a man

Any ideas?

-Adam

 

[Don Guillett]

try it this way
Sub findword()
On Error Resume Next
For Each c In Range("i1:i5")
x = InStr(Trim(c), "he")
If Mid(c, x - 1, 1) = " " And Mid(c, x + 2, 1) = "" Or _
Mid(c, x - 1, 1) = " " And Mid(c, x + 2, 1) = " " Then MsgBox c.Row
Next c
End Sub

 

[Adam_needs_help]

Great, that seems to do the trick.

I follow just about everything, but what does the code:

Or _

Do, does it run the opposite of what comes before the Or?

 

[Adam_needs_help]

Thanks to everyone for helping, I was able to get my code working!