Hyperlink to a folder
 

I currently have a spreadsheet with multiple sheets, and LOTS of hyperlinks.
Right now the hyperlink opens a file, and all of that works fine. I would
like to create in the far right column a second hyperlink that opens the
folder the file is located in.

So, somehow using a formula or VBA I would like to take all of the path
except the file name and create a hyperlink without having to make each
hyperlink manually.

For example I may have a hyperlink of with text of joe and it points to
\\servername\Sharename\Folder1\Folder2\Folderx\pho tograph.jpg

The path from hyperlink to hyperlink will be different, i.e. different
folders. I just want to peal everything off except the filename.

Hyperlink to a folder
 

Try this formula with the assumption that your link is in A1.

=HYPERLINK(LEFT(A1,FIND("^^",SUBSTITUTE("\"&A1,"\" ,"^^",LEN("\"&A1)-LEN(SUBSTITUTE("\"&A1,"\",""))))-1))

Regards,
Paul

"Roundy" wrote in message
...
I currently have a spreadsheet with multiple sheets, and LOTS of
hyperlinks.
Right now the hyperlink opens a file, and all of that works fine. I would
like to create in the far right column a second hyperlink that opens the
folder the file is located in.

So, somehow using a formula or VBA I would like to take all of the path
except the file name and create a hyperlink without having to make each
hyperlink manually.

For example I may have a hyperlink of with text of joe and it points to
\\servername\Sharename\Folder1\Folder2\Folderx\pho tograph.jpg

The path from hyperlink to hyperlink will be different, i.e. different
folders. I just want to peal everything off except the filename.

Hyperlink to a folder
 

The formula works OK, but my problem is that A1 display text is not the path,
it is a "friendly name" Is there a way to do this with the hyperlink address
vs. the cell contents?

"PCLIVE" wrote:

Try this formula with the assumption that your link is in A1.

=HYPERLINK(LEFT(A1,FIND("^^",SUBSTITUTE("\"&A1,"\" ,"^^",LEN("\"&A1)-LEN(SUBSTITUTE("\"&A1,"\",""))))-1))

Regards,
Paul

"Roundy" wrote in message
...
I currently have a spreadsheet with multiple sheets, and LOTS of
hyperlinks.
Right now the hyperlink opens a file, and all of that works fine. I would
like to create in the far right column a second hyperlink that opens the
folder the file is located in.

So, somehow using a formula or VBA I would like to take all of the path
except the file name and create a hyperlink without having to make each
hyperlink manually.

For example I may have a hyperlink of with text of joe and it points to
\\servername\Sharename\Folder1\Folder2\Folderx\pho tograph.jpg

The path from hyperlink to hyperlink will be different, i.e. different
folders. I just want to peal everything off except the filename.