Hyperlink

C

code_warrior

This is probably a simple question but its been a while. I want to
format a cell to display the url of a hyperlink in another cell

eg

A
B
1 _Microsoft_
http://microsoft.com


The text in A1 is a hyperlink (is it a or an hyperlink). So if you
click it it will open it up in a browser. The problem is I want B1 to
diplay the url (eg. http://www.microsoft.com) of the hyperlink in A1 so
I can avaiod doing a whole lot of copying and pasting.

Thank You
 
C

code_warrior

I think I'm not explaining it well.

A
1 _Microsoft_
2

A1 is already a hyperlink. I want to A2 to show the url of A1's
hyperlink. So lets say A1's links to microsofts website, A2 should read
"http://www.microsft.com". Or maybe its not so simple. Seems simple
enough though.
 
G

Gary''s Student

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


Try this tiny UDF
 
C

code_warrior

There is a problem though. The whole point was to save some time not
copying and pasting. The function does not work for more than one cell
at a time so I have to format each cell seperately. Is there anyway I
can get around this. EG( =hyp2(B226:B231)
 
G

Gary''s Student

I am not sure how your hyperlinks are stored (column? row?)

If you have a table of hyperlinks in column A (say A1 thru A100) and you
want to get all the urls then in B1 enter:

=hyp2(A1) and then copy B1 from B2 thru B100.

So you start of a table of hyperlinks and then make next to it a table of
the urls.

Update this post if you still need help.
 
C

code_warrior

I have a table of different hyperlinks in A1 through to A200 and I want
their significant urls to show in the adjacent B column. So if I
hilight/select all the cells in the b column (B1 through to B200) and
type =hyp2(A1:A200), A1's url would displayin B1, A2's in B2, etc. I
think I need some kind of a cell loop or something.
 
G

Gary''s Student

There is another neat trick to save lots of time & effort


1. clear B1 thru B100
2. Hi-light B1 thru B100
3. enter =hyp2(A1) and use SHIFT_ENTER instead of ENTER


This should fill in all the cells (B1 thru B100) in one swell foop!!
 
C

code_warrior

That doesnt solve the problem. It doesnt populate the whole list just
the first cell. Maybe I can copy the whole list of urls in A1:A200,
paste it into B1:B200 then use a function to change the hyperlinks into
urls. Somehow I dont think you quite fully grasp the predicament I am
in.
 
G

GS

to populate the function in all the cell, use the key combination Ctrl+Enter
not Shift+Enter.

Regards,
GS
 
D

Debra Dalgleish

In step 3 of the instructions:

3. enter =hyp2(A1) and use SHIFT_ENTER instead of ENTER

it should say Ctrl+Enter, not Shift+Enter.
 
Top