Extracting hyperlinks

[Guest]

Hi,

I have got an excel spreadsheet which has 500+
hyperlinks. I need to extract the address behind the text
to be able to import the actual address into a db.

e.g cell A1 has "click here" with http:/www.xyz.com
behind it. I need to be able to copy the cell "click
here" and paste it (special?) so that what i am pasting is
http:/www.xyz.com. Paste value doesn't seem to work nor
formula.

Cheers

 

[Paul Falla]

Hi there

Assuming that the hyperlinks are in the same column
(starting in A1) the following code will place the address
of the hyperlink in the cell directly to the right of the
hyperlink.

Sub GetHyperlinkAddress()
'Code snippet courtesy of Paul Falla'
Dim HyperlinkAddress As String
On Error Resume Next
Range("A1").Activate
While Not IsEmpty(ActiveCell)
HyperlinkAddress = ActiveCell.Hyperlinks(1).Address
ActiveCell(, 2) = HyperlinkAddress
ActiveCell.Offset(1, 0).Select
Wend

End Sub


It may be an idea to cut and paste the thyperlinks on to a
new worksheet to ensure that your original data remains
intact.

Hope this helps

Paul Falla

 

[Dave Peterson]

Another way (quite similar code) is to use a function that you can use in a cell
in the worksheet.

Saved from a previous post:

One way to extract those URL's from a hyperlink is with a userdefinedfunction.

Here's one that may help:

Option Explicit
Function GetURL(Rng As Range) As String
Application.Volatile

Set Rng = Rng(1)

If Rng.Hyperlinks.Count = 0 Then
GetURL = ""
Else
GetURL = Rng.Hyperlinks(1).Address
End If
End Function

So if you had a hyperlink in A1, you could put =getURL(a1) in that adjacent
cell.

Then convert those formulas to values. Then use =hyperlink(b1) instead.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And after you convert those formulas to values (edit|copy, edit|paste
special|values), you can delete the links from the other cells.

Select the cells and run this macro:

sub deletelinks()
selection.hyperlinks.delete
end sub

 

[Guest]

worked beautifully. THANKS!