If I understand you correctly you have a complete pathname
in Column A (no link) and you want to create a HYPERTEXT Worksheet
Function in B that shows the filename but links to the full pathname.
You will still have the full pathname in Column A showing and the
formula in column B will be dependent on the value in Column A.


Private Function ExtractFilename(cell) As String
Dim str As String, newstring As String
str = cell
If InStr(str, "\") = 0 Then
ExtractFilename = str
Exit Function
End If
newstring = Mid(str, InStrRev(str, "\", , vbTextCompare) + 1)
If InStr(newstring, ".") = 0 Then
ExtractFilename = newstring
Exit Function
End If
str = Left(str, InStr(newstring, ".") - 1)
ExtractFilename = str
End Function


a2: G:\Staff Duty Book Folders V2\Book Cover.doc
b2: =Hyperlink(a2,ExtractFilename(a2))

If you were extracting the hyperlink from A2 the following
function would work --- but I don't think that is what you are doing.

Private Function HyperlinkFilename(cell) As String
Dim str As String, newstring As String
str = cell.Hyperlinks(1).Address
If cell.Hyperlinks.Count < 1 Then Exit Function
If InStr(str, "\") = 0 Then
HyperlinkFilename = str
Exit Function
End If
newstring = Mid(str, InStrRev(str, "\", , vbTextCompare) + 1)
If InStr(newstring, ".") = 0 Then
HyperlinkFilename = newstring
Exit Function
End If
str = Left(str, InStr(newstring, ".") - 1)
HyperlinkFilename = str
End Function




---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"QTGlennM" wrote in message ups.com...
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