How to strip subdirectories out of URL's

BlueFoxx

New Member
Joined
Oct 24, 2018
Messages
2
Hi there,

I've been searching for solutions to delete / strip a section in a URL that is a subdirectory. So for example: https://www.domain.com/directory1/directory2/url-remaning.html needs to become https://www.domain.com/url-remaining.html.

I've been reading these posts:



But they don't give me the result I want. I'm not a formula specialist yet, but I do want to learn how this is done.

Help to get me started is appreciated!

Thank you.

Sander
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi BlueFoxx,

Some VBA to help you in your quest.

Cell A2 on a Sheet gives Full Address of URL
Cell B2 formula should be =FirstLastAddress($A2)


But First, you need to add a VBA Module and included the following Function. Not the neatest function but it does the Job.

Public Function FirstLastAddress(strAdd As String) As String
Dim arrAddress() As String
Dim strLast As String
Dim strTemp As String
Dim strFirst As String
Dim strTotal As String
Dim lngDoubleSlash As Long


arrAddress = Split(strAdd, "/")

'get last name

strLast = arrAddress(UBound(arrAddress))

'get First Name

lngDoubleSlash = InStr(1, strAdd, "//")

strTemp = Mid(strAdd, (lngDoubleSlash + 2), Len(strAdd))

arrAddress = Split(strTemp, "/")

strFirst = arrAddress(LBound(arrAddress))

'put all parts together

strTotal = Left(strAdd, (lngDoubleSlash + 1)) & strFirst & "/" & strLast

'send information back to function
FirstLastAddress = strTotal

End Function


Now you have the Function you can call it from anywhere within the workbook for any URL

Hope this helps.

Brombrough
 
Upvote 0
Hi Brombrough,

Thanks man! I think I have to translate it to the Dutch version but I'll work that out. I must learn more about VBA. So this is basicly chopping it into pieces and getting the parts that I want right?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top