Copy text hyperlinks and paste them as source addresses?

M

Minuette

I have 1000s of text hyperlinks in a spreadhseet (are these called target
links?) and I want to display them as proper addresses (called source?) You
can see the URL address in the yellow text box that appears when I hover over
the link. Have been unable to find anything in paste special, or in the View
tab in Options to help. Know that there is a Visual Basic way but can't use
it! Help!
 
G

Gary''s Student

This one line UDF:

Function hyp2(r As Range) As String
hyp2 = r.Hyperlinks(1).Address
End Function

will return the address part of the hyperlink. So if A1 contains the
hyperlink, then
=hyp2r(A1) will return the address (URL)
 
Top