Getting the Hyperlink from Excel

I had a spreadsheet of data that contained hyperlinks. In this case, it was a series of Microsoft Knowledge Base Articles with a hyperlink associated with them. I would assume there’s an easy function in Excel to extract these, but apparently there isn’t. That’s certainly a useful function for a data person.

I turned to the handy, dandy Google and found this Q&A and Superuser. I needed to delve back into VBA and build a macro, which is easy, but seems silly. In any case, I pasted this in and then set a formula based on a cell.

And got a 0 in the field. I started to try and debug this, before trying another cell. That one worked. Apparently some of my cells, formatted as blue, underlined text, don’t really have formulas.

No big deal, but good to know.

Here’s the macro formula repeated from the post, just in case.

Function GetURL(cell As range, Optional default_value As Variant)
 'Lists the Hyperlink Address for a Given Cell
 'If cell does not contain a hyperlink, return default_value
      If (cell.range("A1").Hyperlinks.Count <> 1) Then
          GetURL = default_value
      Else
          GetURL = cell.range("A1").Hyperlinks(1).Address
      End If
End Function

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

2 Responses to Getting the Hyperlink from Excel

  1. rsterbal says:

    I’m puzzled by them not including this function. Here is where I found the write up: https://excel.tips.net/T003281_Extracting_URLs_from_Hyperlinks.html

    • way0utwest says:

      I found quite a few items like that, all showing roughly the same function and description. I am amazed this isn’t included as an Excel native function.

      That’s why I wrote this up, so I’ll remember.

Comments are closed.