Get the URL from a hyperlink in Excel

Recently I needed to extract a links from a web page; normally I would use some Linux commands ported to windows and get the job done. In the end I needed to format the relative link to a full URL, so I decided to use Excel. However when I imported the web page into excel I found that the URL was embedded in the friendly name of the hyperlink.

The hyperlink command in excel actually does the reverse of what I needed; it creates a hyperlink out of the URL and a friendly name and I needed the URL from the friendly name. So I dug in a little deeper and treated the URL like an object. It has properties and those properties can be exposed. The hyperlinkcell object has a type of range so it is passed to the function of GETURL. The first hyperlink in the range we will want the property of the address. Then we simply pass it back to the function so that we can display it.

To install this all you need to do is:

  1. Hit ALT + F11 (Opens Visual Basic Editor)
  2. Click on Insert -> Module (adds a module to your excel file)
  3. Paste the code below for the function of GETURL
  4. Hit ALT + Q (Closes the Visual Basic Editor)

Now use the =GETURL(cell) to get the URL

Example: =GETURL(A1) will return the URL for the Hyperlink displayed in cell A1

Function GETURL(HyperlinkCell As Range)

 GETURL = HyperlinkCell.Hyperlinks(1).Address

End Function

45 thoughts on “Get the URL from a hyperlink in Excel”

  1. Krozyk

    Thanks a lot. I wasn’t able to import hyperlinks from excel to access till now . I wonder why MS don’t have a function doing this… ๐Ÿ™

  2. Fred

    Thanks so much for this. I wish I would have searched for it yesterday, but today it saved me a lot of time.

  3. Bohack Post author

    Starting with Excel 2007 Macros are disabled by default. If you create the macro it will be enabled until it is saved for the first time. It will also need to be saved as an XLSM (macro enabled) Excel document. The next time it is opened a dialog box will ask you to enable the macro. The dialog box is under the formula bar of the spreadsheet. Hope that helps, but that is the behavior when saving the excel and macro.

  4. Jim McSpiritt

    Unfortunately, this only works for web addresses. Is there a way to extract a path on a local computer from the hyperlink? Thanks.

  5. Bohack Post author

    I just tested the code and it seems to work on the URLs as well. Since the hyperlink is just a link redirecting either the web browser or explorer shell to another location.

  6. Jan Van Winckel

    I also have to thank you for this macro & solution in order to retrieve the url from hyperlinks.

    Is there a possibilty to create a macro that gives you the name of the website out of the hyperlink?
    It is the name that you can see when you hit the hyperlink with the cursor.

    I’m looking forward to find this.

  7. Jan Van Winckel

    Hello everyone,

    I found allready the answer/solution to my question.

    If anyone is interested in applying it, you only have to create another module with the same content as the previous one, but replace “Address” with “Screentip”. It’s as simple as that.

  8. Zach

    So i did everything you said and i got what the the underlying hyperlink said, mine being an email address. However i cant copy all of them or edit them in any way is there any way to do that?

  9. Jane

    This is great!! I’ve looked for a solution to this problem off and on for a few years, and solutions have always been complex with inconsistent results. This is super easy to use and works perfectly. Thanks so much!!

  10. Friar

    Thanks Bohack. I love the background music. Couldn’t make out the song though. May I ask what it is ?

    Peace.

  11. Mike

    Thanks for providing such a perfect solution, with concise and clear instructions for getting this to work!

Comments are closed.