Search cell contents
 

Hello,

I have a list of text data in excel (circa 5,000 rows of text) and I
want to search through each cell and count the number of times a
certain phrase occures. For example:

Search "ilk" from the following lis, only looking in col A: (where "-"
indicates seperate columns)

Col A - Col B
milk - ilk
silk - data
water - crunch
wine - bite

which gives a result of "2".

I can do the loop and counter etc but dont know what to use to look
into each cell for the contents. ie: in psuedo code need a hand with
"if cell contents = "ilk""

While "cell in row a" has contents do
if cell contents = "ilk" then counter = counter +1
next cell
loop.


Thanks

Search cell contents
 

You can either use the Like operator or use the Instr function:

If Cell.Value Like "*ilk*" Then...

If Instr(1,Cell.Value,"ilk")0 Then ...

obviously replace Cell with whatever rng object ref you have.

Richard



kaiser wrote:

Hello,

I have a list of text data in excel (circa 5,000 rows of text) and I
want to search through each cell and count the number of times a
certain phrase occures. For example:

Search "ilk" from the following lis, only looking in col A: (where "-"
indicates seperate columns)

Col A - Col B
milk - ilk
silk - data
water - crunch
wine - bite

which gives a result of "2".

I can do the loop and counter etc but dont know what to use to look
into each cell for the contents. ie: in psuedo code need a hand with
"if cell contents = "ilk""

While "cell in row a" has contents do
if cell contents = "ilk" then counter = counter +1
next cell
loop.


Thanks

Search cell contents
 

Thank you very much - worked perfectly


RichardSchollar wrote:
You can either use the Like operator or use the Instr function:

If Cell.Value Like "*ilk*" Then...

If Instr(1,Cell.Value,"ilk")0 Then ...

obviously replace Cell with whatever rng object ref you have.

Richard



kaiser wrote:

Hello,

I have a list of text data in excel (circa 5,000 rows of text) and I
want to search through each cell and count the number of times a
certain phrase occures. For example:

Search "ilk" from the following lis, only looking in col A: (where "-"
indicates seperate columns)

Col A - Col B
milk - ilk
silk - data
water - crunch
wine - bite

which gives a result of "2".

I can do the loop and counter etc but dont know what to use to look
into each cell for the contents. ie: in psuedo code need a hand with
"if cell contents = "ilk""

While "cell in row a" has contents do
if cell contents = "ilk" then counter = counter +1
next cell
loop.


Thanks