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

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"
 Else
  SHOWLINK = Default
 End If

Else

 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)
 Else
  SHOWLINK = cell.Range("A1").Hyperlinks(1).Address
 End If

End If

End Function

---