How To Search For Multiple Keywords With Similar Spellings In The Same Cell

[luckyharte21]

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!

 

[luckyharte21]

Hi JEC,

I quickly checked your formula and it's not 100% quite what I was looking for. If I only have "friends reunion" in a cell, I get a value of "1" for friends and I get a value of "1" for friends reunion. In this case, I would only see a value of "1" for friends reunion and a "0" for friends. I think the issue is that the word friend(s) is in the friends reunion wording so it's registering twice. Also, I am not an Excel wiz by any way, based on what you did the results are shown vertically. How can I show those horizontally?

Thanks so much!

 

[Peter_SSs]

Here is a sample file of what the data consists of and what I expect the results to look like.

In your description you said you wanted 1 or 2 (or presumably 0) in column B. In your sample you appear to have split that with 0 or 1 in column B and 0 or 1 in column C.
Which do you really want?

Will this only ever be about "Friends" and other similar titles or might it also be looking for, say, "Gossip Girl" and "Robots"?

(Still waiting for something we can copy. )

 

[Vikas Kumar]

Hi Vikas,

Thank you for your help. I see what you have done however if I have both words friends and friends reunion in the same cell, I need to split those up so that I can get a value of "1" for friends and a value of "1" in another cell for friends reunion.

Thank you.

As per description in the post no 3, Below solution should work

Insert in B2 and copy down,

=IF(ISNUMBER(SEARCH({"friend","friends"},SUBSTITUTE(LOWER(A2),"friends reunion",""))),1,0)

Insert in C2 and copy down,

=IF(ISNUMBER(SEARCH("Reunion",A2)),1,0)

 

[luckyharte21]

Hi,

Hopefully I did this correctly. Here is the mini sheet.

Thank you

sample data.xlsx
	A	B	C
1	TV SHOWS	FRIENDS	FRIENDS REUNION
2
3	CNN	0	0
4	Friends, Big Bang theory, and Sheldon	1	0
5	Friends, friends reunion	1	1
6	Pretty Little Liars.	0	0
7	Food Network	0	0
8	Friends, Friends Reunion, Robots	1	1
9	friends, friends reunion, sopranos, frankle	1	1
10	Fox News	0	0
11	Friends Reunion	0	1
12	Friends Reunion	0	1
13	Friends	1	0
14	FRIENDS	1	0
15	Friends	1	0
16	Game of thrones Sopranos Friends True detective	1	0
17	Game of thrones Space jam Justice league	0	0
18	Gossip Girl	0	0
19	Gossip Girl	0	0
20	Friends, Friends Reunion, and Pretty Little Liars.	1	1
21	CNN	0	0
Sheet1

 

[Vikas Kumar]

It's nice to have sample in this form. Does the solution mentioned in post 13 meet with your expectation?

 

[Peter_SSs]

Does the solution mentioned in post 13 meet with your expectation?

Up to the OP to confirm but by my understanding that would return incorrect results for the last three of my examples below for column B. You also have not addressed the column C expected results.

@luckyharte21
See if this does what you want. I have added a few extra rows to your sample data.

luckyharte21.xlsm
	A	B	C
1	TV SHOWS	FRIENDS	FRIENDS REUNION
2
3	CNN
4	Friends, Big Bang theory, and Sheldon	1	0
5	Friends, friends reunion	1	1
6	Pretty Little Liars.	0	0
7	Food Network	0	0
8	Friends, Friends Reunion, Robots	1	1
9	friends, friends reunion, sopranos, frankle	1	1
10	Fox News	0	0
11	Friends Reunion	0	1
12	Friends Reunion	0	1
13	Friends	1	0
14	FRIENDS	1	0
15	Friends	1	0
16	Game of thrones Sopranos Friends True detective	1	0
17	Game of thrones Space jam Justice league	0	0
18	Gossip Girl	0	0
19	Gossip Girl	0	0
20	Friends, Friends Reunion, and Pretty Little Liars.	1	1
21	CNN	0	0
22	Enterprise Friendship	0	0
23	Friends the reunion	0	1
24	Friend reunion	0	1
Sheet1

Cell Formulas
Range	Formula
B4:B24	B4	=--(COUNT(FIND({" friend "," friends "},SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&LOWER(SUBSTITUTE(SUBSTITUTE(A4,"."," "),","," "))&" "," friend reunion "," ")," friends reunion "," ")," friends the reunion "," ")))>0)
C4:C24	C4	=--(COUNT(FIND({" friends reunion "," friend reunion "," friends the reunion "}," "&LOWER(SUBSTITUTE(SUBSTITUTE(A4,"."," "),","," "))&" "))>0)