Return true if partial text is found

[crunge75]

I've tried so many examples and ideas from this site but to no avail! It seems simple enough and I'm probably just missing something but what I'm trying to do is this: If partial text is found return true, for example:

In A1
The quick brown fox jumps over the lazy dog.

In A2
The user can enter any part of the line above with additional verbiage for example:

The quick brown fox jumps over the lazy dog. However,

I can make it if any of the words in A! is typed in to return true, but if i type in words in A! with any additional information it returns false.

Any help would be appreciated!

 

[FormR]

Hi, welcome to the forum.

Is the additional verbiage always at the end? Or can it be at the start, in the middle etc? If not always at the end can you post a few more variations of the type of text you are comparing including some that should return a negative result? For each example state the desired result.

 

[Nishant94]

If I understood you correctly, is this what you are looking for:

=ISNUMBER(SEARCH(A2,A1))

 

[crunge75]

Its actual preferable to be in any order so if the user adds any other words like:

The quick brown small fox jumps over the big lazy dog.

Here if the user enters these two words it will still return true.

 

[crunge75]

Number+Word1+Word2 FALSE Word2 Word3

Where the numbers 1,2,3 repents words, letter 2 in this case is the same.

The left side is what the user enters in. The right side is the criteria, where I need to return true because one of the words matches, in this case Letter2 For the left side it can again be preferable to be entered in any order.

 

[FormR]

Hi, try to post several examples that cover all of the possible scenarios - for each example let us know what the expected result it.

If not always at the end can you post a few more variations of the type of text you are comparing including some that should return a negative result? For each example state the desired result.

 

[crunge75]

Here are some examples, currently using something like =ISNUMBER(SEARCH(E2,B2)), returns false. I need to make to be able to make these true.

[TABLE="width: 474"]
<colgroup><col><col><col span="4"></colgroup><tbody>[TR]
[TD][TABLE="width: 472"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD]User Enters[/TD]
[TD]Result[/TD]
[TD]Criteria[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]the quick brown, however[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 3"]The quick brown fox jumps over the lazy dog.[/TD]
[/TR]
[TR]
[TD] fox jumps over and made it[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 3"]The quick brown fox jumps over the lazy dog.[/TD]
[/TR]
[TR]
[TD]why did the lazy dog not see the fox[/TD]
[TD="align: center"]FALSE[/TD]
[TD="colspan: 3"]The quick brown fox jumps over the lazy dog.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="colspan: 4"][/TD]
[/TR]
</tbody>[/TABLE]


I need to be able to make these return true because it found some of the words.

 

[FormR]

I need to be able to make these return true because it found some of the words.

So, if any word from the criteria cell is in the user enters cell then return TRUE?

This isn't perfect (see the last example) but maybe it will work for you.

Excel 2013/2016
	A	B	C
1	User Enters	Result	Criteria
2	the quick brown, however	TRUE	The quick brown fox jumps over the lazy dog.
3	fox jumps over and made it	TRUE	The quick brown fox jumps over the lazy dog.
4	why did the lazy dog not see the fox	TRUE	The quick brown fox jumps over the lazy dog.
5	dog brown green	FALSE	blue red cat
6	dog brown green	TRUE	blue red cat brown
7	however,	TRUE	however blue is good
8	pineapples are tasty	TRUE	apple
Sheet1

Cell Formulas
Range	Formula
B2	=ISNUMBER(LOOKUP(1,-SEARCH(TRIM(MID(SUBSTITUTE(C2," ",REPT(" ",99)),(ROW(INDEX(A:A,1):INDEX(A:A,LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1)))*99-98,99)),A2)))

 

[crunge75]

So, if any word from the criteria cell is in the user enters cell then return TRUE?

This isn't perfect (see the last example) but maybe it will work for you.

Excel 2013/2016

	A	B	C
User Enters	Result	Criteria
the quick brown, however	The quick brown fox jumps over the lazy dog.
fox jumps over and made it	The quick brown fox jumps over the lazy dog.
why did the lazy dog not see the fox	The quick brown fox jumps over the lazy dog.
dog brown green	blue red cat
dog brown green	blue red cat brown
however,	however blue is good
pineapples are tasty	apple

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]TRUE[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]TRUE[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=ISNUMBER(LOOKUP(1,-SEARCH(TRIM(MID(SUBSTITUTE(C2," ",REPT(" ",99)),(ROW(INDEX(A:A,1):INDEX(A:A,LEN(C2)-LEN(SUBSTITUTE(C2," ",""))+1)))*99-98,99)),A2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hi there thanks so much for this -- this is exactly what I was looking for! Now I was curious is there any way for Row a:a,1 to read something like a2:a6? If I change it that way it makes all values to false. If I revert back it makes all values work correctly.

 

[FormR]

Now I was curious is there any way for Row a:a,1 to read something like a2:a6?

Hi, you do not need to change that part of the formula, it's purpose is to robustly generate an array of numbers starting at 1 running up to the number of words in the criteria cell. The overhead from using full column references is small, especially when compared to alternative methods that achieve the same result.