Tips & Tricks 111 – Extract URLs from Hyperlinks

In Excel, sometimes you encounters words which are actually Hyperlinks. Say a cell contains a word Microsoft and you notice that this is in Blue and when you click it, it takes you to http://www.microsoft.com. Another cell contains, the word Latest Yahoo Movie Blockbuster and when you click it, it takes you to http://www.yahoo.com/movies#LatestBlockbuster

Now, how to extract this. If there are very few entries like this, you can simply right click the cell > Edit Hyperlink > Copy the URL

But, you have a bunch of entries like this and you need to extract the URLs. For this, the only method is to use a simple piece of code. Don't be frighten even if you have never touched VBA before. Simply follow the following steps –

1. ALT+F11 or Right Click on tab name > View Code to open VBA window.
2. Now locate your workbook on the left side in Project Explorer window though it should be visible by default.
3. Right click on your Workbook Name in Project Explorer window > Insert Module
4. Double click on the module inserted and copy and paste following code

Function GetURL(Rng As Range) As String
    If Rng(1).Hyperlinks.Count Then
        GetURL = Rng.Hyperlinks(1).Address
        If Len(Rng.Hyperlinks(1).SubAddress) > 0 Then
            GetURL = GetURL & "#" & Rng.Hyperlinks(1).SubAddress
        End If
    Else
        GetURL = ""
    End If    
End Function

5. Now, in your sheet you can use GetURL as a function like a regular function. If your Hyperlink is in A1, you can write =GetURL(A1) to extract

Credit – http://blog.contextures.com/archives/2010/12/13/get-the-url-from-an-excel-hyperlink/