software

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 quot;displayedquot; urls.

RufusTry this tiny UDF:

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

--
Gary's Studentquot;Rufus V. Smithquot; wrote:

gt; Is there a function to extract the URL part of a hyperlink in another cell?
gt;
gt; All I can see is the displayed text for the link.
gt;
gt; if I use, for example, =A1 in another cell, all I get is a
gt;
gt; copy of the displayed text.
gt;
gt; What I am trying to do is automatically create a column of proper urls,
gt; given a list
gt;
gt; behind quot;displayedquot; urls.
gt;
gt; Rufus
gt;
gt;
gt;
gt;
gt;

Fabulous, works perfectly!

Thanks!

Rufus

quot;Gary''s Studentquot; gt; wrote in message
...
gt; Try this tiny UDF:
gt;
gt; Function hyp2(r As Range) As String
gt; hyp2 = r.Hyperlinks(1).Address
gt; End Function
gt;
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Rufus V. Smithquot; wrote:
gt;
gt;gt; Is there a function to extract the URL part of a hyperlink in another
gt;gt; cell?
gt;gt;
gt;gt; All I can see is the displayed text for the link.
gt;gt;
gt;gt; if I use, for example, =A1 in another cell, all I get is a
gt;gt;
gt;gt; copy of the displayed text.
gt;gt;
gt;gt; What I am trying to do is automatically create a column of proper urls,
gt;gt; given a list
gt;gt;
gt;gt; behind quot;displayedquot; urls.
gt;gt;
gt;gt; Rufus
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt;
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.

quot;Gary''s Studentquot; wrote:

gt; Try this tiny UDF:
gt;
gt; Function hyp2(r As Range) As String
gt; hyp2 = r.Hyperlinks(1).Address
gt; End Function
gt;
gt; --
gt; Gary's Student
gt;
gt;
gt; quot;Rufus V. Smithquot; wrote:
gt;
gt; gt; Is there a function to extract the URL part of a hyperlink in another cell?
gt; gt;
gt; gt; All I can see is the displayed text for the link.
gt; gt;
gt; gt; if I use, for example, =A1 in another cell, all I get is a
gt; gt;
gt; gt; copy of the displayed text.
gt; gt;
gt; gt; What I am trying to do is automatically create a column of proper urls,
gt; gt; given a list
gt; gt;
gt; gt; behind quot;displayedquot; urls.
gt; gt;
gt; gt; Rufus
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt;

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

quot;Jimquot; wrote:

gt; I have hyperlinks in Excel that link to a cell in another file. Your
gt; function works, but it only gives me the name of the other file.
gt;
gt; How do I get the cell name that the hyperlink goes to?
gt;
gt; How do I get the worksheet that the hyperlink goes to?
gt;
gt; Thanks for your help.
gt;
gt; quot;Gary''s Studentquot; wrote:
gt;
gt; gt; Try this tiny UDF:
gt; gt;
gt; gt; Function hyp2(r As Range) As String
gt; gt; hyp2 = r.Hyperlinks(1).Address
gt; gt; End Function
gt; gt;
gt; gt; --
gt; gt; Gary's Student
gt; gt;
gt; gt;
gt; gt; quot;Rufus V. Smithquot; wrote:
gt; gt;
gt; gt; gt; Is there a function to extract the URL part of a hyperlink in another cell?
gt; gt; gt;
gt; gt; gt; All I can see is the displayed text for the link.
gt; gt; gt;
gt; gt; gt; if I use, for example, =A1 in another cell, all I get is a
gt; gt; gt;
gt; gt; gt; copy of the displayed text.
gt; gt; gt;
gt; gt; gt; What I am trying to do is automatically create a column of proper urls,
gt; gt; gt; given a list
gt; gt; gt;
gt; gt; gt; behind quot;displayedquot; urls.
gt; gt; gt;
gt; gt; gt; Rufus
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;