luckyharte21
New Member
- Joined
- Aug 25, 2021
- Messages
- 7
- Office Version
- 365
- Platform
- Windows
Hi All,
I am having a problem searching for certain keywords that might exist in the same cell. I was using the following formula but it's not correct.
=(SUMPRODUCT(--ISNUMBER(SEARCH({"FRIEND","FRIENDS"},A3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH({"FRIENDS REUNION","FRIEND REUNION",FRIENDS THE REUNION")}A3)))=0)
So this is what I am trying to do. In cell A3 I can have a single or multiple mention of the words "friend, friends, friends reunion, friends the reunion or friend reunion. These words might have a period after that, comma or just a space if it's more than one word. I want to be able to distinguish between these two cases. If the cell has strictly only friend or friends, I want to return a value of 1 in cell B3. If there are mentions of friend or friends AND friends reunion or friend reunion or friends the reunion or friends reunion I want to return a value of 2 in cell B3. I think the issue is the similarity in part of the wording between the 2 searches. Is it possible to isolate the situations I mentioned here?
Thank you and much appreciated!
I am having a problem searching for certain keywords that might exist in the same cell. I was using the following formula but it's not correct.
=(SUMPRODUCT(--ISNUMBER(SEARCH({"FRIEND","FRIENDS"},A3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH({"FRIENDS REUNION","FRIEND REUNION",FRIENDS THE REUNION")}A3)))=0)
So this is what I am trying to do. In cell A3 I can have a single or multiple mention of the words "friend, friends, friends reunion, friends the reunion or friend reunion. These words might have a period after that, comma or just a space if it's more than one word. I want to be able to distinguish between these two cases. If the cell has strictly only friend or friends, I want to return a value of 1 in cell B3. If there are mentions of friend or friends AND friends reunion or friend reunion or friends the reunion or friends reunion I want to return a value of 2 in cell B3. I think the issue is the similarity in part of the wording between the 2 searches. Is it possible to isolate the situations I mentioned here?
Thank you and much appreciated!