Extract URLs from an excel spreadsheet cell using UDFs

Posted on July 23rd, 2008 in hacks , Learn Excel , technology - 3 comments

extract-hyperlinks-from-cell-excel-spreadsheet-formulaOften 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.

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

3 Responses to “Extract URLs from an excel spreadsheet cell using UDFs”

  1. Ramkumar says:

    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.

  2. Yogesh says:

    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

Leave a Reply