Search for Multiple Text Strings within Multiple Phrases

joegutiony · Feb 4, 2012

This is problem: I want to search all the terms Column B within all the text phrases in Column A. I am ok if the words do not match the case sensitivity as I can address that myself.

If the exact phrase match is found, return the value True or False.

I would like to know if anyone has a valid approach, without a Marco.

Thank you in advance for your time and thoughts.

 [FONT=&quot]Column A[/FONT]
[FONT=&quot]A1:Dog likes to run[/FONT]
[FONT=&quot]A2: Cat Red-tummy-01[/FONT]
[FONT=&quot]A3:Mouse is grey[/FONT]
[FONT=&quot]A4: Dog1001[/FONT]
[FONT=&quot]A5: Redcat[/FONT]
[FONT=&quot]A6:Door needs repair
[/FONT]
[FONT=&quot]Column B: Search Terms (my expected result in the parentheses)[/FONT]
[FONT=&quot]B1:Run (only True for A1)
[/FONT][FONT=&quot]B2:Tummy (False; it is not exact full-phrase match in A2)
[/FONT][FONT=&quot]B3:Grey (only true for A3)
[/FONT][FONT=&quot]B4:Dog (only True for A1, it is not exact full-phrase match in A4)
[/FONT][FONT=&quot]B5:Cat (only True for A2)
[/FONT][FONT=&quot]B6:Air (False; should not be true if found in A6)[/FONT]
B7:Red (False; it is not exact full-phrase match to A2 or A5)

T. Valko · Feb 4, 2012

joegutiony said:
This is problem: I want to search all the terms Column B within all the text phrases in Column A. I am ok if the words do not match the case sensitivity as I can address that myself.

If the exact phrase match is found, return the value True or False.

I would like to know if anyone has a valid approach, without a Marco.

Thank you in advance for your time and thoughts.

[FONT=&quot]Column A[/FONT]
[FONT=&quot]A1:Dog likes to run[/FONT]
[FONT=&quot]A2: Cat Red-tummy-01[/FONT]
[FONT=&quot]A3:Mouse is grey[/FONT]
[FONT=&quot]A4: Dog1001[/FONT]
[FONT=&quot]A5: Redcat[/FONT]
[FONT=&quot]A6:Door needs repair[/FONT]

[FONT=&quot]Column B: Search Terms (my expected result in the parentheses)[/FONT]
[FONT=&quot]B1:Run (only True for A1)[/FONT]
[FONT=&quot]B2:Tummy (False; it is not exact full-phrase match in A2)[/FONT]
[FONT=&quot]B3:Grey (only true for A3)[/FONT]
[FONT=&quot]B4:Dog (only True for A1, it is not exact full-phrase match in A4)[/FONT]
[FONT=&quot]B5:Cat (only True for A2)[/FONT]
[FONT=&quot]B6:Air (False; should not be true if found in A6)[/FONT]
B7:Red (False; it is not exact full-phrase match to A2 or A5)

Try this...

Book1

A

B

C

2

Dog likes to run

Run

TRUE

3

Cat Red-tummy-01

Tummy

FALSE

4

Mouse is grey

Grey

TRUE

5

Dog1001

Dog

TRUE

6

Redcat

Cat

TRUE

7

Door needs repair

Air

FALSE

8

_

Red

FALSE

Sheet1

This formula entered in C2 and copied down:

=ISNUMBER(LOOKUP(1E100,SEARCH(" "&B2&" "," "&A$2:A$8&" ")))

joegutiony · Feb 5, 2012

thanks. that is an interesting solution and very helpful.

two questions that I am now considering:

