find range of text within a single cell of text

[dfida]

I have a range of values listed in individual cells, and I want check if another cell contains *any one or more* of those values. For example the text I want to search for (i.e. "find text") is A, B, C, D, E (each letter in its own cell) and the text I am searching (i.e. "within text") is something like NQD. So, in this example, NQD contains at least one letter from the "find text" range (A, B, C, D, E), and the result would be "TRUE" or the position number or some other indication of a positive result.

Another example of "within text" is NQDA, where it has 2 letters from the "find text" range, and the result would also be positive...i.e. at least one condition from find text is inside search text.

Any ideas?

I may also use this in a sumproduct or other type of array where I have to count or sum the records of "within text" that were positive when compared to "find text".

Hope this makes sense!

Excel Workbook
	M	N	O
1	find text	within text	formula
2	A	BAH	2
3	B	FGA	#VALUE!
4	C	D	#VALUE!
5	D	@#%	#VALUE!
6	F	QE	#VALUE!
7	G
8	H
9	J
10	K
11	M
12	N
13	P
14	Q
15	R
16	S
17	T
18	U
19	V
20	Y
21	Z
22	#
tc_codes

Excel 2007

Cell Formulas
Range	Formula
O2	=SEARCH($M$2:$M$22,N2,1)
O3	=SEARCH($M$2:$M$22,N3,1)
O4	=SEARCH($M$2:$M$22,N4,1)
O5	=SEARCH($M$2:$M$22,N5,1)
O6	=SEARCH($M$2:$M$22,N6,1)

 

[BobUmlas]

Ctrl+shift+enter:

=IFERROR(MATCH(FALSE,ISERROR(SEARCH($M$2:$M$22,N2)),0),0)
then fill down

 

[dfida]

Ctrl+shift+enter:

=IFERROR(MATCH(FALSE,ISERROR(SEARCH($M$2:$M$22,N2)),0),0)
then fill down

Beautful! Thanks, it worked perfectly!

 

[T. Valko]

I have a range of values listed in individual cells, and I want check if another cell contains *any one or more* of those values. For example the text I want to search for (i.e. "find text") is A, B, C, D, E (each letter in its own cell) and the text I am searching (i.e. "within text") is something like NQD. So, in this example, NQD contains at least one letter from the "find text" range (A, B, C, D, E), and the result would be "TRUE" or the position number or some other indication of a positive result.

Another example of "within text" is NQDA, where it has 2 letters from the "find text" range, and the result would also be positive...i.e. at least one condition from find text is inside search text.

Any ideas?

I may also use this in a sumproduct or other type of array where I have to count or sum the records of "within text" that were positive when compared to "find text".

Hope this makes sense!

Excel Workbook
	M	N	O
1	find text	within text	formula
2	A	BAH	2
3	B	FGA	#VALUE!
4	C	D	#VALUE!
5	D	@#%	#VALUE!
6	F	QE	#VALUE!
7	G
8	H
9	J
10	K
11	M
12	N
13	P
14	Q
15	R
16	S
17	T
18	U
19	V
20	Y
21	Z
22	#
tc_codes

Excel 2007

Cell Formulas
Range	Formula
O2	=SEARCH($M$2:$M$22,N2,1)
O3	=SEARCH($M$2:$M$22,N3,1)
O4	=SEARCH($M$2:$M$22,N4,1)
O5	=SEARCH($M$2:$M$22,N5,1)
O6	=SEARCH($M$2:$M$22,N6,1)

Here's another one...

Assuming no empty cells in your "find text" range M2:M22.

Enter this formula in O2 and copy down as needed:

=COUNT(LOOKUP(1E100,SEARCH(M$2:M$22,N2)))>0

This will return either TRUE or FALSE.

 

[dfida]

Here's another one...

Assuming no empty cells in your "find text" range M2:M22.

Enter this formula in O2 and copy down as needed:

=COUNT(LOOKUP(1E100,SEARCH(M$2:M$22,N2)))>0

This will return either TRUE or FALSE.

Thanks...have not tried this yet, but what is the "1E100"?

 

[T. Valko]

Thanks...have not tried this yet, but what is the "1E100"?

=COUNT(LOOKUP(1E100,SEARCH(M$2:M$22,N2)))>0

1E100 is scientific notation for a very large number. 1 followed by 100 zeros.

SEARCH will return an array that contains a combination of numbers and #VALUE! errors.

If any of the lookup entries in M2:M22 are found within the entry of cell N2 then those matches return numbers. Lookup entries in M2:M22 that are not found within the entry of cell N2 return the #VALUE! error.

Let's take a look at this small example...

N2 = XYZ

