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 
