Formula to get details from Hyperlink

M

Mishka

New Member
Joined
Apr 16, 2007
Messages
33
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
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
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)

Code: 
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:

Code: 
=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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
Code: 
Sub HyperLinkAddressToRight()
Dim h As Hyperlink

For Each h In Selection.Hyperlinks
  h.Range.Offset(0, 1).Value = h.Address
Next h
End Sub
 
Upvote 0
lapta301 said:
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
Click to expand...

Using the code from post #2 - change:

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

to

Code: 
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.
 
Upvote 0
DonkeyOte

That works fine thank you so much, another big task out of the way
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Pulling Data From ~300 Individual Sheets Onto a Master Sheet
Replies
1
Views
199
ranman256
ranman256
D
  • Solved
Hyperlink to today's date in a row.
Replies
1
Views
191
djclements
djclements
L
  • Question Question
Create Hyperlink from one Tab to another based on Cell - Hyperlink needs to move when cell moves
Replies
4
Views
373
LaputianSky
L
M
  • Question Question
Formula Help - Tab naming without loosing hyperlink/s
Replies
1
Views
400
TheLastLinuxUser
T
S
  • Question Question
HYPERLINK() function Seems Unable to Jump to Another Sheets Table and Filter
Replies
6
Views
375
Skyybot
Skyybot

Forum statistics

Threads
1,224,616
Messages
6,179,909
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top