Extract URLs from an excel spreadsheet cell using UDFs
Often my work involves processing web page data in excel sheets. This includes extracting the hyperlinks from cell contents. There is no formula for extracting hyperlinks though, you can right click on cell and choose “edit hyperlink” to see which address the cell is linking to. But that is a tedious process especially if you are planning on using the hyperlink for something.
Here is a handy user defined function in VBA for getting hyperlinks from a spreadsheet cell:
Function getURL(forThisCell As Range) As String
'VBA UDF for getting URLs from a cell if any
retVal = ""
If forThisCell.Hyperlinks(1).Address <> "" Then
retVal = forThisCell.Hyperlinks(1).Address
getURL = retVal
Bonus tip: You can create hyperlink on a cell using “hyperlink()” spreadsheet function. The syntax is simple.
=hyperlink("http://chandoo.org/wp","Pointy Haired Dilbert") will create a link in the cell to this blog.
Leave a Reply
|Changing Sort Orientation in Excel – Sorting across columns instead of rows||Watch out if you are buying insurance online at GEICO – your password is not encrypted|