Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Hillsborough, California, USA
    Posts
    30
    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
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 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
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 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
    30
    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!

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Rich,

    If you were to record a macro, you would find the actions you took converted to code and located in a module in the VB editor by pressing Alt-F11.

  6. #6
    Lounger
    Join Date
    Sep 2002
    Location
    Hillsborough, California, USA
    Posts
    30
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Maud, yes, thanks. I'm not arguing with you. I've seen code modules and even done some (very light) edits to my recorded macros. I've just never written any VBA code per se, just as code. But besides that, I'm getting enormous work kudos for solving a giant problem the group had. I did a bunch after using your code, but your code was crucial, and now everybody wants a copy of my full spreadsheet, so thanks for that too!

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    For me, a macro is just something that plays back my keystrokes!
    Rich,

    I didn't think you were arguing It was just an FYI as I wasn't sure if you knew where/how your macros were being recorded. Very glad I was able to help.

    Maud

Posting Permissions

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