Need to get the hyperlink from a cell in Excel? This can be especially helpful after you've cut and pasted a list of links from a webpage into or opened a webpage in Excel. You can easily do this by creating a User Defined Function (UDF).

Sounds more complicated than it actually is. Just complete the following steps:

  1. Open the worksheet that contains your information
  2. Press ALT-F11 to open the Visual Basic for Applications editor
  3. In the VBAProject windows, find your workbook
  4. Right click on it and select Insert… followed by Module
  5. Copy and paste the following code into the main window, right below where you see (General):
Function GetURLAddress(HyperlinkCell As Range) As String
   GetURLAddress = HyperlinkCell.Hyperlinks(1).Address
End Function

Next close this window and return to your worksheet.

In the following example, we are retrieving the URL from the hyperlink in cell A1. Be sure to change this according to your needs.

In the cell where you want the hyperlink text ot appear, enter:

=GetAddress(A1)

Note that the function will only be available in the current workbook.

Imporant Note for Office 2007 and 2010 Users

Should you wish to save this UDF with your workbook, be sure to save your file as either an .xlsm or .xls type file. The UDF function above will automatically be removed when saving it as an .xlsx type file.

Related Posts: