Hyperlink Display in Second Column

  • Thread starter Thread starter Richard Spangenberg
  • Start date Start date
R

Richard Spangenberg

Does anyone know how to copy the URL address from a cell
that contain a hyperlink. For example if a company name
contains a link to a website, I would like to create a
column that contains the URL that is contained within
that hyperlink.

Thanks,

Rick
 
Hi Richard,
You will need programming code:

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

=HyperlinkAddress(A2)
or
=personal.xls!HyperlinkAddress(A2)

and fill down with the fill handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm

Directions to install macros and functions in
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Some additional examples of getting hyperlinks in
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#url
 
Option Explicit
Sub testme03()

Dim myHyper As Hyperlink

For Each myHyper In Selection.Cells.Hyperlinks
With myHyper
.Parent.Offset(0, 1).Value = .Address
End With
Next myHyper

End Sub

'and another one
Sub testme04()

Dim myCell As Range

For Each myCell In Selection.Cells
With myCell
If .Hyperlinks.Count > 0 Then
.Offset(0, 1).Value = .Hyperlinks(1).Address
End If
End With
Next myCell

End Sub

Select your range and run either of these. (they each overwrite the adjacent
cell, though!)

And if you're new to macros, you can read some notes at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top