Check If a Cell Includes List of Partial Values

[Legacy 143009]

Hello,

Usually, there are many methods to match the partial text in a range but I need just the opposite.
I have list of partial values and I want to check if a specific cell value includes any of those partial strings.

Thanks for the help!

 

[bebo021999]

Like this?

Book1
	A	B	C	D
1	text			partial text
2	I like dog	TRUE		dog
3	I hate lion	FALSE		cat
4	Catwalk in the night	FALSE		elephant
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=OR(ISNUMBER(SEARCH(" " & $D$2:$D$4 & " "," " & A2 & " ")))

 

[Legacy 143009]

Its simpler.

=OR(ISNUMBER(SEARCH($D$2:$D$4,A2)))

Book1
	A	B	C	D
1	text			partial text
2	I like dog	TRUE		dog
3	I hate lion	FALSE		cat
4	Catwalk in the night	FALSE		elephant
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=OR(ISNUMBER(SEARCH($D$2:$D$4,A2)))

B4 should return true since it includes one of the partial strings "cat".

 

[cmowla]

What version of office do you have? And do you want VBA or formulas?

Like this (column A contains substrings. Column B contains the full strings that we need to check to see if they contain any of the substrings in column A)?

a.xlsb
	A	B	C
1	ab	ab39cf	yes
2	cd	cf	no
3	ef		no
Sheet9

Cell Formulas
Range	Formula
C1:C3	C1	=IF(SUM(IFERROR(FIND($A$1:$A$3,B1),0))>0,"yes","no")

 

[Legacy 143009]

Like this?

Book1
	A	B	C	D
1	text			partial text
2	I like dog	TRUE		dog
3	I hate lion	FALSE		cat
4	Catwalk in the night	FALSE		elephant
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=OR(ISNUMBER(SEARCH(" " & $D$2:$D$4 & " "," " & A2 & " ")))

No.. Not really. Actually Catwalk includes one of the strings in the range.

 

[bebo021999]

No.. Not really. Actually Catwalk includes one of the strings in the range.

Its simpler.

=OR(ISNUMBER(SEARCH($D$2:$D$4,A2)))

Book1
	A	B	C	D
1	text			partial text
2	I like dog	TRUE		dog
3	I hate lion	FALSE		cat
4	Catwalk in the night	FALSE		elephant
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=OR(ISNUMBER(SEARCH($D$2:$D$4,A2)))

 

[Legacy 143009]

What version of office do you have? And do you want VBA or formulas?

Like this (column A contains substrings. Column B contains the full strings that we need to check to see if they contain any of the substrings in column A)?

a.xlsb
	A	B	C
1	ab	ab39cf	yes
2	cd	cf	no
3	ef		no
Sheet9

Cell Formulas
Range	Formula
C1:C3	C1	=IF(SUM(IFERROR(FIND($A$1:$A$3,B1),0))>0,"yes","no")

I was not able to check all rows but at the first glance, I think this works. Thanks a lot!

 

[cmowla]

If you have Office 365, then you can use this formula instead, where you don't have to carry down the formula (or manually define the range containing the substring or the range containing the text. You just would need to change all 3 occurrences of A:A to G:G, if the substrings were in column G instead of A. Same for B:B.):

a.xlsb
	A	B	C
1	ab	ab39cf	yes
2	cd	cf	no
3	ef	befd	yes
4	gh	gh	yes
5		ab39cf	yes
6		cd	yes
7		da	no
8		cat	no
9		beef	yes
10		about	yes
11		effort	yes
12
Sheet9

Cell Formulas
Range	Formula
C1:C11	C1	=LET( rowsInA,MAX(IF(A:A<>"",ROW(A:A),0)), rowsInB,MAX(IF(B:B<>"",ROW(B:B),0)), colA,INDEX(A:A,SEQUENCE(rowsInA)), colB,INDEX(B:B,SEQUENCE(rowsInB)), IF(TRANSPOSE(MMULT(TRANSPOSE(0*SEQUENCE(rowsInA))+1,IFERROR(FIND(colA,TRANSPOSE(colB)),0)))>0,"yes","no") )
Dynamic array formulas.

 

[Peter_SSs]

@Flashbond
As has already been asked, please update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Another option might be ..

22 09 29.xlsm
	A	B	C
1	ab	ab39cf	TRUE
2	cd	cf	FALSE
3	ef
Check substrings

Cell Formulas
Range	Formula
C1:C2	C1	=COUNT(FIND(A$1:A$3,B1))>0

 

[cmowla]

For what it's worth, I shortened the formula from my previous post a little, assigned two more LET variables (so that you only have to replace A:A and B:B once), and renamed all LET variables to something more meaningful. (But this assumes that the cells in the two columns are all non-blank starting with the cells in row 1 and up to whatever row they each end on.)

a.xlsb
	A	B	C
1	ab	ab39cf	yes
2	cd	cf	no
3	ef	befd	yes
4	gh	gh	yes
5		ab39cf	yes
6		cd	yes
7		da	no
8		cat	no
9		reef	yes
10		about	yes
11		effort	yes
12
Sheet9

Cell Formulas
Range	Formula
C1:C11	C1	=LET( substrFullCol,A:A, textFullCol,B:B, substrColRows,MAX(IF(substrFullCol<>"",ROW(substrFullCol),0)), textColRows,MAX(IF(textFullCol<>"",ROW(textFullCol),0)), substrCol,INDEX(substrFullCol,SEQUENCE(substrColRows)), textCol,INDEX(textFullCol,SEQUENCE(,textColRows)), IF(TRANSPOSE(MMULT(0*SEQUENCE(,substrColRows)+1,IFERROR(FIND(substrCol,textCol),0)))>0,"yes","no") )
Dynamic array formulas.