M2 = A
M3 = Z
M4 = T

=COUNT(LOOKUP(1E100,SEARCH(M2:M4,N2)))>0
=TRUE

We can clearly see that M3 Z is within the entry XYZ.

Here's how we get our result...

• A is not found within XYZ so SEARCH(M2,N2) = #VALUE!
• Z is found within XYZ so SEARCH(M3,N2) = 3 (the starting position within the string where the searched for substring is located. 3 = character #3)
• T is not found within XYZ so SEARCH(M4,N2) = #VALUE!

So, we have this array generated by the SEARCH function:

{#VALUE!;3;#VALUE!}

This array is passed to the LOOKUP function:

LOOKUP(1E100,{#VALUE!;3;#VALUE!})

The way that LOOKUP works in this application is if the lookup value 1E100 (1 followed by 100 zeros) is greater than any numeric value in the array returned by SEARCH, then LOOKUP will return the *LAST* numeric value from the array that is less than the lookup value.

In this example the array contains a single number so by default it's the *LAST* numeric value in the array.

So, the result of LOOKUP = 3.

We use an arbitrary number as the lookup value that is guaranteed to be greater than any number returned in the SEARCH array. This assures that we "find" any numbers in the SEARCH array. 1E100 is a gigantic number and it is guaranteed to be greater than any number returned in the SEARCH array. The largest number that SEARCH can return is 32767, the max number of characters allowed in a cell.

This result is then passed to the COUNT function:

COUNT(3)

COUNT returns the count of numbers referenced in its arguments. In this case the argument is the single number 3 so COUNT(3) = 1.

We then test the result of COUNT to see if it's >0:

1>0 = TRUE

And that is the final result of our formula:

=COUNT(LOOKUP(1E100,SEARCH(M2:M4,N2)))>0
=TRUE

If none of the lookup values in M2:M4 are found within the entry in N2 then this is how that breaks down:

SEARCH(M2:M4,N2) = {#VALUE!;#VALUE!;#VALUE!}
LOOKUP(1E100,{#VALUE!;#VALUE!;#VALUE!}) = #VALUE!
COUNT(#VALUE!) = 0
0>0 = FALSE

=COUNT(LOOKUP(1E100,SEARCH(M2:M4,N2)))>0
=FALSE

 

[dfida]

Biff, thanks for the GREAT explanation...the more I learn, the more I realize I don't know...I will be using this (array explanation and the use of 1E100 and other bits of info presented) again and again and I appreciate the time!

 

[mikerickson]

The example data confuses me on Row 2.
B, A & H are all in the find text column, yet "BAH" returns only 2.

If this is a typo, the formula in O2 is
=SUMPRODUCT(COUNTIF(N2,"*"&$M$2:$M$22&"*"))
<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>M</b><td width=25><b>N</b><td width=25><b>O</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>find text<td align="left" bgcolor=#FFFFFF>within text<td align="left" bgcolor=#FFFFFF>formula</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="left" bgcolor=#FFFFFF>A<td align="left" bgcolor=#FFFFFF>BAH<td align="right" bgcolor=#FFFFFF>3</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="left" bgcolor=#FFFFFF>B<td align="left" bgcolor=#FFFFFF>FGA<td align="right" bgcolor=#FFFFFF>3</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="left" bgcolor=#FFFFFF>C<td align="left" bgcolor=#FFFFFF>D<td align="right" bgcolor=#FFFFFF>1</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="left" bgcolor=#FFFFFF>D<td align="left" bgcolor=#FFFFFF>@#%<td align="right" bgcolor=#FFFFFF>1</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="left" bgcolor=#FFFFFF>F<td align="left" bgcolor=#FFFFFF>QE<td align="right" bgcolor=#FFFFFF>1</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="left" bgcolor=#FFFFFF>G<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>8</b><td align="left" bgcolor=#FFFFFF>H<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>9</b><td align="left" bgcolor=#FFFFFF>J<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>10</b><td align="left" bgcolor=#FFFFFF>K<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>11</b><td align="left" bgcolor=#FFFFFF>M<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>12</b><td align="left" bgcolor=#FFFFFF>N<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>13</b><td align="left" bgcolor=#FFFFFF>P<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>14</b><td align="left" bgcolor=#FFFFFF>Q<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>15</b><td align="left" bgcolor=#FFFFFF>R<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>16</b><td align="left" bgcolor=#FFFFFF>S<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>17</b><td align="left" bgcolor=#FFFFFF>T<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>18</b><td align="left" bgcolor=#FFFFFF>U<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>19</b><td align="left" bgcolor=#FFFFFF>V<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>20</b><td align="left" bgcolor=#FFFFFF>Y<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>21</b><td align="left" bgcolor=#FFFFFF>Z<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>22</b><td align="left" bgcolor=#FFFFFF>#<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>

 

[dfida]

The example data confuses me on Row 2.
B, A & H are all in the find text column, yet "BAH" returns only 2.

If this is a typo, the formula in O2 is
=SUMPRODUCT(COUNTIF(N2,"*"&$M$2:$M$22&"*"))



<table border="1" cellspacing="0"><tbody><tr align="center" bgcolor="#a0a0a0"><td width="25"> </td><td width="25">M</td><td width="25">N</td><td width="25">O</td></tr><tr><td align="center" bgcolor="#a0a0a0">1</td><td align="left" bgcolor="#ffffff">find text</td><td align="left" bgcolor="#ffffff">within text</td><td align="left" bgcolor="#ffffff">formula</td></tr><tr><td align="center" bgcolor="#a0a0a0">2</td><td align="left" bgcolor="#ffffff">A</td><td align="left" bgcolor="#ffffff">BAH</td><td align="right" bgcolor="#ffffff">3</td></tr><tr><td align="center" bgcolor="#a0a0a0">3</td><td align="left" bgcolor="#ffffff">B</td><td align="left" bgcolor="#ffffff">FGA</td><td align="right" bgcolor="#ffffff">3</td></tr><tr><td align="center" bgcolor="#a0a0a0">4</td><td align="left" bgcolor="#ffffff">C</td><td align="left" bgcolor="#ffffff">D</td><td align="right" bgcolor="#ffffff">1</td></tr><tr><td align="center" bgcolor="#a0a0a0">5</td><td align="left" bgcolor="#ffffff">D</td><td align="left" bgcolor="#ffffff">@#%</td><td align="right" bgcolor="#ffffff">1</td></tr><tr><td align="center" bgcolor="#a0a0a0">6</td><td align="left" bgcolor="#ffffff">F</td><td align="left" bgcolor="#ffffff">QE</td><td align="right" bgcolor="#ffffff">1</td></tr><tr><td align="center" bgcolor="#a0a0a0">7</td><td align="left" bgcolor="#ffffff">G</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">8</td><td align="left" bgcolor="#ffffff">H</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">9</td><td align="left" bgcolor="#ffffff">J</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">10</td><td align="left" bgcolor="#ffffff">K</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">11</td><td align="left" bgcolor="#ffffff">M</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">12</td><td align="left" bgcolor="#ffffff">N</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">13</td><td align="left" bgcolor="#ffffff">P</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">14</td><td align="left" bgcolor="#ffffff">Q</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">15</td><td align="left" bgcolor="#ffffff">R</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">16</td><td align="left" bgcolor="#ffffff">S</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">17</td><td align="left" bgcolor="#ffffff">T</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">18</td><td align="left" bgcolor="#ffffff">U</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">19</td><td align="left" bgcolor="#ffffff">V</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">20</td><td align="left" bgcolor="#ffffff">Y</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">21</td><td align="left" bgcolor="#ffffff">Z</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr><tr><td align="center" bgcolor="#a0a0a0">22</td><td align="left" bgcolor="#ffffff">#</td><td align="left" bgcolor="#ffffff">
</td><td align="left" bgcolor="#ffffff">
</td></tr></tbody></table>

Not a typo - that's the actual result from my juvenile formula....I assumed the 2 was generated because the first character in within_text (i.e. B) is in the 2nd row of the find_text array.

That said, all three of these formulas are interesting and useful for my application, in different ways:

Bobumlas: returns the row number of find_text based on the first character found in within_text. I used that formula within a vlookup to return the actual character that was found (I did this after not being able to figure out how to use the formula in a sumproduct)...so in that sense, Bob's formula was invisible on a report but doing a lot of background work (filtering and not displaying characters in within_text that I don't care about) so to speak.

T Valko: returns the true/false which really is the gist of what any of my users need to know, per my original problem statement. Looking at it, it also seems to be more compact and doesn't require the CSE which trips up anyone that would try to edit my worksheet. And since the formula also returns the within_text position number, I think I can modify the formula to return the actual character if I need it.


Mikerickson: Returns the number of ocurrences that were found. Until this point I only knew if one was found. I can think of a few instances where I may need to know if more than one was found. Also, no CSE involved.
I see the use of wildcards - the max length of within_text is 3 characters, so I assume the two * are ok?

Decisions, decisions!!! Thanks guys.

Dave

 

[mikerickson]

The universal wild card * does not require that there be a character.

Even though it is one character long, "x" will match the wildcarded criteria "*x*"