Get Folder Name

[djl0525]

I know how to get the file path...

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

But is there a way to get just the FOLDER name (not the entire path) of the current file?

DJ

 

[AliGW]

Doh!!! Good point ...

OK - I'm having a look now.

 

[AliGW]

Try this:

=TRIM(LEFT(SUBSTITUTE(RIGHT(SUBSTITUTE(SUBSTITUTE( LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",""),"\",REPT(" ",30)),60),REPT(" ",30),"\"),FIND("\",SUBSTITUTE(RIGHT(SUBSTITUTE(SUBSTITUTE( LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",""),"\",REPT(" ",30)),60),REPT(" ",30),"\"))-1))

 

[djl0525]

Thanks for the quick reply. I think you are on the right track....

It doesn't bring back enough characters, though. I'm only getting the last 12 characters between the backslashes.

DJ

 

[navic]

Try this in A1 (Save file after entered formula)

=IFERROR(TRIM(SUBSTITUTE(MID(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1),FIND(CHAR(1),SUBSTITUTE(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1),"\",CHAR(1),(LEN(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1))-LEN(SUBSTITUTE(LOWER(LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)),"\","")))/1-1))+1,20),"\","")),"")

 

[djl0525]

Okay, that got me the first 20 characters between the last two backslashes.

I want everything between those two backslashes. And the number of characters will vary, of course.

DJ

 

[AliGW]

The first 20 or the last 20? Should give you more than that.

Give me a real filepath to work with.

 

[djl0525]

C:\Users\DJLTC\Box Sync\DJLTC\01 - Courses\EXC10\DJL Training EXC10 Student Files (XLSM)\Level 1\Lesson 02 - Editing, Viewing, and Printing Worksheets\

 

[AliGW]

Ah - so there is no filename there in the string?

 

[djl0525]

Correct. The formula I posted gets the file path. I just want the end of the path, the last folder.

DJ

 

[navic]

Get Folder Name only from pathe

Correct. The formula I posted gets the file path. I just want the end of the path, the last folder.

What is problem with my formula
See this ss

Just change this part of formula
/1-1))+1;20)

to

/1-1))+1;100)

[EDIT] Sorry, on the screenshot formula need to be in A7 cell