Extracting portions of URLs in Excel

[TraderDavis]

I have a list of URLs that I need to sift through, such as:
facebook.com
home.facebook.com
live.fanfooty.com.au
fanfooty.com.au
desktop.spotify.com
zlink.app.spotify.com

I only need the main part of the domain (facebook.com, spotify.com, fanfooty.com.au), and there are many different variations of these duplicates. I've been trying to figure out a conditional format that will highlight the duplicate cells containing the main part (so it should highlight "home.facebook.com" and not "facebook.com"), but I think it may not be possible. I only need to highlight the cells so I can deal with them on a case-by-case basis. Perhaps this can be done in VBA?

This is as far as I got with the conditional format... it doesn't work of course.

=AND(LEN($A10)-LEN(SUBSTITUTE($A10,".",""))>=2,OR(ISERROR(SEARCH(".com.",$A10)),ISERROR(SEARCH(".co.",$A10))),NOT(ISERROR(MATCH(MID($A10,FIND("@",SUBSTITUTE(A10,".","@",LEN(A10)-(LEN(SUBSTITUTE(A10,".",""))+1)))+1,(FIND("@",SUBSTITUTE(A10,".","@",LEN(A10)-LEN(SUBSTITUTE(A10,".",""))))+1-FIND("@",SUBSTITUTE(A10,".","@",LEN(A10)-(LEN(SUBSTITUTE(A10,".",""))+1)))-1)),A:A)))

 

[István Hirsch]

Give this a try to extract in another column what you need:

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",REPT(" ",100)),(1*ISNUMBER(SEARCH(".??#",A1&"#"))+2)*100))," ",".")

Excel Workbook
	A	B
1	facebook.com	facebook.com
2	home.facebook.com	facebook.com
3	live.fanfooty.com.au	fanfooty.com.au
4	fanfooty.com.au	fanfooty.com.au
5	desktop.spotify.com	spotify.com
6	zlink.app.spotify.com	spotify.com
Sheet

 

[TraderDavis]

Thanks, this works perfectly! Could you explain how the ISNUMBER(SEARCH part works?

 

[mfexcel]

Very nice!

Give this a try to extract in another column what you need:

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",REPT(" ",100)),(1*ISNUMBER(SEARCH(".??#",A1&"#"))+2)*100))," ",".")


Excel tables to the web >> Excel Jeanie HTML 4

 

[mfexcel]

that would give you a result of either 200 or 300, depends on how your domain ends. If it ends with .??, it returns a result of 300; else, it returns a result of 200.
A nice trick to determine whether you need the final two words or three words.

Thanks, this works perfectly! Could you explain how the ISNUMBER(SEARCH part works?

 

[István Hirsch]

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"."," ")," ",REPT(" ",100)),(1*ISNUMBER(SEARCH(".??#",A1&"#"))+2)*100))," ",".")

The inner Substitute converts each period to space, the outer Substitute converts each space to 100 spaces, so each part of the Url is separated with 100 spaces. So 100 characers from the right embeds the last part, 200 the last two parts, 300 the last three parts of the URL.

It is supposed that if the Url ends in „.??#” (two characters after the last period), you need three parts to keep, if it does not end with that then you need only the last two parts to extract. Search for „.??#” gives a numeric value in the former case, so 1*Isnumber will be 1, 1+2 = 3, so 3*100 = 300 characters will be extracted from the right end. If Search cannot find the „.??#” string then Isnumber and 1*isnumber will be zero, zero +2 will be 2, and 2*100 characters will be extracted from the right end.
The string „.??” alone would be also found at other locations of the URL, for example .fa(cebook.com) therefore „#” is put to the end of A1 to restrict the search for „.??” (period followed by two characters) only to the end of the string in A1.
Trim deletes the excess spaces, the wrapping Substitute converts the spaces to periods.