Returning last directory in excel? 2

[cjhepburn]

I've been puzzling over this for a while.

I have a formula "=cell("filename&quot" that gives me
"c:\xxx\yyy\ttt\excel.xls"

How do I get the cell block to return "ttt"?

 

[JVFriederick]

If you need the complete directory, without filename :
=MID(CELL("filename",A1),1,FIND("[",CELL("filename",A1))-2)

If you want to trim the drive letter out :
=MID(CELL("filename",A1),3,FIND("[",CELL("filename",A1))-4)

If you want JUST the last directory name :
Tried, but I don't know how to do that ! Sorry. :-(

 

[Store300]

You can use the FIND function to discover where the "\" characters are and then use the MID, LEFT or RIGHT functions to trim the string

 

[DarkSun]

Try This:

Insert this function into a Module in the Workbook.

Public Function GetLastDirectory(Pathname As String)
Dim LookPos As Integer
Dim PrevPos As Integer
LookPos = 0

Do While InStr(1, Pathname, &quot;\&quot <> 0
If InStr(LookPos + 1, Pathname, &quot;\&quot = 0 Then
Pathname = Mid(Pathname, PrevPos + 1, (LookPos - PrevPos - 1))
Exit Do
End If
PrevPos = LookPos
LookPos = InStr(LookPos + 1, Pathname, &quot;\&quot
Loop

GetLastDirectory = Pathname
End Function

Then you can enter something like this into a cell

=getlastdirectory(cell(&quot;filename&quot)

Let me know if you have any problems.

 

[JVFriederick]

Very cool.

Did we loose the original author?

 

[cjhepburn]

Thanks, works beautifully.