Extracting whole word from cell
 

I have a troubles with code to extracting whole word like "count" from cells
My column for search looks like:
A B
1 count |
2 count for games |
3 account with |
4 counting for |
5 count and something |

I need to extract to column B valies from A where word "count"
appear WHOLE :
A B
1 count | count
2 count for games | count for games
3 account with | count and something
4 counting for |
5 count and something |

Find method doesn't work because it has 2 limits , xlWhole or xlParts,
with xlWhole I cannot get A2 and A3, with xlParts I get all 5 records

Thank for help
Leo

Extracting whole word from cell
 

-----Original Message-----
I have a troubles with code to extracting whole word
like "count" from cells
My column for search looks like:
A B
1 count |
2 count for games |
3 account with |
4 counting for |
5 count and something |

I need to extract to column B valies from A where
word "count"
appear WHOLE :
A B
1 count | count
2 count for games | count for games
3 account with | count and something
4 counting for |
5 count and something |

Find method doesn't work because it has 2 limits ,
xlWhole or xlParts,
with xlWhole I cannot get A2 and A3, with xlParts I get
all 5 records

Thank for help
Leo
.


You could try this:
Step 1) Concatenate a space (" ") to the beginning and end
of the cell value.
Step 2) Use the InStr function to look for the
word "count" within the resulting string
Step 3) If you find "count" then check to make sure the
character immediately before it is a space and the
character immediately after it is a space.

e.g.:
CheckStr = UCase(" " & Range(CellAddress).Value & " ")
FoundIt = InStr(CheckStr,"COUNT")
If FoundIt0 Then
SpaceBefore = (Mid(CheckStr,FoundIt-1,1) = " ")
SpaceAfter = (Mid(CheckStr,FoundIt+5,1) = " ")
If (SpaceBefore And SpaceAfter) Then
' Code to process the cell goes here
End If
EndIf

Extracting whole word from cell
 

-----Original Message-----
I have a troubles with code to extracting whole word
like "count" from cells
My column for search looks like:
A B
1 count |
2 count for games |
3 account with |
4 counting for |
5 count and something |

I need to extract to column B valies from A where
word "count"
appear WHOLE :
A B
1 count | count
2 count for games | count for games
3 account with | count and something
4 counting for |
5 count and something |

Find method doesn't work because it has 2 limits ,
xlWhole or xlParts,
with xlWhole I cannot get A2 and A3, with xlParts I get
all 5 records

Thank for help
Leo
.


Silly me - easier is to use Like operator:

If UCase(Range(RangeAddress).Value) like "*COUNT*" then...

Also, if not obvious, you would need to put this into a
loop through your first range and then write code to copy
the relevant cells to the second range

Extracting whole word from cell
 

"K Dales" wrote in message ...
-----Original Message-----
I have a troubles with code to extracting whole word
like "count" from cells
My column for search looks like:
A B
1 count |
2 count for games |
3 account with |
4 counting for |
5 count and something |

I need to extract to column B valies from A where
word "count"
appear WHOLE :
A B
1 count | count
2 count for games | count for games
3 account with | count and something
4 counting for |
5 count and something |

Find method doesn't work because it has 2 limits ,
xlWhole or xlParts,
with xlWhole I cannot get A2 and A3, with xlParts I get
all 5 records

Thank for help
Leo
.


Silly me - easier is to use Like operator:

If UCase(Range(RangeAddress).Value) like "*COUNT*" then...

Also, if not obvious, you would need to put this into a
loop through your first range and then write code to copy
the relevant cells to the second range



================================================== ==================
To K Dales:
Thank for help.
First decision work , second ( with Like ) - no,
but first do my job.

thanks