|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
QTGlennM wrote:
[Quoted Text] > 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
|
|
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" <glenn.m.davis1[ at ]gmail.com> wrote in message news:1153942432.224285.68560[ at ]m73g2000cwd.googlegroups.com...
[Quoted Text] > 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(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
|
|
|