Does cell contain any matches from delimited list

[jcopen]

I am trying to construct a formula which would at least give me a TRUE or FALSE. I have two cells that each have a list of space-delimited text terms. I need to know if the second cell contains any of the text terms in the first cell. I need to do this for hundreds of rows of records, so it isn't a realistic option to have to transpose each cell into a column list. Also, the number of space-delimited terms is NOT constant. It varies from one record to another. So in the attached example, the formula would at least need to be able to return TRUE. If it could return a list of all matches, that would be icing on the cake, but I know that may not be possible outside of VBA, lol.

 

[Eric W]

How about:

Book2
	A	B	C	D
1	Dog Cat Bird Lizard	Giraffe Monkey Dog Tiger	TRUE	Dog
Sheet6

Cell Formulas
Range	Formula
C1	C1	=OR(ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*{1,2,3,4,5,6}-98,99))&" "," "&B1&" ")))
D1	D1	=TEXTJOIN(" ",TRUE,IF(ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*{1,2,3,4,5,6}-98,99))&" "," "&B1&" ")),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*{1,2,3,4,5,6}-98,99)),""))

The formula should work for up to 6 values in a cell, but you can change that by changing the array constant {1,2,3,4,5,6}, but if you go too much higher, you might have to change the 99s to 999 and 98s to 998 as well. TEXTJOIN is available in Excel 2019 and 365, so if you have an older version, the TEXTJOIN might not work for you.

 

[jcopen]

Oh Wow, thanks!

 

[ydeznutz]

interesting i have something similar. thanks

 

[jcopen]

How about:

Book2
	A	B	C	D
1	Dog Cat Bird Lizard	Giraffe Monkey Dog Tiger	TRUE	Dog
Sheet6

Cell Formulas
Range	Formula
C1	C1	=OR(ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*{1,2,3,4,5,6}-98,99))&" "," "&B1&" ")))
D1	D1	=TEXTJOIN(" ",TRUE,IF(ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*{1,2,3,4,5,6}-98,99))&" "," "&B1&" ")),TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*{1,2,3,4,5,6}-98,99)),""))

The formula should work for up to 6 values in a cell, but you can change that by changing the array constant {1,2,3,4,5,6}, but if you go too much higher, you might have to change the 99s to 999 and 98s to 998 as well. TEXTJOIN is available in Excel 2019 and 365, so if you have an older version, the TEXTJOIN might not work for you.

Hey thanks - it worked great for me! Because I had to account for up to around 50 words, I did go ahead and add to the array constant up to ",99" with no issues. It turns out that now I need the opposite as well...I need the formula to return my B column cell stripped of the matched words. I tried to tweak your formula to do that by simply switching the "if true" with the "if false" formulas, and it worked except for one glitch, and that was that there were spaces added in the middle of two of the words in the list - see below in red text:

meaningful handmade sentimental day unique love message word inspire keepsake best nevertheless persist jw de gree judge bar exam pass attorney scale paralegal legal congratulation success college 2020 friend daughter girl li fe gavel future

Here is the original cell for reference containing the matching word(matching word in red):

meaningful handmade sentimental day unique gift love message word inspire keepsake best nevertheless persist jw degree judge bar exam pass attorney scale paralegal legal congratulation success college 2020 friend daughter girl life gavel future

If you can make any recommendations, that would be great!

 

[Eric W]

Besides changing the array constant to go up to 99, did you also change the 99's to 999 and the 98's to 998? The trick I used to split the line into individual pieces is to change all the spaces to 99 spaces in a row, then I take out 99-character chunks and remove the spaces from either side. Where this fails is if the total number of letters exceeds 99, then the 99-character chunks could end up cutting a word into 2 different chunks. So if you raise the 99's to 999 and the 98 to 998, it might work. One thing I'm not sure of is how big the temporary string can be internal to the formula. A cell can only hold 32,000 characters. So if you have 34 words with 33*999 spaces between them, that might cause problems. Try 999 and 998 first, and if that doesn't work, experiment with 199 and 198, or 299 and 298, etc.

 

[jcopen]

Besides changing the array constant to go up to 99, did you also change the 99's to 999 and the 98's to 998? The trick I used to split the line into individual pieces is to change all the spaces to 99 spaces in a row, then I take out 99-character chunks and remove the spaces from either side. Where this fails is if the total number of letters exceeds 99, then the 99-character chunks could end up cutting a word into 2 different chunks. So if you raise the 99's to 999 and the 98 to 998, it might work. One thing I'm not sure of is how big the temporary string can be internal to the formula. A cell can only hold 32,000 characters. So if you have 34 words with 33*999 spaces between them, that might cause problems. Try 999 and 998 first, and if that doesn't work, experiment with 199 and 198, or 299 and 298, etc.

298, 299 made it work! Thanks again, and thanks for explaining the concepts!