Find text - worksheet function or macro?

S

sebh007

I have text in A2-A1500. All of the cells contain long strings of text. I
want to identify which cells contain "ItemA" or "ItemB". As I am weak in VB
I would prefer to use worksheet functions if possible. I tried using FIND
but that returns #VALUE! if the string does not exist. All suggestions
welcome!
 
M

Max

One way ..

Put in B2:
=SUMPRODUCT(--ISNUMBER(FIND({"ItemA","ItemB"},A2)))>0
Copy down as far as required to B1500

Col B returns TRUE if either: "ItemA" or "ItemB", or both, are found.
Just autofilter col B for TRUE to get all the lines

Replace FIND with SEARCH
if you don't want the searching to be case sensitive
(SEARCH is not case sensitive)
 
S

sebh007

Fantastic! Thank you so much!

S

Max said:
One way ..

Put in B2:
=SUMPRODUCT(--ISNUMBER(FIND({"ItemA","ItemB"},A2)))>0
Copy down as far as required to B1500

Col B returns TRUE if either: "ItemA" or "ItemB", or both, are found.
Just autofilter col B for TRUE to get all the lines

Replace FIND with SEARCH
if you don't want the searching to be case sensitive
(SEARCH is not case sensitive)
Click to expand...
 
S

sebh007

It's OK thanks - cracked it! Just bracket it with an IF and I'm there -
sorry to trouble you.

S
 
Top