Extracting whole word from cell

  • Thread starter Thread starter Leo
  • Start date Start date
L

Leo

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
 
-----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
.
Click to expand...

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 FoundIt>0 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
 
-----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
.
Click to expand...

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
 
K Dales said:
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
Click to expand...



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

thanks
 
Back
Top