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!

 

[alansidman]

Maybe the Fuzzy Look Up from Microsoft. Here is the link to this Excel Add In

Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center

The Fuzzy Lookup Add-In for Excel performs fuzzy matching of textual data in Excel.

www.microsoft.com

 

[Peter_SSs]

Welcome to the MrExcel board!

It might help us understand better if you provided a set of sample data and expected results, say 10-15 rows, and explain again in relation to that sample data.
Best if we can copy to test with so I suggest XL2BB

 

[luckyharte21]

Welcome to the MrExcel board!

It might help us understand better if you provided a set of sample data and expected results, say 10-15 rows, and explain again in relation to that sample data.
Best if we can copy to test with so I suggest XL2B

Hi Peter,

Sure, you are right. That would help. Here is a sample file of what the data consists of and what I expect the results to look like.

Thank you.

 

[Vikas Kumar]

Hi,

This should be shorter although works for now,

=IF(ISNUMBER(SEARCH("Reunion",A2)),2,IF(ISNUMBER(SEARCH({"Friend","Friends"},A2)),1,0))

enter in B2 and copy down.

 

[luckyharte21]

Hi,

This should be shorter although works for now,

=IF(ISNUMBER(SEARCH("Reunion",A2)),2,IF(ISNUMBER(SEARCH({"Friend","Friends"},A2)),1,0))

enter in B2 and copy down.

View attachment 45675

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.

 

[JEC]

Given the picture of your data, this should be enough

=TRANSPOSE(N(ISNUMBER(SEARCH({"Friend";"Friends Reunion"},A1))))

 

[alansidman]

I can supply you with a Power Query solution if you are interested, however, I cannot manipulate data in a picture. If you are interested, please upload your data using XL2BB as Peter requested. When you post a picture, you are asking us to recreate your file to help you. That is a lot of work to ask for when you are getting FREE help. Help us to help you and make it easy for us to supply you with solutions that are quick and easy.

 

[luckyharte21]

I can supply you with a Power Query solution if you are interested, however, I cannot manipulate data in a picture. If you are interested, please upload your data using XL2BB as Peter requested. When you post a picture, you are asking us to recreate your file to help you. That is a lot of work to ask for when you are getting FREE help. Help us to help you and make it easy for us to supply you with solutions that are quick and easy.

Hi Sir,

I will upload a file shortly.

Thanks again.

 

[luckyharte21]

Given the picture of your data, this should be enough

=TRANSPOSE(N(ISNUMBER(SEARCH({"Friend";"Friends Reunion"},A1))))