Using nested "if(or(isnumber(search.." to search in a string for text and return a value

[TMCINNIS]

I am trying to build a nested formula that will search in a url string for a set of keywords and return a label.

I have tried several different ways of doing this and have not been successful.

Here are my attempts.

Thanks in advance for any assistance on this question.

=IF(OR(ISNUMBER(SEARCH({“*TWITTER*”,“*FACEBOOK*”,“*REDDIT*”,“*YELP*”,“*LNKD*”,“*TINY*”,“*DISQUS*”},A15)),“SOCIAL_MEDIA”,
IF(OR(ISNUMBER(SEARCH({“*KEYE*”,“*KMFA*”,=“*KUT*”,“*KVUE*”,“*KXAN*”,“*STATESMAN*”},A15)),“MEDIA_OUTLETS”,
IF(OR(ISNUMBER(SEARCH({“*LOWES*”,“*HOMEDEPOT*”,“*AMAZON*”},A15)),“LARGE_RETAIL”,
IF(OR(ISNUMBER(SEARCH({“*.GOV*”,“*.TX.US*”},A15)),“GOV AND TX.US DOMAINS”,
IF(OR(ISNUMBER(SEARCH({“*EDU*”},A15)),“EDU DOMAINS”,“OTHER”)))))

=IF(OR(ISNUMBER(find({“*TWITTER*”,“*FACEBOOK*”,“*REDDIT*”,“*YELP*”,“*LNKD*”,“*TINY*”,“*DISQUS*”},A15)),“SOCIAL_MEDIA”,
IF(OR(ISNUMBER(find({“*KEYE*”,“*KMFA*”,=“*KUT*”,“*KVUE*”,“*KXAN*”,“*STATESMAN*”},A15)),“MEDIA_OUTLETS”,
IF(OR(ISNUMBER(find({“*LOWES*”,“*HOMEDEPOT*”,“*AMAZON*”},A15)),“LARGE_RETAIL”,
IF(OR(ISNUMBER(find({“*.GOV*”,“*.TX.US*”},A15)),“GOV AND TX.US DOMAINS”,
IF(OR(ISNUMBER(find({“*EDU*”},A15)),“EDU DOMAINS”,“OTHER”)))))


=IF(COUNT(SEARCH({“*TWITTER*”,“*FACEBOOK*”,“*REDDIT*”,“*YELP*”,“*LNKD*”,“*TINY*”,“*DISQUS*”},A15)),“SOCIAL_MEDIA”,
IF(COUNT(SEARCH({“*KEYE*”,“*KMFA*”,=“*KUT*”,“*KVUE*”,“*KXAN*”,“*STATESMAN*”},A15)),“MEDIA_OUTLETS”,
IF(COUNT(SEARCH({“*LOWES*”,“*HOMEDEPOT*”,“*AMAZON*”},A15)),“LARGE_RETAIL”,
IF(COUNT(SEARCH({“*.GOV*”,“*.TX.US*”},A15)),“GOV AND TX.US DOMAINS”,
IF(COUNT(SEARCH({“*EDU*”},A15)), “EDU_DOMAINS”,“OTHER”)))))


=IF(OR(A15="*TWITTER*",A15="*FACEBOOK*",A15="*REDDIT*",A15="*YELP*",A15="*LNKD*",A15="*TINY*",A15="*DISQUS*"),"SOCIAL_MEDIA",
IF(OR(A15="*KEYE*",A15="*KMFA*",A15="*KUT*",A15="*KVUE*",A15="*KXAN*",A15="*STATESMAN*"),"PRINT_MEDIA",
IF(OR(A15="*LOWES*",A15="*HOMEDEPOT*",A15="*AMAZON*"),"LARGE_RETAIL",
IF(OR(A15="*.GOV*",A15="*.TX.US*"),"GOV and TX.US DOMAINS",
IF(OR(A15="*.EDU*"),"EDU_DOMAINS",“OTHER”))))))

 

[Jonmo1]

I suggest making a table containing all the keywords and their respective results.
Say E2:F10 or wherever.
Column E would be all the keywords, Twitter Facebook, Reddit, etc..
Column F is their corresponding value Social_Media, Media_Outlets, Large_Retail etc..

Then use

=LOOKUP(2^15,SEARCH($E$2:$E$10,A15),$F$2:$F$10)

 

[Jonmo1]

Example
Note, there cannot be any blanks in E2:E10

Excel Workbook
	A	B
15	This is Lowes Dude	Large_Retail
16	I hate .GOV man	GOV AND TX.US DOMAINS
17	Stay with Facebook for a while	Social_Media
Sheet1

Excel Workbook
	E	F
2	Twitter	Social_Media
3	Facebook	Social_Media
4	Keye	Media_Outlets
5	KMFA	Media_Outlets
6	Lowes	Large_Retail
7	HomeDepot	Large_Retail
8	.Gov	GOV AND TX.US DOMAINS
9	.TX.US	GOV AND TX.US DOMAINS
10	EDU	EDU_Domains
Sheet1

 

[TMCINNIS]

Example
Note, there cannot be any blanks in E2:E10

Sheet1

	A	B
This is Lowes Dude	Large_Retail
I hate .GOV man	GOV AND TX.US DOMAINS
Stay with Facebook for a while	Social_Media

<tbody>
[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]17[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B15 =LOOKUP(2^15,SEARCH($E$2:$E$10,A15),$F$2:$F$10) B16 =LOOKUP(2^15,SEARCH($E$2:$E$10,A16),$F$2:$F$10) B17 =LOOKUP(2^15,SEARCH($E$2:$E$10,A17),$F$2:$F$10) <tbody> </tbody>

<tbody>
</tbody>

Sheet1

	E	F
Twitter	Social_Media
Facebook	Social_Media
Keye	Media_Outlets
KMFA	Media_Outlets
Lowes	Large_Retail
HomeDepot	Large_Retail
.Gov	GOV AND TX.US DOMAINS
.TX.US	GOV AND TX.US DOMAINS
EDU	EDU_Domains

<tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

Jonmo1
Sadly, this does not solve my problem. I have a spreadsheet with thousands of lines of urls and 12 columns of associated data. The urls are in column A. I need to create a pivot table based on the labels that I am trying great by searching in the string for a set of keywords and then assign a label in the adjacent cell in column B. Can you help me with a solution? Many thanks.
TMcInnis

 

[Jonmo1]

I don't understand in what way that suggestion doesn't help.

You have a bunch (thousands) of text strings (URLs) in column A
You want to search each of those text strings for a group of KeyWords and assign a value (label) in column B based on which keyword is found in the text string.

That's exactly what the formula does.
You only have to build the table of keywords/labels in an available area of your sheet.

 

[TMCINNIS]

Jonmo1

Thank you. It worked. I was so firmly entrenched in the path I was going down that I needed to step back and look out of the box. You did that for me. However I needed to add one small refinement to remove the #N/A error. THis is the formula that worked.

=IFERROR(LOOKUP(2^15,SEARCH($A$2:$A$22,A26),$B$2:$B$22),"")

Thank you very much for your help.

 

[Jonmo1]

You're welcome.

A couple things to understand about that formula.
First, there cannot be any blanks in A2:A22, they will produce false positives.
2nd, if 2 or more keywords are found in the string, it returns based on the one that is listed LAST in A2:A22.
So if the string contains both "abc" AND "xyz", and you want specifically "abc" to the the one to take priority, then "abc" should be listed 'below' "xyz" in the list.