Grabbing URLs from a Hyperlink

  • Thread starter Thread starter smurdon
  • Start date Start date
S

smurdon

Hello everyone,

I have copy and pasted from the web into excel. It accurately copies
the hyperlinks from the website, but what I would like to do is to grab
the URL from that column.

I've tried some paste specials...but that doesn't seem to work. I know
there has to be something fairly easy to do. :)


Thanks for your help,

-Dwayne
 
Are they treated as hyperlinks in Excel

You could use a macro:

Basic approach:

Sub ShowLinks()
Dim hlnk as Hyperlink
for each hlnk in Activesheet.Hyperlinks
msgbox hlnk.Address
Next
End Sub
 
Thanks Tom,

I'll give that a try. I've not done any macro programming, but it
never hurts to learn.

What I really want is another column created on my spreadsheet that
splits the url from it's display text. In the end, I'll be processing
this with PERL.

Do you think this has to be accomplished with special programming?
I've had this problem MANY times before, but have ignored it or found a
way around it.

Seems like I should be able to do something like a paste special..or
copy the results to another column and turn on/off some flag. Perhaps
wishful thinking, huh? :)
-Dwayne
 
Sub ShowLinks()
Dim hlnk as Hyperlink
for each hlnk in Activesheet.Hyperlinks
hlnk.parent.offset(0,1).Value = hlnk.Address
Next
End Sub

If the address is being displayed, you can copy, then pastespecial and
select xlValues and the hyperlink is gone leaving the address, or you can
pastespecial, values in an adjacent cell. If the address isn't being
displayed, then this wouldn't work.

You can edit each link and use ctrl+C to copy the address from the dialog.
 
Hi Dwayne,
For what you want in Excel, don't know how that fits in with Perl, you
can use a User Defined Function (UDF).

Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

usage:
=HyperlinkAddress(B3)
=personal.xls!hyperlinkaddress(b3)

If not familiar with macros or UDF see my page getstarted.htm

For more information on obtaining different hyperlinks see
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url
 
Tom said:
If the address is being displayed, you can copy, then pastespecial
and
select xlValues and the hyperlink is gone leaving the address, or you
can
pastespecial, values in an adjacent cell. If the address isn't
being
displayed, then this wouldn't work.
Click to expand...

Unfortunately, they are not being displayed.
You can edit each link and use ctrl+C to copy the address from the
dialog.
Click to expand...

There are 18,000+ of them. I don't see me opening each and copying the
URL. :)

I'm about to try the macro above...

-Dwayne
 
That was a side note of a case where you might not need to use
the macro Tom supplied above his comment. The macro Tom supplied will
place the hyperlink address in the cell to the right using OFFSET.
The macro will select all of the hyperlinks on the sheet and place
the URL in the link to the cell to the right. You could be overwriting
data, but you are not doing any individual copying and pasting
as long as the macro you refer to is the one above the comment you
referred to. The original reply was a sample using MsgBox
to show you each link to show you it could be done -- probably not
a good thing to show you each of 16000 links. .

I would suggest using the User Defined Function that I provided
as a bit safer and more flexible -- as long as you retain the original
cells. Otherwise you will have to use copy, paste special to remove
the dependencies before you can remove the original column.
 
Back
Top