How can I display the text of a hyperlink?

[Bob]

I have a lot of hyperlinks to photographic sites set up on XL, each of which
contains the photo reference number within the link text. I want to produce
a column adjacent to each hyperlink to show the text so that I can then
extract the photo reference from the full text string as a numeric record for
each photo. I know I could just move the cursor over the link, read the
text and input the number, but there are almost 6,000 of them, each with 7
digits.

I have tried numerous things and although the answer seems to be on this
forum, I don't understand the answers on here -using a macro? Can anyone
give me simple instructions how to achieve this?

Bob

 

[Mike H]

Bob,

I 've assumed these hyperlinks are in Column A. If that's incorrect post
back. Right click your sheet tab, view code and paste this in and run it.

Sub Prime_Lending()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
On Error Resume Next
c.Offset(0, 1).Value = c.Hyperlinks(1).Address
Next
End Sub

Mike

 

[Gord Dibben]

Copy this David McRitchie UDF to a general module in your workbook.

Function HyperLinkText(oRange As Range) As String
Dim ST1 As String, ST2 As String
If oRange.Hyperlinks.Count = 0 Then Exit Function
ST1 = oRange.Hyperlinks(1).Address
ST2 = oRange.Hyperlinks(1).SubAddress
If ST2 <> "" Then ST1 = "[" & ST1 & "]" & ST2
HyperLinkText = ST1
End Function

In a cell adjacent to your cell with the hyperlink enter

=hyperlinktext(cellref)

To get to a general module, hit Alt + F11 to open VBE

CTRL + r to open Project Explorer.

Select your workbook/project. Right-click and Insert>Module.

Paste the UDF into that module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

 

[Bob]

Mike H - absolutely brilliant! Its 2200 here in UK and just checked for
any replies... ....Copied hyperlinks to col A on blank worksheet and pasted
your formula as instructed. Got the full text displayed which I can now
truncate and copy back to my base worksheet. Never used "view code" before
- easy when you know how.

Many many MANY thanks!! (and to Gord Dibben's response which I presume also
works)

 

[Pto. Morelos]

¡Brilliant!

Bob,

I 've assumed these hyperlinks are in Column A. If that's incorrect post
back. Right click your sheet tab, view code and paste this in and run it.

Sub Prime_Lending()
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
On Error Resume Next
c.Offset(0, 1).Value = c.Hyperlinks(1).Address
Next
End Sub

Mike