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]

Oh, OK. Thanks. But the problem is the table is gone now so I think it'd be hard for Rick to understand...Can you put that back in there? Thanks!

 

[Zack Barresse]

No, and please stay in your own thread. I purposefully moved your posts to your own thread. I put a link to it in this thread.

 

[lwcorp]

Sorry for bumping it 5 years later, but this was the best formula for this question that I've found online! Just one downside - it doesn't 1 word TLDs like www.this.co or test.this.co - can you add support to them?

 

[Rick Rothstein]

Sorry for bumping it 5 years later, but this was the best formula for this question that I've found online! Just one downside - it doesn't 1 word TLDs like www.this.co or test.this.co - can you add support to them?

It is not clear from your post which formula you were thinking was the "best formula for this question", but I would note that the formula I posted in Message #19 returns "this.co" and "test.this.co"for you two URLs which is what the OP for this thread would have been looking for... were you wanting a different response? If so, what answers would you like for those two? Depending on your answer, I will probably have follow up questions.

 

[lwcorp]

Sorry, the formula I meant was in post #16. I clicked reply under it and thought it will be shown.
Your post #16's formula was much better in my eyes than your post #19's! Your post #19's formula doesn't strip any subdomains other than www. For example, no support for this.foobar.com