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.
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