Matching part of a web address in another cell

[alanyip]

I Require assistance with the following problem:

In column A I have a lengthy data set of complete web addresses circa 50,000 lines long all with the domain somewhere in the middle eg:

http://movie.lagri.com.cn/alexa_2010-02-25/432
http://wopin.com.cn/rank/domainlist.cn_4.html
http://film.wopin.com.cn/alexa_2011-04-25/292
http://www.domains5.cn/view/21229.html

In column B I have the list of about 1000 domain names themselves eg:

wikipedia.com
wopin.com.cn
yahoo.com

I would like Column C to somehow show if "wopin.com.cn" was found as part of the data in any cell in Column A by saying "True" or similar.


I think that some sort of VLOOKUP is required but as the data in Column B will not exactly match any cell in Column A it always returns a False.

Can someone please help me out with the formula I need in Column C?

I am using Excel 2008 for Mac.

 

[andrewkard]

Hello. In C column:

=IsNumber(Find(B1,A1,1))

 

[alanyip]

Hi Andrew,

Great! That is much closer than I had got. But what I need is to search for the contents of B1 anywhere in A1:A50000.

If it is found just once (even there may be hundreds of cells containing the string) I need it to say "True" in C1.

Thanks.

 

[Aladin Akyurek]

I Require assistance with the following problem:

In column A I have a lengthy data set of complete web addresses circa 50,000 lines long all with the domain somewhere in the middle eg:

http://movie.lagri.com.cn/alexa_2010-02-25/432
http://wopin.com.cn/rank/domainlist.cn_4.html
http://film.wopin.com.cn/alexa_2011-04-25/292
http://www.domains5.cn/view/21229.html

In column B I have the list of about 1000 domain names themselves eg:

wikipedia.com
wopin.com.cn
yahoo.com

I would like Column C to somehow show if "wopin.com.cn" was found as part of the data in any cell in Column A by saying "True" or similar.


I think that some sort of VLOOKUP is required but as the data in Column B will not exactly match any cell in Column A it always returns a False.

Can someone please help me out with the formula I need in Column C?

I am using Excel 2008 for Mac.

Try...

C1, copy down:

=ISNUMBER(MATCH("*"&B1&"*",A:A,0))

 

[andrewkard]

OK
in D:
=MID(A1,8,Find("/",A1,8)-8)
in C:
=IsNumber(Match(B1,D:D,0))

 

[andrewkard]

=ISNUMBER(MATCH("*"&B1&"*",A:A,0))

it is good

 

[alanyip]

Gentlemen,

Magic!

Exactly what I needed. Thank you so much.

Al.