How to strip subdirectories out of URL's

[BlueFoxx]

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:

• https://stackoverflow.com/questions/20431870/extract-subdirectory-from-a-url-in-excel
• https://www.mrexcel.com/forum/excel-questions/743815-extracting-subdirectory-subfolder-url-url.html

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

 

[Brombrough]

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

 

[BlueFoxx]

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?