Remove all characters before domain & subdirectory in URL

[RD_MG]

Similar to this thread: http://www.mrexcel.com/forum/excel-...-url-except-root-domain-domain-extension.html

I'm looking for a formula within Excel to remove all characters before the domain. Below are a couple of examples as the list will be ever-growing, and will also be paired up with another column to match specific strings of data.


[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Example URL String[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]https://www.example.com[/TD]
[TD]example.com[/TD]
[/TR]
[TR]
[TD]https://example.uk/[/TD]
[TD]example.uk/[/TD]
[/TR]
[TR]
[TD]http://www.example.com/sub123[/TD]
[TD]example.com/sub123[/TD]
[/TR]
[TR]
[TD]http://example.com/sub123/example/[/TD]
[TD]example.com/sub123/example/[/TD]
[/TR]
</tbody>[/TABLE]








Ultimately I'm looking to address the different variations of https & http, as well as www and non-www. Plus any variation of country codes, with or without an ending forward slash that doesn't get removed if it is present.

Any help would be greatly appreciated!

 

[Eric W]

Welcome to the forum.

If I read this correctly, your issue is a bit simpler than the other thread. This might work:

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Example URL String[/TD]
[TD="bgcolor: #FAFAFA"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]https://www.example.com[/TD]
[TD="bgcolor: #FAFAFA"]example.com[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]https://example.uk/[/TD]
[TD="bgcolor: #FAFAFA"]example.uk/[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]http://www.example.com/sub123[/TD]
[TD="bgcolor: #FAFAFA"]example.com/sub123[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]http://example.com/sub123/example/[/TD]
[TD="bgcolor: #FAFAFA"]example.com/sub123/example/[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"https://",""),"http://",""),"www.","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott Huish]

Also:
=SUBSTITUTE(MID(A2,FIND("//",A2&"//")+2,2083),"www.","")

I went with 2083 because after a little research, that seems to be the longest URL supported by any browser.

 

[RD_MG]

Welcome to the forum.

If I read this correctly, your issue is a bit simpler than the other thread. This might work:

	A	B

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Example URL String[/TD]
[TD="bgcolor: #FAFAFA"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA"]https://www.example.com[/TD]
[TD="bgcolor: #FAFAFA"]example.com[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA"]https://example.uk/[/TD]
[TD="bgcolor: #FAFAFA"]example.uk/[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA"]http://www.example.com/sub123[/TD]
[TD="bgcolor: #FAFAFA"]example.com/sub123[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA"]http://example.com/sub123/example/[/TD]
[TD="bgcolor: #FAFAFA"]example.com/sub123/example/[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"https://",""),"http://",""),"www.","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for the warm welcome and I was certainly overthinking things! This worked and I truly appreciate it!

Also:
=SUBSTITUTE(MID(A2,FIND("//",A2&"//")+2,2083),"www.","")

I went with 2083 because after a little research, that seems to be the longest URL supported by any browser.

This worked as well, thanks!