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.
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
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 »|