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

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

"Bob" wrote:

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

Reading your post again it seems these links are all over the sheet so use
this instead. Be careful because it will overwrite anything in the cell
adjacent to each hyperlink.

Sub Prime_Lending()
Dim c As Hyperlink
For Each c In ActiveSheet.Hyperlinks
On Error Resume Next
c.Range.Offset(0, 1).Value = c.Address
Next
End Sub

Mike

Sub Prime_Lending()
Dim c As Hyperlink
For Each c In ActiveSheet.Hyperlinks
On Error Resume Next
c.Range.Offset(0, 1).Value = c.Address
Next
End Sub

"Mike H" wrote:

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

"Bob" wrote:

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

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 InsertModule.

Paste the UDF into that module.

Alt + q to return to the Excel window.


Gord Dibben MS Excel MVP

On Sat, 10 Jan 2009 07:39:01 -0800, Bob
wrote:

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 - 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)
--
Bob


"Mike H" wrote:

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

"Bob" wrote:

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

¡Brilliant!


"Mike H" wrote:

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

"Bob" wrote:

I have a lot of hyperlinks. I want to produce
a column adjacent to each hyperlink to show the text so that I can then
extract reference from the full text string

Can anyone give me simple instructions how to achieve this?

Bob