Hyperlink

[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

 

[Guest]

If this is what you want:

A B
1 Microsoft http://www.microsoft.com

then
enter =HYPERLINK(A1,"Microsoft") into A1

Regards,
GS

 

[Guest]

correction: that should read..

=HYPERLINK(B1,"Microsoft")

....coffee time!
Regards,
GS

 

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

 

[Guest]

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


Try this tiny UDF

 

[code_warrior]

worked, thanx

Is there a place I can brush up on coding for excell.

 

[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)

 

[Guest]

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.

 

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

 

[Guest]

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

 

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

 

[Guest]

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

Regards,
GS

 

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

 

[code_warrior]

Worked Thank you one and all.