Copy actual URL from hyperlink cell

[IAN]

I have a spreadsheet with thousands of records. One column contains
hyperlinks either created manually using the Insert->Hyperlink action
or via a =HYPERLINK() dynamic formula.

I need to return the URL for the cyperlink column into another column
so that I can use it for some other purpose. The new cells would
simply contain text like http://website/folder/file.htm.

I couldn't find a function that returned the URL, so I'm wondering if
there is another way to get the info? A macro possibly?

Thanks for your help
IAN

 

[Nick Hodge]

Ian

Not sure if I understand, but the code below will iterate a pre-selected
range and if there is a hyperlink will place it's address 2 columns to the
right (change as necessary)

Sub MoveHyper()
Dim myCell As Range
Dim hyLink As Hyperlink

For Each myCell In Selection
If myCell.Hyperlinks.Count > 0 Then
Set hyLink = myCell.Hyperlinks(1)
myCell.Offset(0, 2).Hyperlinks.Add myCell.Offset(0, 2),
hyLink.Address
Set hyLink = Nothing
End If
Next myCell
End Sub

--

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

 

[papou]

Hello Ian
You don't say whether you actually want the other column to show URL value
as hyperlink?
Anyway amend this code and try and see if it suits your needs:

Sub PlaceUrls()
Dim rg As Range
For Each rg In Range("C1:C10")
If rg.Hyperlinks.Count <> 0 Then
rg.Offset(0, 1).Value = rg.Hyperlinks(1).Address
End If
Next rg
End Sub

HTH
Cordially
Pascal

 

[Guest]

Here is a function to put in the other column. Will handle both Inserted
hyperlinks and SIMPLE hyperlink functions:

Function hyp(r As Range) As String
Dim s As String

If r.HasFormula Then
s = r.Formula
MsgBox (s)
s_array = Split(s, Chr(34))
hyp = s_array(1)
Else
hyp = r.Hyperlinks(1).Address
End If
End Function