Search for multiple text

[currybai]

Hi,

I have in column A, a bunch of store names.

For example:

Target-Macys-BestBuy-Kohls.


How can I search for text within that cell if I just want Target and Macys, but not Kohls and then the return value would be something like true or false.

Thanks

 

[Andrew Poulsom]

How about?

=IF(ISNUMBER(SUM(SEARCH({"Target","Macys"},A1))),IF(ISERROR(SEARCH("Kohls",A1)),TRUE))

 

[currybai]

How about?

=IF(ISNUMBER(SUM(SEARCH({"Target","Macys"},A1))),IF(ISERROR(SEARCH("Kohls",A1)),TRUE))

Thanks for the quick reply.

Everything returns a false.

I have it as

=IF(ISNUMBER(SUM(SEARCH({"Target","Macys"},A1))),IF(ISERROR(SEARCH("Kohls","Bloomingdales",A1)),TRUE))

 

[Andrew Poulsom]

What's Bloomingdales doing in there? That's moving the goalposts.

=IF(ISNUMBER(SUM(SEARCH({"Target","Macys"},A1))),IF(ISERROR(SEARCH("Kohls",A1)),IF(ISERROR(SEARCH("Bloomingdales",A1)),TRUE)))

 

[currybai]

What's Bloomingdales doing in there? That's moving the goalposts.

=IF(ISNUMBER(SUM(SEARCH({"Target","Macys"},A1))),IF(ISERROR(SEARCH("Kohls",A1)),IF(ISERROR(SEARCH("Bloomingdales",A1)),TRUE)))

It is still returning all false. I should have been more clear, but if A1 has macys or target or whatever else.

 

[Andrew Poulsom]

Please post some sample data and the results you expect.

 

[currybai]

Please post some sample data and the results you expect.

A1

<table width="584" border="0" cellpadding="0" cellspacing="0"><col width="584"><tr height="20"> <td style="height: 15pt; width: 438pt;" width="584" height="20">Macys-Target-Kohls-BestBuy-HomeDepot-bloomingdales
</td> </tr></table>

If A1 has macys or target or bestbuy but not kohls or bloomingdales then show True.

 

[Andrew Poulsom]

First it's target and macys, now it's macys or target or bestbuy. Try:

=IF(SUM(--ISNUMBER(SEARCH({"macys","target","bestbuy"},A1))),IF(NOT(SUM(--ISNUMBER(SEARCH({"kohls","bloomingdales"},A1)))),TRUE))