Hi,
Have been trying to make this concise but it's annoying me too much now:
I want to return a Yes in a cell if an adjacent cell contains the specific text (of potentially up to 6 different 'keywords') within it's string content.
eg, A column might have a list of locations such as:
Level 1 - foyer by stairs
Level 1 - corridor by room G8
Level 1 - room 1.05 by entry
And in another column I want to flag the entry with a Yes IF the location has some warning words for me, such as "foyer" "corridor" "by room" etc.
Thus far I've got it to work with some nested statements, but it seems yuck.
eg:
=IF(ISNUMBER(SEARCH("by room",I40)),"Yes","")
=IF(ISNUMBER(SEARCH("by room",I40)),"Yes",IF(ISNUMBER(SEARCH("corridor",I40)),"Yes",IF(ISNUMBER(SEARCH("foyer",I40)),"Yes","")))
That works but it's not nice. I want to add a few more checks, and don't want to nest 5 levels down.
What I really want (I sound like the spice girls here) is:
=If (the location string in the cell next to me contains ("by room" OR "corridor" OR "foyer" OR "plant room" OR "service duct" OR "entire") then "YES" else "")
Really open to ideas here.