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!

 

[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".

 

[Legacy 143009]

Shortened it one final time! (Removed the last TRANSPOSE.)

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(MMULT(IFERROR(FIND(substrCol,textCol),0),0*SEQUENCE(substrColRows)+1)>0,"yes","no") )
Dynamic array formulas.

Dear @cmowla thanks for your all effort but I am using 2019. So, the first suggestion has already worked perfectly for me.

 

[Legacy 143009]

.@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

Thanks for the reminder. I was not around here for a long time. I was not aware of those options were available in account settings. I updated my account.

 

[cmowla]

Dear @cmowla thanks for your all effort but I am using 2019. So, the first suggestion has already worked perfectly for me.

That's perfectly okay. I actually made this, just in case you did. And it is very useful for those that do (and can be referenced in the future to solve similar problems in the future). So I thought it was worth the effort. (I never did this before, so it was a learning experience for me.)

 

[Peter_SSs]

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

Dear @cmowla thanks for your all effort but I am using 2019. So, the first suggestion has already worked perfectly for me.

These two statements seem contradictory to me.

The first one seems to indicate that "Catwalk in the night" should return True because it contains "cat", yet the "first suggestion" mentioned in the second quote returns "no" (=false) for that same example (see col D below)

If the string check is not to be case-sensitive, wouldn't this do it?

Flashbond.xlsm
	A	B	C	D
1	text		partial text	post #2
2	I like dog	TRUE	dog	yes
3	I hate lion	FALSE	cat	no
4	Catwalk in the night	TRUE	elephant	no
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=COUNT(SEARCH(C$2:C$4,A2))>0
D2:D4	D2	=IF(SUM(IFERROR(FIND($C$2:$C$4,A2),0))>0,"yes","no")

 

[Legacy 143009]

These two statements seem contradictory to me.

The first one seems to indicate that "Catwalk in the night" should return True because it contains "cat", yet the "first suggestion" mentioned in the second quote returns "no" (=false) for that same example (see col D below)

If the string check is not to be case-sensitive, wouldn't this do it?

Flashbond.xlsm
	A	B	C	D
1	text		partial text	post #2
2	I like dog	TRUE	dog	yes
3	I hate lion	FALSE	cat	no
4	Catwalk in the night	TRUE	elephant	no
Sheet2

Cell Formulas
Range	Formula
B2:B4	B2	=COUNT(SEARCH(C$2:C$4,A2))>0
D2:D4	D2	=IF(SUM(IFERROR(FIND($C$2:$C$4,A2),0))>0,"yes","no")

Ok, my data were all in caps. The first suggestion had worked for me so I went with the first suggestion directly as soon as I saw "FALSE" next to "Cat". COUNT function is also lean. I will mark this as answer.

 

[cmowla]

Well if you ever upgrade to 365, you can convert my formula where you don't have to carry the formula down to case in-sensitive by simply changing FIND to SEARCH. (The same for the originally marked solution.) And COUNT can't be used in the non-carrydown version. (FYI)

 

[Peter_SSs]

And COUNT can't be used in the non-carrydown version. (FYI)

Why not?

Flashbond.xlsm
	A	B	C
1	ab	ab39cf	TRUE
2	cd	cf	FALSE
3	ef	befd	TRUE
4	gh	gh	TRUE
5		ab39cf	TRUE
6		cd	TRUE
7		da	FALSE
8		cat	FALSE
9		reef	TRUE
10		about	TRUE
11		effort	TRUE
Check substrings (2)

Cell Formulas
Range	Formula
C1:C11	C1	=LET(strings,B1:B11,substrings,A1:A4,BYROW(strings,LAMBDA(strings,COUNT(SEARCH(substrings,strings))>0)))
Dynamic array formulas.

 

[DeepuSultan]

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 & " ")))

this formula worked for me :D