Hyperlink display
 

I have the file path to several files of mine in columb A.
In columb B, I use =hyperlink(A1) and copy down.

The hyperlinks work fine but I only want to display the actual file
name.

example instead of

- G:\Staff Duty Book Folders V2\Book Cover.doc

I want it to display

- Book Cover

Any suggestions I am totally lost on this.
Thanks in advance

Hyperlink display
 

=HYPERLINK(A1,RIGHT(A1,LEN(A1)-FIND("^^",
SUBSTITUTE(A1,"\","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))))

=====
Are all the files in the same folder?

If yes, then I'd use another cell to hold the folder:
Put this in (say) $b$1
G:\Staff Duty Book Folders V2\
and put the file name in A1.

Then the formula could be:

=hyperlink($b$1&a1,a1)
or
=hyperlink("file:////" & $b$1 & a1,a1)

I find this especially nice if the folder name could change--I just change one
spot.

QTGlennM wrote:

I have the file path to several files of mine in columb A.
In columb B, I use =hyperlink(A1) and copy down.

The hyperlinks work fine but I only want to display the actual file
name.

example instead of

- G:\Staff Duty Book Folders V2\Book Cover.doc

I want it to display

- Book Cover

Any suggestions I am totally lost on this.
Thanks in advance

--

Dave Peterson

Hyperlink display
 

Dave you are the man!!! That was the solution that I was looking for.
You deserve a raise just send me your bosses email I will take care of
the rest ;-).


Dave Peterson wrote:
=HYPERLINK(A1,RIGHT(A1,LEN(A1)-FIND("^^",
SUBSTITUTE(A1,"\","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))))

=====
Are all the files in the same folder?

If yes, then I'd use another cell to hold the folder:
Put this in (say) $b$1
G:\Staff Duty Book Folders V2\
and put the file name in A1.

Then the formula could be:

=hyperlink($b$1&a1,a1)
or
=hyperlink("file:////" & $b$1 & a1,a1)

I find this especially nice if the folder name could change--I just change one
spot.

QTGlennM wrote:

I have the file path to several files of mine in columb A.
In columb B, I use =hyperlink(A1) and copy down.

The hyperlinks work fine but I only want to display the actual file
name.

example instead of

- G:\Staff Duty Book Folders V2\Book Cover.doc

I want it to display

- Book Cover

Any suggestions I am totally lost on this.
Thanks in advance

--

Dave Peterson