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.

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)

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

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)


"Ron Coderre" wrote in message
...
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)

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

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

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

can "he" be replaced with a variable?

"Don Guillett" wrote:

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

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

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" wrote:

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

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

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

mylen=len("he")

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Adam_needs_help" wrote in message
...
can "he" be replaced with a variable?

"Don Guillett" wrote:

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Adam_needs_help" wrote in message
...
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" wrote:

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

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

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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Adam_needs_help" wrote in message
...
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" wrote:

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

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

regexp ?
(regular expressions)

"Adam_needs_help" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Adam_needs_help" wrote in
message
...
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" wrote:

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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Adam_needs_help" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Adam_needs_help" wrote in
message
...
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" wrote:

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

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

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?

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Adam_needs_help" wrote in message
...
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" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Adam_needs_help" wrote in
message
...
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" wrote:

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

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

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

"Adam_needs_help" wrote:

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.