Find text - worksheet function or macro?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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)
 
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...
 
It's OK thanks - cracked it! Just bracket it with an IF and I'm there -
sorry to trouble you.

S
 
Back
Top