Extracting URL from Hyperlink

[Lisa]

I have a spreadsheet with several hundred website entries listed. Each one
of them is a hyperlink where the hyperlink displays as "xyz company". In
order to extract the URL to a different cell, I have to right click, select
"Edit Hyperlink" then highlight the URL and copy it, hit cancel, and then
paste the URL into another cell.

Is there an easier way?

Excel 2003 SP2

 

[Mike H]

Lisa,

You could use this UDF.
Alt+F11 to open VB editor
Right click 'This Workbook'
Insert Module
Paste this in on the right

Function GetLink(HyperlinkCell As Range)
GetLink = Replace(HyperlinkCell.Hyperlinks(1).Address, "mailto:", "")
End Function

Enter the formula
=GetLink(A1) in a cell to return the underlying address of a hyperlink in
that cell

Mike

 

[Gary''s Student]

Here is a User Defined Function that will return a URL:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

 

[Lisa]

Perfection!! You just saved me hours!! Thanks!

 

[Mike H]

Glad I could help

Perfection!! You just saved me hours!! Thanks!