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
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
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.