Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
I need to extract the URLs from hyperlinks in 1000 rows

Answered I need to extract the URLs from hyperlinks in 1000 rows

  • Tuesday, May 01, 2012 8:55 PM
     
     
    Sign In to Vote
    0
    I know there are functions for that will read the links in column A and write the hyperlink out to column B. But I have not used VBA for so long I forgot even the basics of how to set it up. Can someone point me to what I need to get from here to where I need to be in order to extract this information from my spreadsheet?
     

All Replies

  • Tuesday, May 01, 2012 9:24 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Press Alt+F11 to activate the Visual Basic Editor.

    Select Insert | Module.

    Copy the following code into the module:

    Function GetURL(rng As Range) As String
    Application.Volatile
    If rng.Hyperlinks.Count > 0 Then
        GetURL = rng.Hyperlinks(1).Address
    End If
End Function

    Close the Visual Basic Editor.

    Let's say the first hyperlink is in A2.

    Enter the following formula in B2: =GetURL(A2)

    You can fill this formula down as far as needed.

    Remark: the formulas will not be updated automatically when the hyperlinks are edited. Press F9 to update the formulas.

    Regards, Hans Vogelaar

    • Marked As Answer by Lalewicz Wednesday, May 02, 2012 4:00 AM
    •  
     
  • Wednesday, May 02, 2012 4:00 AM
     
     
    Sign In to Vote
    0
    I cannot thank you enough. I appreciate this so much.