Does cell contain any matches from delimited list

jcopen

New Member
Joined
Aug 1, 2009
Messages
6
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.

ExcelQuest.jpg
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
How about:

Book2
ABCD
1Dog Cat Bird LizardGiraffe Monkey Dog TigerTRUEDog
Sheet6
Cell Formulas
RangeFormula
C1C1=OR(ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*{1,2,3,4,5,6}-98,99))&" "," "&B1&" ")))
D1D1=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.
 

ydeznutz

New Member
Joined
Jun 19, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
  2. Web
interesting i have something similar. thanks
 

jcopen

New Member
Joined
Aug 1, 2009
Messages
6

ADVERTISEMENT

How about:

Book2
ABCD
1Dog Cat Bird LizardGiraffe Monkey Dog TigerTRUEDog
Sheet6
Cell Formulas
RangeFormula
C1C1=OR(ISNUMBER(SEARCH(" "&TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),99*{1,2,3,4,5,6}-98,99))&" "," "&B1&" ")))
D1D1=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,640
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

New Member
Joined
Aug 1, 2009
Messages
6
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,380
Messages
5,601,288
Members
414,440
Latest member
Kim0204

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
Top