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

luckyharte21

New Member
Joined
Aug 25, 2021
Messages
7
Office Version
  1. 365
Platform
  1. 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!
 
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!
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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. :))
 
Upvote 0
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)

Solution.png
 
Upvote 0
Hi,

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

Thank you


sample data.xlsx
ABC
1TV SHOWSFRIENDSFRIENDS REUNION
2
3CNN00
4Friends, Big Bang theory, and Sheldon10
5Friends, friends reunion11
6Pretty Little Liars.00
7Food Network00
8Friends, Friends Reunion, Robots11
9friends, friends reunion, sopranos, frankle11
10Fox News00
11Friends Reunion01
12Friends Reunion01
13Friends10
14FRIENDS10
15Friends10
16Game of thrones Sopranos Friends True detective10
17Game of thrones Space jam Justice league00
18Gossip Girl00
19Gossip Girl00
20Friends, Friends Reunion, and Pretty Little Liars.11
21CNN00
Sheet1
 
Upvote 0
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
ABC
1TV SHOWSFRIENDSFRIENDS REUNION
2
3CNN
4Friends, Big Bang theory, and Sheldon10
5Friends, friends reunion11
6Pretty Little Liars.00
7Food Network00
8Friends, Friends Reunion, Robots11
9friends, friends reunion, sopranos, frankle11
10Fox News00
11Friends Reunion01
12Friends Reunion01
13Friends10
14FRIENDS10
15Friends10
16Game of thrones Sopranos Friends True detective10
17Game of thrones Space jam Justice league00
18Gossip Girl00
19Gossip Girl00
20Friends, Friends Reunion, and Pretty Little Liars.11
21CNN00
22Enterprise Friendship00
23Friends the reunion01
24Friend reunion01
Sheet1
Cell Formulas
RangeFormula
B4:B24B4=--(COUNT(FIND({" friend "," friends "},SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(" "&LOWER(SUBSTITUTE(SUBSTITUTE(A4,"."," "),","," "))&" "," friend reunion "," ")," friends reunion "," ")," friends the reunion "," ")))>0)
C4:C24C4=--(COUNT(FIND({" friends reunion "," friend reunion "," friends the reunion "}," "&LOWER(SUBSTITUTE(SUBSTITUTE(A4,"."," "),","," "))&" "))>0)
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top