PDA

View Full Version : Solved: Viewing Hyperlink locations



GoKats78
02-11-2010, 05:58 AM
I have a spreadsheet in which there are literally hundreds of hyperlinks (all in the same column). Is there a means to view the locations of the links..other than one at a time? What I need is to be able to somehow copy and paste that column in such a means as the file location of the individual links is visible rather than the text assigned to the hyperlink.

Does that make sense?

mike31z
02-11-2010, 08:07 AM
If you have a extra column you can use the simple formula below to convert the hyperlink back to text only'


=T(B1)

That should convert the total formula back to text.

Of cource B1 is the cell were the source is just copy the formula down the column.

The result should give you the exact location.


Mike in Wisconsin

mbarron
02-11-2010, 08:19 AM
Here is a UDF.
Copy the code to a module in the workboox (not a sheet module or the ThisWorkbook module)
Use the formula: =showlink(A1)

Function showLink(myCell As Range) As String
Application.Volatile
On Error Resume Next
showLink = myCell.Hyperlinks(1).Address
If Err.Number = 9 Then
showLink = "No Link"
End If
Err.Clear
End Function