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
End If
getURL = retVal
End Function
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.
3 Responses to “Extract URLs from an excel spreadsheet cell using UDFs”
HI,
I have a situation for Hyperlink.
Have done hyperlink using place in this document for list of ranges in a index sheet.
Now i want to change the sheet name using VBA.
@Ramkumar
Changing the sheet names should not impact the Hyperlinks as Excel should update them as appropriate
Have you tried?
If you have problems post the question at:
http://chandoo.org/forums/
Hi,
I am trying to extract "Sheet Name" which is an hyperlink to a cell . when I use hyperlink.address method , it returns nothing. Could you please advise any other option? Thanks
Yogesh