Extract URLs from an excel spreadsheet cell using UDFs

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.

Chandoo

Hello Awesome...

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.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

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


« »