Remove all characters from a url except the root domain and domain extension

[christojuan]

Hi - This formula takes any url and removes everything that I want except that it does not remove the forward slash and all characters after that.

=IFERROR(MID(B10, FIND("^", SUBSTITUTE(B10,".","^", LEN(B10)-LEN(SUBSTITUTE(B10, ".", ""))-IF(ISNUMBER(SEARCH(OR(".co.",".com."), B10)), 2, 1)))+1, 100),B10)

The formula evaluates the items in the left column to the result in the right, but what I am looking for is for every url example in the left column to generate "woohoo.com" as a result of the formula in the right column, regardless of the top level domain extension (.com, .ru. .co, .net, .jp etc -in those cases it would generate woohoo.com, woohoo.ru, woohoo.net, woohoo.jp etc.).


http://www.bob.woohoo.com woohoo.com
http://www.bob.woohoo.com/ woohoo.com/
http://www.bill.woohoo.com woohoo.com
http://www.bill.woohoo.com/folder1 woohoo.com/folder1
http://www.bill.woohoo.com/folder1/ woohoo.com/folder1/
http://www.bill.woohoo.com/folder1/folder2 woohoo.com/folder1/folder2
http://www.bill.woohoo.com/folder1/folder2/ woohoo.com/folder1/folder2/
http://bill.woohoo.com woohoo.com
www.bill.woohoo.com woohoo.com
bill.woohoo.com woohoo.com


I would really appreciate help getting that formula to achieve that (if it is possible).


Thanks very much in advance!

 

[waldoctg]

Hi Rick,

Thanks for the help. Could you make it so it strips https:// and https://www. as well - I still want the http:// and http://www.? Also, can you remove the part that gets rid of the subdomain - I want the sub domains? Thanks a LOT!!!

 

[Rick Rothstein]

Hi Rick,

Thanks for the help. Could you make it so it strips https:// and https://www. as well - I still want the http:// and http://www.? Also, can you remove the part that gets rid of the subdomain - I want the sub domains? Thanks a LOT!!!

What is the "sub-domain"? Please show a sample of before and after for a URL with a subdomain in it.

 

[waldoctg]

subexample.example.com

 

[Rick Rothstein]

subexample.example.com

I just noticed that you are not the original poster for this thread. Given that, I think you should post several before and after examples so that we can see exactly the kind of text you have for existing data and exactly how you want it the resulting output to appear.

 

[waldoctg]

OK here you go. Does this make sense?

http://bob.woohoo.com	bob.woohoo.com
www.bob.woohoo.com/	bob.woohoo.com
http://www.bill.woohoo.com	bill.woohoo.com
https://www.bill.woohoo.com/folder1	bill.woohoo.com
http://bill.woohoo.com/folder1/	bill.woohoo.com
http://www.bill.woohoo.com/folder1/folder2	bill.woohoo.com
http://www.bill.woohoo.com/folder1/folder2/	bill.woohoo.com
http://bill.woohoo.com	bill.woohoo.com
www.bill.woohoo.com	bill.woohoo.com
bill.woohoo.com	bill.woohoo.com
http://portal.hud.gov/hudportal/HUD/states	portal.hud.gov
http://mapquest.com/	mapquest.com
http://historymatters.gmu.edu/browse/refdesk/	historymatters.gmu.edu
http://blast.ncbi.nlm.nih.gov/	blast.ncbi.nlm.nih.gov
http://www.tfl.gov.uk/modalpages/2605.aspx	tfl.gov.uk
http://www.constantcontact.com/features/tours.jsp	constantcontact.com

<tbody>
</tbody>

 

[Rick Rothstein]

OK here you go. Does this make sense?

http://bob.woohoo.com	bob.woohoo.com
www.bob.woohoo.com/	bob.woohoo.com
http://www.bill.woohoo.com	bill.woohoo.com
https://www.bill.woohoo.com/folder1	bill.woohoo.com
http://bill.woohoo.com/folder1/	bill.woohoo.com
http://www.bill.woohoo.com/folder1/folder2	bill.woohoo.com
http://www.bill.woohoo.com/folder1/folder2/	bill.woohoo.com
http://bill.woohoo.com	bill.woohoo.com
www.bill.woohoo.com	bill.woohoo.com
bill.woohoo.com	bill.woohoo.com
http://portal.hud.gov/hudportal/HUD/states	portal.hud.gov
http://mapquest.com/	mapquest.com
http://historymatters.gmu.edu/browse/refdesk/	historymatters.gmu.edu
http://blast.ncbi.nlm.nih.gov/	blast.ncbi.nlm.nih.gov
http://www.tfl.gov.uk/modalpages/2605.aspx	tfl.gov.uk
http://www.constantcontact.com/features/tours.jsp	constantcontact.com

<tbody>
</tbody>

In your first post you said, "Could you make it so it strips https:// and https://www. as well - I still want the http:// and http://www.?" In your above examples, you show the http:// and http://www. being removed but the red highlighted part from in the quote says you want to keep them... please clarify.

 

[waldoctg]

I meant to say I still want to strip the http:// and http://www. Sorry!

 

[waldoctg]

Does that make sense? Thanks!

 

[Rick Rothstein]

I think this formula will do what you want...

[table="width: 500"]
[tr]
	[td]=TRIM(MID(SUBSTITUTE(REPLACE(MID(SUBSTITUTE("//"&A1,"//www.","//"),3,500),1,IFERROR(FIND("//",A1)+1,0),""),"/",REPT(" ",500)),1,500))[/td]
[/tr]
[/table]

 

[Zack Barresse]

Moved waldoctg's post to new thread.