How do I display the pathname/address of a hyperlink?

[DMAnalyst]

I have a spreadsheet that links to other sheets and word documents. Does
anyone know of a quick way to change the view/display so that it would
display the actual pathname of the linked files rather than the name as I
have it now? Please advise.

Thank you in advance for your response.

 

[Gary''s Student]

The easiest way is to use the following form for your hyperlinks:

=HYPERLINK("C:\temp\xwer.xls#Sheet2!B9","target")

The cell normally displays:
target
simply touch CNTRL-`
This will change the display to formula mode and you can see the
path/filename.

 

[DMAnalyst]

Hi, thank you for the response but it's not quite I'm looking for --

I already have the links built in. I need to export the information into
another program and it would be useful to be able to pull the information in
as the actual pathname rather than the label. That's what it is doing right
now.

I need a quick way to change from the way it's displaying right now which is
the label to the pathname.

E.g.

Current display: F06 Sample Effort
Preferred display: H:/Marketing Drive/Sample Effort.xls

Please help if you can.

Thank you.

 

[Gary''s Student]

O.K. then...

It will take a tiny bit of VBA

Check back later...

 

[Gary''s Student]

Run this tiny macro on the active sheet:

Sub hyperverter()
For Each Hyperlink In ActiveSheet.Hyperlinks
Hyperlink.TextToDisplay = Hyperlink.Address
Next
End Sub

It will convert each hyperlink so the displayed "friendly name" will be the
address.

 

[DMAnalyst]

You are super-duper awesome. It works!!! Thank you so much.

 

[ngoc lam]

I'm a novice in Excel and macros and its hyperlinks. Can you show me how to run this macro step by step to show the path name? Thank you!

 

[ngoc lam]

I'm a novice w/ macros, excel and its hyperlinks. can you give me step by step directions on how to run this macro? Thank you so much!