Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Hillsborough, California, USA
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Excel 2010: Mass extraction of URLs from hyperlinks

    I have a problem that I think is the opposite of one solved here. I have several thousand lines of data in Excel (Excel 2010) each with a single cell containing some text and also a hyperlink to a URL that gives the source of the text. It was straightforward to pull out all the text names, but now I'd like to pull out the URL's from the hyperlinks and list them in a separate column as straight text. I can do this one line at a time: Ctrl-k | Alt-e | Ctrl-c | TAB | Enter | [move to the right] |Ctrl-v to paste it in the other cell. I've tried to embed this method in a macro (i.e., containing the above commands plus "go back to the left and then one cell down"). That works great, but just one time. Then, the result for the paste is always the URL from the very first hyperlink pasted, not updated to be the URL from succeeding cells.

    I'd like to solve this using traditional Excel commands and macros and without using VBA if that's at all possible, but thanks for any help.
    Last edited by richlane; 2014-11-21 at 23:20.

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,270
    Thanks
    46
    Thanked 248 Times in 228 Posts
    I'd like to solve this using traditional Excel commands and macros and without using VBA if that's at all possible
    rich,

    Macro=VBA. If all else fails to find a non-VBA method, the following code will get the URLs from the hyperlinks in Column A and paste it in the adjacent cell. Paste the code into a standard module. Once the task has completed, you always have the option to delete the code and/or save it as a non-macro enabled workbook.

    HTH,
    Maud

    hyperlinks1.png

    Code:
    Public Sub GetHyperlinks()
    On Error Resume Next
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    For I = 2 To LastRow
        Cells(I, 2) = Cells(I, 1).Hyperlinks(1).Address
    Next I
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-11-22 at 01:40.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2014
    Posts
    122
    Thanks
    1
    Thanked 11 Times in 11 Posts
    http://www.ibm.com/us/en/
    use this
    =IF(ISERROR(FIND("//www.",J3)), MID(J3,FIND(":",J3,4)+3,FIND("/",J3,9)-FIND(":",J3,4)-3), MID(J3,FIND(":",J3,4)+7,FIND("/",J3,9)-FIND(":",J3,4)-7))
    to get
    ibm.com

  4. #4
    Lounger
    Join Date
    Sep 2002
    Location
    Hillsborough, California, USA
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Maud: Brilliant, thanks! Worked the first time. For me, a macro is just something that plays back my keystrokes! VBA is way over my head, even though I've used Excel constantly since 1988 (pre-Windows). But following your instructions gave me hope.

    dguillett: thanks also, although that wasn't exactly my problem!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •