lumineers01
New Member
- Joined
- Dec 7, 2013
- Messages
- 44
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>
SECOND FORMULA:
<code><code><code></code>
</code>
</code>
<tbody>
</tbody><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. :-/
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>
URL | FORMULA 1 | FORMULA 2 | WHAT I WANT: |
https://www.stlukes-hospice.org.uk/useful-links-for-healthcare-professionals | stlukes-hospice.org.uk | /www.stlukes-hospice.org.uk | stlukes-hospice.org.uk/useful-links-for-healthcare-professionals |
http://www.leaguelineup.com/links.asp?url=richmond_ruckus | leaguelineup.com | #VALUE! | leaguelineup.com/links.asp?url=richmond_ruckus |
http://www.komenvablueridge.org/understanding-breast-cancer/local-resources/ | komenvablueridge.org/understanding-breast-cancer/local-resources | www.komenie.org/understanding-breast-cancer | komenvablueridge.org/understanding-breast-cancer |
<tbody>
</tbody>
</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. :-/
Last edited: