Copy actual URL from hyperlink cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

IAN - 10 Oct 2007 06:31 GMT

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

Reply to this Message

Nick Hodge - 10 Oct 2007 08:31 GMT

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

Signature

HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog

>I have a spreadsheet with thousands of records. One column contains
> hyperlinks either created manually using the Insert->Hyperlink action
[quoted text clipped - 9 lines]
> Thanks for your help
> IAN

Reply to this Message

papou - 10 Oct 2007 12:57 GMT

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

Reply to this Message

Gary''s Student - 10 Oct 2007 18:07 GMT

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

Signature

Gary''s Student - gsnu200749

> I have a spreadsheet with thousands of records. One column contains
> hyperlinks either created manually using the Insert->Hyperlink action
[quoted text clipped - 9 lines]
> Thanks for your help
> IAN

Reply to this Message