Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy only hyperlink to a new cell | Excel Discussion (Misc queries) | |||
converting hyperlink to actual web address | Excel Discussion (Misc queries) | |||
Look up actual files from a list in excel and enter as a hyperlink | Excel Discussion (Misc queries) | |||
Copy hyperlink from one cell to/as hyperlink in another cell | Excel Worksheet Functions | |||
Hyperlink-Programable replacement of Text with Its Actual Address | Excel Worksheet Functions |