Extract only certain part of text from a cell

Hi guys, need your help!

I have a list of over 500 000 webpages. Some of them are listed with http:// or https://, some have www. and some don't, some have .com/somethinghere and some don't.

Webpages are different (from different domains, some are subdomains, etc).

Thing is that I need a formula that would remove everything except the actual domain name. For example out of this:

http://www.examplesite.com/whateverhere or;
http://examplesite.site.com/verylong...ndomcharacters or
examplesite.com/whatever

I need to get this

examplesite.com - no https, no forward slash, no www - plain domain name.

I have found a temporary solution using text-to-cell with "/" as separator together with SUBSTIUDE, but going forward I will have much more such lists and I need to create template.

Thank you!

Hi Martin,

Will try now, as soon as my file is done with recalculations. With such massive amount of data it takes up to 10 minutes sometimes...

Just for you to know, how I did it in the first place. I made it in several steps:

1. Used LEFT with Search to get rid of anything after third "/", ones that comes just after .com (or .any)

2. Used Text to Cells where I used "/" as separator to split the whole URL (split http, name of the site and last /)

3. used multiple SUBSTITUDE to get rid of www. and ww. (I had some sites with only two "ww")

Martin it works as a charm! Thank you my friend! Just one thing - where should I add another SUBSTITUTE in case if I have sites with "ww." instead of "www." ?

Thanks a lot!

Thank you very much!!!

Hi Martin,

I noticed one thing: in case when domain name is missing last forward slash (ex -nameofsite.com instead of nameofsite.com/) then formula returns #value! error). If I add forward slash manually everything works. Can you help me with this one?

Thanks!

Originally Posted by martindwilson

Formula:

Please Login or Register  to view this content.

Hi Martin,

it returns 0 now in all cases..

I think I got to the point where I made a mistake, will check again and let you know!

Originally Posted by Dimitripros

I think I got to the point where I made a mistake, will check again and let you know!

Found it, it was a lame one, I forgot to change the cell number...
Thanks Martin, have a good day.