Forum Home
Press F1
 
Thread ID: 40508 2003-12-09 22:37:00 excel hyperlink eve rybody (4979) Press F1
Post ID Timestamp Content User
198951 2003-12-09 22:37:00 I have a list of names in col A (Excel) The name such as Jim Bean has a Hyperlink attached.
I want to show the value of the hyperlink in the B column.
Is there any formula or macro that I can use to do this.
Ta
Brian 		eve rybody (4979)
198952 2003-12-09 22:52:00 Explain the "value" of the hyperlink?

Can't quite grasp what you are trying to do? 		godfather (25)
198953 2003-12-09 22:54:00 Probably list the actual URL/ADDRESS that is hyperlinked?

I don't know how. 		Mark Veldhuizen (2570)
198954 2003-12-09 23:07:00 Ah--I see now.

Probably the hyperlink is an email address, and they want that shown in another cell.

No available function to do that as I see it, but a VB expert may know on a method. 		godfather (25)
198955 2003-12-09 23:11:00 This will show the path that the hyperlink is pointing to.

Function Barnabas(myRange As Range) As String

Dim String1 As String
Dim String2 As String

If myRange.Hyperlinks.Count = 0 Then
Exit Function
End If

String1 = myRange.Hyperlinks(1).Address
String2 = myRange.Hyperlinks(1).SubAddress

If String2 <> "" Then
String1 = "[" & String1 & "]" & String2
End If

Barnabas = String1

End Function


Just go to tools, macro, visual basic editor, make a new module and then copy and paste the code into that module and save. Now in your worksheet simply enter the formula

=Barnabas(A1)

or whatever the cell reference happens to be and the path of the hyperlink will be displayed.
Hope this is what you were after as it wasn't terribly clear. If not post again and I'll go from there.

B. 		Barnabas (4562)
198956 2003-12-09 23:20:00 That works.

Filed for reference, thanks! 		godfather (25)
198957 2003-12-10 00:54:00 Worked a treat. Thank you very much
ttfn brian 		eve rybody (4979)
198958 2003-12-10 01:04:00 Glad to be of service.

B. 		Barnabas (4562)
1