Find text - worksheet function or macro?
 

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!

Find text - worksheet function or macro?
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sebh007" wrote:
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!

Find text - worksheet function or macro?
 

Fantastic! Thank you so much!

S

"Max" wrote:

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sebh007" wrote:
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!

Find text - worksheet function or macro?
 

Pleasure` !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sebh007" wrote in message
...
Fantastic! Thank you so much!

S

Find text - worksheet function or macro?
 

Sorry, but there's more. I get the resuLt 'TRUE' or 'FALSE' which is a great
help but there are other tests I wish to apply elsewhere so I need to be able
to say the equivalent of =IF(A2='TRUE',1,0) or similar. Although the display
in A2 is definitely 'TRUE' Excel doesn't recognise it as such. Is there any
way of utilising the 'TRUE' that is being returned in A2 please?

Thanks in advance.

S



"Max" wrote:

Pleasure` !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sebh007" wrote in message
...
Fantastic! Thank you so much!

S

Find text - worksheet function or macro?
 

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

S

"sebh007" wrote:

Sorry, but there's more. I get the resuLt 'TRUE' or 'FALSE' which is a great
help but there are other tests I wish to apply elsewhere so I need to be able
to say the equivalent of =IF(A2='TRUE',1,0) or similar. Although the display
in A2 is definitely 'TRUE' Excel doesn't recognise it as such. Is there any
way of utilising the 'TRUE' that is being returned in A2 please?

Thanks in advance.

S



"Max" wrote:

Pleasure` !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"sebh007" wrote in message
...
Fantastic! Thank you so much!

S

Find text - worksheet function or macro?
 

Glad you got it sorted out !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
sebh007 wrote:
It's OK thanks - cracked it! Just bracket it with an IF and I'm there -
sorry to trouble you.

S