[QTGlennM]

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

[Franz Verga]

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

Maybe it's a little bit long, but I think it should work:

=MID(SUBSTITUTE(A10,"\","]",LEN(A10)-LEN(SUBSTITUTE(A10,"\",""))),FIND("]",SUBSTITUTE(A10,"\","]",LEN(A10)-LEN(SUBSTITUTE(A10,"\",""))))+1,LEN(A10)-FIND("]",SUBSTITUTE(A10,"\","]",LEN(A10)-LEN(SUBSTITUTE(A10,"\",""))))-4)

where in A10 you have your path with filename.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

[David McRitchie]

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

[Bill Manville]

=HYPERLINK(A1,MID(LEFT(A1,FIND(".",A1)-1),MAX(ROW(INDIRECT("1:"&LEN(A1)
))*(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="\"))+1, 255))

entered as an array formula with Ctrl+Shift+Enter.
It assumes there will be at least one "\" in the file name and a . in
only the file name part.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup