Is there a function to extract the URL part of a hyperlink in another cell?

[Rufus V. Smith]

Is there a function to extract the URL part of a hyperlink in another cell?

All I can see is the displayed text for the link.

if I use, for example, =A1 in another cell, all I get is a

copy of the displayed text.

What I am trying to do is automatically create a column of proper urls,
given a list

behind "displayed" urls.

Rufus

 

[Gary''s Student]

Try this tiny UDF:

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

 

[Rufus V. Smith]

Fabulous, works perfectly!

Thanks!

Rufus

Try this tiny UDF:

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

 

[Jim]

I have hyperlinks in Excel that link to a cell in another file. Your
function works, but it only gives me the name of the other file.

How do I get the cell name that the hyperlink goes to?

How do I get the worksheet that the hyperlink goes to?

Thanks for your help.

 

[Jim]

I answered my own question... I looked in the object browser under hyperlin,
and found the .address that you used.

Also noticed that there is a .subaddress . I tried this and got both the
worksheet and the cell (i.e. worksheet!cell).

I guess I don't have this question any longer.

Thanks.

Jim