Return TRUE if a cell contains text OTHER THAN certain substrings

[xavier_eales]

Hi!

I'm new to this forum, but I've tried Googling and using other methods to find my answer without any luck.

I need to find whether a cell contains the text "Inbound", "Core" or "Outbound" as well as whether it contains AT LEAST ONE other word (each word is separated by a comma). So far, I can only figure out how to return TRUE if the cell contains one of the substrings "Inbound", "Core" or "Outbound" using the formula:

SUMPRODUCT(--ISNUMBER(SEARCH({"Inbound","Core","Outbound"},A1)))>0

But, I'm looking to add a condition to this formula that it should only return TRUE if it is the case that there is at least one other word in the cell.

For example, a cell might contain "Inbound, apple". This should return TRUE. However, if a cell contains "apple, banana" or "Inbound" only, then it should return FALSE.

Any help would be much appreciated! Thanks.

 

[JoeMo]

Welcome to the forum!

Excel Workbook
	A	B
1	apple, banana	FALSE
2	Inbound	FALSE
3	Inbound, Apple	TRUE
Tom

 

[Aladin Akyurek]

In B1 control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(","&List&",",","&SUBSTITUTE(A1," ","")&","))),(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1-SUM(IF(ISNUMBER(SEARCH(","&List&",",","&SUBSTITUTE(A1," ","")&",")),1)))>0)

where List refers to a range which lists the relevant items like Inbound, Outbound, and Core.