Formula to get details from Hyperlink

[Mishka]

Hi,

I have a 300 + individual Hyperlinks in excel, which I would need to get the details of where the hyperlink is pointing to.

E.g. News - has the hyperlink to www.news.com.au

Rather then going into each individual record, I was hoping that someone can recommend a formula that can extract that detail?

Thank you in advance.

Regards,
Michelle

 

[DonkeyOte]

I think you will need to use a User Defined Function (VBA) for this - formulae will only retrieve the "text to display" element (as you no doubt know already)

Function GetLink(rngHyper As Range) As String
    On Error GoTo Handler
    With rngHyper(1).Hyperlinks(1)
        GetLink = IIf(.Address <> "", .Address, .SubAddress)
    End With
    Exit Function
Handler:
    GetLink = "Invalid Hyperlink"
End Function

The above would be stored in a Standard Module in VBE (in a Macro Enabled File) and would be called from a cell along the lines of:

=GETLINK(A1)

where A1 holds the Hyperlink

The above obviously assumes manually created hyperlinks rather than those generated via HYPERLINK function and an XL version other than Mac 2008 (no VBA)

Note also that the insertion/modification of a manually created Hyperlink is a non-volatile action and will not invoke a recalculation.

 

[lapta301]

Perfect timing as I have a similar requirement and this works fine.

If the hyperlink is similar to those used on this board (see below) could you provide a formula to extract the number that comes after the equals sign namely 536375

http://www.mrexcel.com/forum/showthread.php?t=536375

Thank you.

 

[DippNCope]

I forgot where I found this but it works.
if your hyperlinks are in column A this will put url in B
Select cells with your hyperlinks and run

Sub HyperLinkAddressToRight()
Dim h As Hyperlink

For Each h In Selection.Hyperlinks
  h.Range.Offset(0, 1).Value = h.Address
Next h
End Sub

 

[DonkeyOte]

If the hyperlink is similar to those used on this board (see below) could you provide a formula to extract the number that comes after the equals sign namely 536375

Using the code from post #2 - change:

GetLink = IIf(.Address <> "", .Address, .SubAddress)

to

GetLink = Split(Split(IIF(.Address <> "",.Address,.SubAddress),"&")(0),"=")(1)

The above will return the value of the first variable within the URL else error if no variables exist.

If you want to retrieve by variable name etc... it would be worth adapting the UDF to accept some additional parameters.

 

[lapta301]

DonkeyOte

That works fine thank you so much, another big task out of the way