Checking if word in cell (string) matches one from keyword list

[Underlandtongue]

Hello! I am doing a research project on twitter content from politicians and need some assistance. I've been able to use various functions to either extract or check content within the cells.

But what I am trying to do now is to check the contents of a cell (extracted from an original twitter JSON) against a list on another sheet.

I have the raw Twitter data starting in column C, cell 2. (C2) I have the extracted text only in column E, cell 2. (E2). My data is in Sheet1, in A column (there are spaces in between some of the words/phrases in the data in column A).

I can return a value that states that the entire contents of E2 matching the list in Sheet1 Column A is false. Even if I place the value in the string in E2 or C2 to test them. I'm specifically looking for use of metaphors in the tweets, and I have a list of metaphors I'm looking to return a binary yes/no value.

Here are the formulas I've been trying, that keep giving me the FALSE value (even though the words were in the cell as part of a string).

=IFERROR(IF(e2=VLOOKUP(Sheet1!$A:$A,1,FALSE),TRUE,FALSE),FALSE)

=COUNTIF(Sheet1!A:A, E2)>0

=ISNUMBER(MATCH(E2,Sheet1!A:A,0))

=IF(COUNTIF(e2,Sheet1!A:A)>0,"Yes","No")

=IF(MAX(IFERROR(SEARCH(OFFSET(Sheet1!$A$1,1,0,MATCH(TRUE,INDEX(ISBLANK(Sheet1!$A:$A),0,0),0)-2),$e2),0))>0,"yes","no")

If anyone could help me in the right direction, I'd appreciate it!

Thanks.

 

[Marcílio_Lobão]

Re: Need help checking if word in cell (string) matches one from keyword list

Underlandtongue, Good afternoon.

I have some doubts about your explanation.

Are the words in column A each one in a different cell?

Could you give us at least one concrete example to facilitate our help to you?

If you'd like, save a short simulation of your spreadsheet on a free website www.sendspace.com and place the download link here.

Show what you have data and the expected response.

I'm sure there will be several colleagues to help you.

 

[Underlandtongue]

Re: Need help checking if word in cell (string) matches one from keyword list

Underlandtongue, Good afternoon.

I have some doubts about your explanation.

Are the words in column A each one in a different cell?

Could you give us at least one concrete example to facilitate our help to you?

If you'd like, save a short simulation of your spreadsheet on a free website www.sendspace.com and place the download link here.

Show what you have data and the expected response.

I'm sure there will be several colleagues to help you.

Thank you for replying! The words in column A are both words and phrases. I have tried to write several functions and have tried some ideas from this board/forum. I'm sure it has to do with my limited experience with functions in Excel, so I thought I'd give this a shot.

Here's the download link from sendspace https://www.sendspace.com/file/14c7gj

So I hope that helps. I am trying to determine if the metaphors oh Sheet1 are in the message body.

 

[Underlandtongue]

Re: Need help checking if word in cell (string) matches one from keyword list

Thank you for replying! The words in column A are both words and phrases. I have tried to write several functions and have tried some ideas from this board/forum. I'm sure it has to do with my limited experience with functions in Excel, so I thought I'd give this a shot.

Here's the download link from sendspace https://www.sendspace.com/file/14c7gj

So I hope that helps. I am trying to determine if the metaphors oh Sheet1 are in the message body.

The words and phrases are in their own cells

 

[Fluff]

Re: Need help checking if word in cell (string) matches one from keyword list

Cross posted https://www.excelforum.com/excel-fo...n-a-list-on-another-sheet-nope-epic-fail.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Marcílio_Lobão]

Re: Need help checking if word in cell (string) matches one from keyword list

Well noted Fluff.

Underlandtongue, the rush to have an answer does not justify breaking the rules of kindness or use of the forum.

As for your desired response I have a formula to suggest you different from the one you have already obtained on the other site.

This formula tells you how many phrases in the list are present in the message cell.

=SUMPRODUCT((COUNTIF(E2,"*"& Sheet1!$A$1:$A$296 & "*"))*1)

If you do not care to know how many phrases are present, simply adapt the formula to ask if the result is greater than zero.

=SUMPRODUCT((COUNTIF(E2,"*"& Sheet1!$A$1:$A$296 & "*"))*1)[B][COLOR=#ff0000] > 0[/COLOR][/B]

The answer will be TRUE or FALSE

Or use the other formula suggested to you.

This formula works only with whole sentences.
Therefore, comma-separated sentences are not considered new sentences.

I will try to work the formula to meet this peculiarity.

Separating the sentences between commas in different lines is not an option for you?

In the sentences of the list are unwanted spaces at the end of some of them.
This influences the search in the message cell.

I did a little test and I marked in your example the ones that would need to be worked on.

I used the MSTR tab to simulate a message and put the same applied formula there as an example.

Check the model and tell us if this is what you needed.
https://www.sendspace.com/file/6xyxe9

I hope I have helped you.

 

[Underlandtongue]

Re: Need help checking if word in cell (string) matches one from keyword list

Thank you, I didn't realize there was a cross-post rule. Understood. Thank you for a response, I am going to try to implement it now as we speak.

 

[Underlandtongue]

This works! Thank you so much for your help. I appreciate it. Keep up the good work!