shortening a URL

**Mikeda2** · 06-01-2007, 03:39 AM

Hello!

I have a very long column (almost 3000 cells) of URLs. I would like to replace them in the second column with shortened versions, basically cut them at the fourth slash.

Let me illustrate:

www.plant.edu/wm/mail/window/apple/seed

would turn into

www.plant.edu/wm/mail/window

Could you tell me how can I used functions in Excel to do it? I am not very advanced when it comes to Excel and I need help. Thank you very much.

**VBA Noob** · 06-01-2007, 04:31 AM

Does this help

=HYPERLINK(LEFT(A1,FIND("^",SUBSTITUTE(A1,"/","^",4))-1))

VBA Noob

**escelinen** · 06-01-2007, 04:57 AM

I don't see any functions to solve this. So I made little VBA-code to handle it.
Long URLs in Column A begins with row 1. Fixed URLs will be placed in Column B. Here is the code for that. Hope it's helping you.

 Sub Shorten_URL()

    Dim slash_count As Integer 'counts how many slashes has been found starting from beginning of the URL
    Dim URL_rows As Integer 'Current row counter
    Dim URL_chr As Integer 'Counts characters in URL
    
    slash_count = 0
    URL_rows = 1
        
    Do Until Range("A" & Trim(Str(URL_rows))) = ""
        For URL_chr = 1 To Len(Range("A" & Trim(Str(URL_rows))))
            If Mid(Range("A" & Trim(Str(URL_rows))), URL_chr, 1) = "/" Then
                slash_count = slash_count + 1
            End If
            If slash_count = 4 Then
                Range("B" & Trim(URL_rows)) = Left(Range("A" & Trim(URL_rows)), URL_chr - 1)
                slash_count = 0
                Exit For
            End If
        Next
        URL_rows = URL_rows + 1
    Loop
End Sub

**Mikeda2** · 06-01-2007, 05:56 AM

Thank you both of you for the fast posts.

I tried to enter the formula, VBA Noob, but it does not seem to be working. I get error message.

I will try your solutions on Monday, Escelien. I am off work now and I want to solve the problem beginning of next week.

Thank you very much again! It is great to see such fast responses.

**VBA Noob** · 06-01-2007, 06:22 AM

Here's an example

VBA Noob

**Mikeda2** · 06-03-2007, 10:38 PM

Thank you, VBA Noob. Unfortunately, I cannot open the file. Could you try posting it again?

**Paul** · 06-03-2007, 10:45 PM

If you have Windows XP, save the file to your PC and right-click on it and choose Explore. You'll then see the Excel file and can double-click to open it.

If you don't have Windows XP, you'll need a zip utility to extract the Excel file from the .zip file (such as WinZip, WinRAR, PKZip, etc.)