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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s