Copy actual URL from hyperlink cell
 

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

Copy actual URL from hyperlink cell
 

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
DTHIS
web: www.nickhodge.co.uk
blog (non tech): www.nickhodge.co.uk/blog


"IAN" wrote in message
ups.com...
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

Copy actual URL from hyperlink cell
 

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

"IAN" a écrit dans le message de news:
...
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

Copy actual URL from hyperlink cell
 

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

--
Gary''s Student - gsnu200749


"IAN" wrote:

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