Formula - if cell contains certain words in any order

[robinexeva]

Hi all,

I need a formula to count if a cell contains the words 'best' and 'crm' in any order. For example:

Screen Shot 2017-04-07 at 12.46.54.png

What formula would I use in B1 - B5 to get a true value for cells A1 to A4, but a false value for A5?

Thanks!

[Special-K]

=AND(ISNUMBER(SEARCH("best",A1)),ISNUMBER(SEARCH("crm",A1)))

Someone's bound to put this as an alternative so I'll try and beat them to it

=AND(ISNUMBER(SEARCH({"best","crm"},A1)))

[canapone]

Hi

a possible approach

=isnumber(search("best",A1)*search("crm",A1))

Regards

Edit: sorry Special-K I did not mean to overlap. Same concept.

[davsth]

perhaps

=COUNTIFS(A1,"*"&"best"&"*",A1,"*"&"crm"&"*")

[Pete_UK]

Put this in B1:

=AND(ISNUMBER(SEARCH("best",A1)),ISNUMBER(SEARCH("crm",A1)))

then copy down.

Another way would be:

=COUNTIFS(A1,"*best*",A1,"*crm*")>0

Note the asterisks around the words.

Hope this helps.

Pete

[AliGW]

Welcome to the forum!

Suggestion removed - doesn't work as intended - sorry!

[Pete_UK]

WOW, so many responses within 3 minutes of each other.

Pete

[AliGW]

WOW, so many responses within 3 minutes of each other.

Pete

[Special-K]

Welcome to the forum!

In B1 copied down:

=IFERROR(SEARCH("best",A1)+SEARCH("crm",A1)>1,FALSE)

1, 2, 3 opening brackets...
1, 2, 3 closing brackets...

Yep, all there!

[AliGW]

It's a new day and I am properly caffeinated.