Formula to search for words in a cell from a list

[sobrien1234]

I need a formula in column B to identify if words in columns A are identified in C2:
For example:
In column A I have a list of words
In C2 I have a sentence
In column B I have a formula which identifies if any of the words in column A are identified in the text in C2
Not sure if I need to have a space or not after the commas for the words in Column A?

Thanks

Seamus

 

[Sbugle]

I don't think that you will be able to do it unless the words in the A cell are in an individual cell/list. If you had them hidden with each word in a different cell then you could use a formula similar to: =IF(OR(C1=D1,C1=D2,C1=D3......), "Yes", "No")

It would be as simple as text to columns and removing the comma......

 

[kweaver]

Is VBA an option?

 

[sobrien1234]

I don't think that you will be able to do it unless the words in the A cell are in an individual cell/list. If you had them hidden with each word in a different cell then you could use a formula similar to: =IF(OR(C1=D1,C1=D2,C1=D3......), "Yes", "No")

It would be as simple as text to columns and removing the comma......

No worries - I thought as much. Can still do it just take a little longer

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this does what you want.

Not sure if I need to have a space or not after the commas for the words in Column A?

I have assumed there is a space after each comma but the formula could be adjusted if that is not the case.
If there could be any punctuation in C2, then this approach could be problematic.

21 06 02.xlsm
	A	B	C
1
2	Cat, cat, CAT, Kat, Kat, kat	Yes	There is a kat and a pig in the room
3	Dog, DOG, Dogg	No
4	Pig, pig, PIG, pigg	Yes
Check words

Cell Formulas
Range	Formula
B2:B4	B2	=IF(COUNT(FIND(" "&FILTERXML("<p><c>"&SUBSTITUTE(C$2," ","</c><c>")&"</c></p>","//c")&","," "&A2&",")),"Yes","No")

 

[sobrien1234]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

See if this does what you want.


I have assumed there is a space after each comma but the formula could be adjusted if that is not the case.
If there could be any punctuation in C2, then this approach could be problematic.

21 06 02.xlsm
	A	B	C
1
2	Cat, cat, CAT, Kat, Kat, kat	Yes	There is a kat and a pig in the room
3	Dog, DOG, Dogg	No
4	Pig, pig, PIG, pigg	Yes
Check words

Cell Formulas
Range	Formula
B2:B4	B2	=IF(COUNT(FIND(" "&FILTERXML("<p><c>"&SUBSTITUTE(C$2," ","</c><c>")&"</c></p>","//c")&","," "&A2&",")),"Yes","No")

Awesome!!!!!

 

[Peter_SSs]

Awesome!!!!!

Glad it helped.

I take it from your sample that if column A had "Cat, cat, CAT, Kat, Kat, kat" and column C had "A Fat cAt" that you would want "No" returned because you want the search to be case-sensitive?

 

[sobrien1234]

Glad it helped.

I take it from your sample that if column A had "Cat, cat, CAT, Kat, Kat, kat" and column C had "A Fat cAt" that you would want "No" returned because you want the search to be case-sensitive?

This is really amazing Peter. That said, It doesn't need to be case sensitive - in fact I would prefer it isn't? Also it didn't work if there was a character attached to the text (see below there the / and the . appear to affect it (i.e. LRBA should answer "Yes"). Be great if you could solve this!!

 

[Peter_SSs]

It doesn't need to be case sensitive - in fact I would prefer it isn't

We could deal with that issue easily, but consider the issue below first.

Also it didn't work if there was a character attached to the text (see below there the / and the .

I mentioned that issue already.

If there could be any punctuation in C2, then this approach could be problematic.

If you are looking for "words" in the column C text, then there has to be some way to determine where a "word" begins and ends. If you don't have that and you are looking for "apple" you find it in "pineapples" for example. The normal way that an Excel formula will determine where words start and end is that they occur between space characters (or the start/end of the sentence) and that is what my formula does. So in your latest example "LRBA/borrowing" would be a "word", not two.

If there was very limited punctuation characters (so far we have seen just two "/" and ".") then we could still do this with a formula. If there were many more, again it becomes problematic and vba might be better suited. So what about question mark, quote marks (single or double), exclamation mark, parentheses, asterisk etc? Another one that can be tricky is a hyphen. For example, is "semi-annual" one word or two? Is "Ted Hills-Smith" two words or three? etc

So, if only "/" and "." try this

=IF(COUNT(SEARCH(" "&FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C$2,"/"," "),"."," ")," ","</c><c>")&"</c></p>","//c")&","," "&A2&",")),"Yes","No")

 

[sobrien1234]

This is really amazing Peter. That said, It doesn't need to be case sensitive - in fact I would prefer it isn't? Also it didn't work if there was a character attached to the text (see below there the / and the . appear to affect it (i.e. LRBA should answer "Yes"). Be great if you could solve this!!
View attachment 40106

I also tried adding those words with the characters and didn't work

We could deal with that issue easily, but consider the issue below first.


I mentioned that issue already.

If you are looking for "words" in the column C text, then there has to be some way to determine where a "word" begins and ends. If you don't have that and you are looking for "apple" you find it in "pineapples" for example. The normal way that an Excel formula will determine where words start and end is that they occur between space characters (or the start/end of the sentence) and that is what my formula does. So in your latest example "LRBA/borrowing" would be a "word", not two.

If there was very limited punctuation characters (so far we have seen just two "/" and ".") then we could still do this with a formula. If there were many more, again it becomes problematic and vba might be better suited. So what about question mark, quote marks (single or double), exclamation mark, parentheses, asterisk etc? Another one that can be tricky is a hyphen. For example, is "semi-annual" one word or two? Is "Ted Hills-Smith" two words or three? etc

So, if only "/" and "." try this

=IF(COUNT(SEARCH(" "&FILTERXML("<p><c>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C$2,"/"," "),"."," ")," ","</c><c>")&"</c></p>","//c")&","," "&A2&",")),"Yes","No")

Thanks Peter. I really appreciate this. I can see your point about the punctuation characters and may need to add more in the future. I have 2 more questions:
1. Can you show me the formula with a "?" also added to the punctuation so I can see the difference between the above formula and the new one - then I can add additional characters if I need to?
2. The formula didn't appear to work for the second set of text where there was a . at the end?