Hyperlinks

[Helen]

Scenario
I have a web page containing text with hyperlinks.
e.g.http://listings.ebay.co.uk/pool1/listings/list/all/cate
gory353/overview.html?from=R13#_top I want to transfer
this into excel so that the web link e.g.www.ebay.co.uk
shows up as oppose to the blue text with the link.

Any ideas would be much appreciated.

Helen

 

[David McRitchie]

Hi Helen,
I'm not sure of your question but will assume that the display text
of the link differs from the link url.

If these links are lined up on your web page or such as from an
exported favorites file which is an HTML file once exported, you
can paste the page into Excel.

As long as the link (link url is hidden below text) then you can
get the URL into the next column with

A1: contains an object type hyperlink (Ctrl+K)
text component: overview
url component: http://listings.ebay.co.uk/pool1/listings/list/all/category353/overview.html?from=R13#_top

B1:
=personal.xls!gethyperlink(A1) --or--
=gethyperlink(A1)
display as:
http://listings.ebay.co.uk/pool1/listings/list/all/category353/overview.html?from=R13#_top

if you just want the first part of the actual URL you can use
(will work for http:// and for https:// )

C1:
=LEFT(MID(B1,FIND("://",B1)+3,255),FIND("/",MID(B1,FIND("://",B1)+3,255))-1)
or better as
=IF(A1="","",LEFT(MID(A1,FIND("://",A1)+3,255),FIND("/",MID(A1,FIND("://",A1)+3,255))-1))

display as:
listings.ebay.co.uk

to install the following User Defined Function (UDF) se my page
Getting Started with Macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

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

The above macro can be found on my page which is 116 KB
Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url

 

[David McRitchie]

Hi Helen,
Sorry the suggested worksheet code did not match the
UDF supplied later in the reply. Change part of the example to
read:
B1:
=personal.xls!HyperLinkAddress(A1) --or--
=HyperLinkAddress(A1)
display as:
http://listings.ebay.co.uk/pool1/listings/list/all/category353/overview.html?from=R13#_top

The user defined function for the above is still:

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