Display The Actual Link / Email Address From Links in Excel - UDF

Add to Favorites

Display the actual link or email address from links within Excel with this UDF. This user defined function pulls the actual http address or the actual email address from cells that contain links. This udf is simple to use since you just have to put the cell reference that contains the link into the range argument and hit enter. The default text argument is optional and just displays whatever you want if the cell you point to does not contain a link.

This UDF is great when you need to turn a list of imported email addresses, imported links, or a web query that contains a lot of links from the displayed links to the actual underlying values of the links. You can also easily and quickly transform lists of email addresses that are in link form (with a "mailto:" prefix) into an email address in text form.

Where to install the macro:  Module

UDF to Display The Actual Link / Email Address From Links in Excel

Function SHOWLINK(cell As Range, Optional Default As Variant)

'This UDF will display all links as text and display the full http address that is in the hyperlink
'This UDF also automatically detects if the link is an Email address and displays it correctly
If (cell.Range("A1").Hyperlinks.Count <> 1) Then

 If IsMissing(Default) Then
  SHOWLINK = "Not a Link"
  SHOWLINK = Default
 End If


 If Left(cell.Range("A1").Hyperlinks(1).Address, 7) = "mailto:" Then
  SHOWLINK = Right(cell.Range("A1").Hyperlinks(1).Address, Len(cell.Range("A1").Hyperlinks(1).Address) - 7)
  SHOWLINK = cell.Range("A1").Hyperlinks(1).Address
 End If

End If

End Function

How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.

Tutorial Details
Excel Forum