FORMULA to display parent folder name

[ms500716]

Does anyone have ideas for a FORMULA (not a macro) that can be used to diplay the parent folder name in a cell? I don't need the file name or workbook name, just the folder name.

Example,
My Workbook is stored in the following location: C:\Files\ExcelFiles

I want a fromula to return "ExcelFiles", since that is the parent folder

I have searched previous threads but found no resoultion.

 

[Blade Hunter]

Sorry didn't see the part about the last dir only, Try this:

=TRIM(RIGHT(SUBSTITUTE(LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-2),"\",REPT(" ",100)),100))

It works by getting the DIR and replacing all "\" with 100 spaces. It then grabs the right 100 chars which will be a bunch of spaces and the final DIR name then it trims off the preceding spaces.

Good point Hotpepper:

=TRIM(RIGHT(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-2),"\",REPT(" ",100)),100))

 

[ms500716]

Thank you all for your help. The formula Blade Hunter gave works perfectly.

=TRIM(RIGHT(SUBSTITUTE(LEFT(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)-2),"\",REPT(" ",100)),100))

Thanks again!

 

[tabiscari]

Don't complicate your life

this macro list all files in a folder and automatically adds an hyperlink to the folder and to the file itself (first define iRow = 1)

Yes, it also shows the folder name...

Sub ListFiles()


iRow = 11

Rows(iRow & ":" & "65536").Clear

'grab the path from a cell or else, this is just an example
Call ListMyFiles("C:\Program Files\Microsoft Office")



End Sub

Sub ListMyFiles(mySourcePath)
Set MyObject = CreateObject("Scripting.FileSystemObject")
Set mySource = MyObject.GetFolder(mySourcePath)
On Error Resume Next
For Each myFile In mySource.Files
iCol = 2
Cells(iRow, iCol).Value = myFile.path
iCol = iCol + 1

'this is the folder name with the hyperlink
Cells(iRow, iCol).Hyperlinks.Add _
Anchor:=Cells(iRow, iCol), _
Address:=mySourcePath, _
TextToDisplay:=mySource.Name
iCol = iCol + 1

'this is the file name with the hyperlink
Cells(iRow, iCol).Hyperlinks.Add _
Anchor:=Cells(iRow, iCol), _
Address:=myFile.path, _
TextToDisplay:=myFile.Name
iCol = iCol + 1

Cells(iRow, iCol).Value = myFile.Size / 1024 'for kilobytes display
iCol = iCol + 1

Cells(iRow, iCol).Value = myFile.DateLastModified

iRow = iRow + 1

Next

For Each mySubFolder In mySource.SubFolders
Call ListMyFiles(mySubFolder.path)
Next


End Sub

 

[Scott Huish]

That's not what the op was asking for, the op wanted to return the path of the workbook the formula was in. The op also specifically asked for a formula, not a macro. But you do know this thread is almost 8 years old right?

 

[tabiscari]

That's not what the op was asking for, the op wanted to return the path of the workbook the formula was in. The op also specifically asked for a formula, not a macro. But you do know this thread is almost 8 years old right?

Sure, just thought it might be helpful