Extracting Subdirectory/Subfolder URL in a URL

[lumineers01]

I'd like to do the following in Excel using one formula:

http://subdomain.domain/subdirectory/url.php --> subdomain.domain/subdirectory
https://subdomain.domain/url.php --> subdomain.domain/url.php
http://www.subdomain.domain/subdirectory/url.php --> subdomain.domain/subdirectory

So basically, I only need subdomain.domain/directory

I already searched for formulas or add ins online but can't seem to find any formula for this. I only found a formula for subdomain.domain and url location.


I'm currently trying to modify these 2 formulas I got from nice people in another website, but I just can't figure out how!:



FIRST FORMULA:


<code><code></code>

=SUBSTITUTE(SUBSTITUTE(LEFT(A1,FIND("@",SUBSTITUTE(A1,"/","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))/LEN("/")))-1),"http://www.",""),"https://www.","")</code><code></code>

SECOND FORMULA:

<code><code><code></code>

=MID(A1,8,FIND("/",MID(A1,8,LEN(A1)),FIND("/",MID(A1,8,LEN(A1)))+1)-1)<code></code>

</code>
</code>[TABLE="class: grid, width: 10, align: left"]
<tbody>[TR]
[TD]URL
[/TD]
[TD]FORMULA 1
[/TD]
[TD]FORMULA 2
[/TD]
[TD]WHAT I WANT:
[/TD]
[/TR]
[TR]
[TD]https://www.stlukes-hospice.org.uk/useful-links-for-healthcare-professionals
[/TD]
[TD]stlukes-hospice.org.uk
[/TD]
[TD]/www.stlukes-hospice.org.uk
[/TD]
[TD]stlukes-hospice.org.uk/useful-links-for-healthcare-professionals
[/TD]
[/TR]
[TR]
[TD]http://www.leaguelineup.com/links.asp?url=richmond_ruckus
[/TD]
[TD]leaguelineup.com
[/TD]
[TD]#VALUE!
[/TD]
[TD]leaguelineup.com/links.asp?url=richmond_ruckus
[/TD]
[/TR]
[TR]
[TD]http://www.komenvablueridge.org/understanding-breast-cancer/local-resources/
[/TD]
[TD]komenvablueridge.org/understanding-breast-cancer/local-resources
[/TD]
[TD]www.komenie.org/understanding-breast-cancer
[/TD]
[TD]komenvablueridge.org/understanding-breast-cancer
[/TD]
[/TR]
</tbody>[/TABLE]
<code>

</code>

Any help will be highly appreciated.

I want the formula to return the subdomain.domain/subdirectory of a url if there's a subdirectory and subdomain.domain/url.php (or the original url without http:// or https: //) if it doesn't have any subdirectory or subfolder

For the second formula, someone said if I only want the first slash wrap it in SUBSTITUTE instead of MID/LEN? I can't figure it out! I'm no genius when it comes to formulas in Excel. :-/

 

[pgc01]

Hi
Welcome to the board

Try:

=TRIM(LEFT(SUBSTITUTE(MID(A2,FIND("//",A2)+2+IF(ISNUMBER(SEARCH("//www.",A2)),4),LEN(A2))&"/","/",REPT(" ",999),2),999))

 

[lumineers01]

Thank you so much! This works really great!

 

[pgc01]

You're welcome. Thanks for the feedback.

 

[comicosp]

Hi, this worked phenomenally, but how would I modify the formula to exclude the domain name? let's say instead of example.com/subfolder I just really wanted /subfolder. Thanks!