1. ISNUMBER(LOOKUP(1E100, ---- can you clarify why the ISNUMBER is being used. And, why does such a large number, 1E100, play into the solution?

2. if I need to add "tummy", how would that change the code? since it is between two dash. i tried this, but it does not work.
(OR("-"," "))&A$2:A$8&(OR("-"," ")). But that is not the right way to use the OR.

thanks again.

T. Valko · Feb 6, 2012

joegutiony said:
thanks. that is an interesting solution and very helpful.

two questions that I am now considering:

1. ISNUMBER(LOOKUP(1E100, ---- can you clarify why the ISNUMBER is being used. And, why does such a large number, 1E100, play into the solution?

2. if I need to add "tummy", how would that change the code? since it is between two dash. i tried this, but it does not work.
(OR("-"," "))&A$2:A$8&(OR("-"," ")). But that is not the right way to use the OR.

thanks again.

1. A cell can hold a maximum of 32,767 characters. The SEARCH function will return the starting character position of the substring that we are looking for if it exists in the larger string. The starting character position number will be a number from 1 to 32,767.

For the formula to work properly we need to use a lookup value that is greater than 32,767. I use 1E100 which is scientific notation for the very large number 1 followed by 100 zeros. This number is greater than 32,767 and it is easy to remember and easy to type in. Basically, 1E100 is my personal preference for a lookup value. You may see formulas that use 9.99999999999999E+307 as the lookup value but I think that is confusing to most people. How many 9s do I have to type?

On the other hand, 1E100 is nice and simple (KISS)!

So, if SEARCH returns a number that means the substring was found in the larger string. If the substring was not found then SEARCH will return the #VALUE! error. Since you wanted a result of either TRUE or FALSE we need to test if SEARCH returned a number or an error. That's what we use ISNUMBER for.

2. If you want to look for "-tummy-" then:

=ISNUMBER(LOOKUP(1E100,SEARCH("-"&B2&"-","-"&A$2:A$8&"-")))

Where B2 = tummy

Or:

B2 = -tummy-

=ISNUMBER(LOOKUP(1E100,SEARCH(B2,"-"&A$2:A$8&"-")))

joegutiony · Feb 6, 2012

if i use the =ISNUMBER(LOOKUP(1E100,SEARCH("-"&B2&"-","-"&A$2:A$8&"-")))
then it is not finding the other terms.

I am trying to change the result to include "tummy" as TRUE, AND, keep the other results the same.

That is:
[FONT=&quot]Column B: Search Terms (my expected result in the parentheses)[/FONT]
[FONT=&quot]B1:Run (only True for A1)[/FONT]
[FONT=&quot]B2:Tummy (TRUE--- change I am trying to make)[/FONT]
[FONT=&quot]B3:Grey (only true for A3)[/FONT]
[FONT=&quot]B4:Dog (only True for A1, it is not exact full-phrase match in A4)[/FONT]
[FONT=&quot]B5:Cat (only True for A2)[/FONT]
[FONT=&quot]B6:Air (False; should not be true if found in A6)[/FONT]
B7:Red (False; it is not exact full-phrase match to A2 or A5)

T. Valko · Feb 6, 2012

joegutiony said:
if i use the =ISNUMBER(LOOKUP(1E100,SEARCH("-"&B2&"-","-"&A$2:A$8&"-")))
then it is not finding the other terms.

I am trying to change the result to include "tummy" as TRUE, AND, keep the other results the same.

That is:
[FONT=&quot]Column B: Search Terms (my expected result in the parentheses)[/FONT]
[FONT=&quot]B1:Run (only True for A1)[/FONT]
[FONT=&quot]B2:Tummy (TRUE--- change I am trying to make)[/FONT]
[FONT=&quot]B3:Grey (only true for A3)[/FONT]
[FONT=&quot]B4:Dog (only True for A1, it is not exact full-phrase match in A4)[/FONT]
[FONT=&quot]B5:Cat (only True for A2)[/FONT]
[FONT=&quot]B6:Air (False; should not be true if found in A6)[/FONT]
B7:Red (False; it is not exact full-phrase match to A2 or A5)

Is -tummy- the only "oddball" entry you need to find?

joegutiony · Feb 6, 2012

no. it is not the only oddball. i need it to work for any string of text between the two dashes and the other words.
whether it by -tummy- or -abcde- or -abc1000- etc etc
thank you for help.

T. Valko · Feb 6, 2012

joegutiony said:
no. it is not the only oddball. i need it to work for any string of text between the two dashes and the other words.
whether it by -tummy- or -abcde- or -abc1000- etc etc
thank you for help.

By oddball I meant the substring being between other "unusual" characters.

This seems to work...

Book1

A

B

C

2

Dog likes to run

Run

TRUE

3

Cat Red-tummy-01

Tummy

TRUE

4

Mouse is grey

Grey

TRUE

5

Dog1001

Dog

TRUE

6

Redcat

Cat

TRUE

7

Door needs repair

Air

FALSE

8

_

Red

FALSE

Sheet1

Entered in C2 and copied down:

=ISNUMBER(LOOKUP(1E100,SEARCH(" "&B2&" ",SUBSTITUTE(SUBSTITUTE(" "&A$2:A$8&" ","-","- ",1),"-"," -",2))))

joegutiony · Feb 6, 2012

very clever sir! thank you.

T. Valko · Feb 6, 2012

joegutiony said:
very clever sir! thank you.

You're welcome. Thanks for the feedback!

Search for Multiple Text Strings within Multiple Phrases

joegutiony

New Member

Excel Facts

T. Valko

Well-known Member

joegutiony

New Member

T. Valko

Well-known Member

joegutiony

New Member

T. Valko

Well-known Member

joegutiony

New Member

T. Valko

Well-known Member

joegutiony

New Member

T. Valko

Well-known Member

Similar threads

Forum statistics

Share this page

Search for Multiple Text Strings within Multiple Phrases

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock