Get the file path without the file name

[jwalkerack]

Hello ,

i have a long list of file directories on a spreadsheet.

Intially i wanted to seperate the file name. i was able to find a post online which allowed me to do this

get filename from filepath and filename

But now i would like to just have the file path without the file name

So if my full directory was

S:\AppsData\Excel\Jack\TEST\Football_players.xls

i would like the end result to be

S:\AppsData\Excel\Jack\TEST\


Is there an easy way of doing this ...


Thanks a lot Jack

 

[jwalkerack]

Thanks Guys , that was great , help , i had already for the file name from the at link i had on the first message of the post.

i was then used the subsitute formala as you guys , stated ,

Thanks a lot

 

[cgiesbre]

(1) file path: =LEFT(CELL("filename"),SEARCH("[",CELL("filename"),1)-2)

(2) file name: =MID(CELL("filename"),SEARCH("[",CELL("filename"),1)+1,SEARCH("]",CELL("filename"),1)-SEARCH("[",CELL("filename"),1)-1)

(3) tab name: =MID(CELL("filename"),SEARCH("]",CELL("filename"),1)+1,99)

-----------------------

(4) file path+name: =LEFT(CELL("filename"),SEARCH("[",CELL("filename"),1)-2)&""&MID(CELL("filename"),SEARCH("[",CELL("filename"),1)+1,SEARCH("]",CELL("filename"),1)-SEARCH("[",CELL("filename"),1)-1)

 

[Peter_SSs]

@cgiesbre
Welcome to the MrExcel board!

I am not sure whose question you are answering(?) but since you have MS365 those values can be obtained with simpler formulas as shown below. Also note also that your 'path + name' formula omits the final separator.

Sample.xlsm
	A	B	C
1	file path	D:\D-Documents\Test	D:\D-Documents\Test
2	file name	Sample.xlsm	Sample.xlsm
3	tab name	Some sheet	Some sheet
4	path + name	D:\D-Documents\TestSample.xlsm	D:\D-Documents\Test\Sample.xlsm
Some sheet

Cell Formulas
Range	Formula
B1	B1	=LEFT(CELL("filename"),SEARCH("[",CELL("filename"),1)-2)
C1	C1	=TEXTBEFORE(CELL("filename"),"\[")
B2	B2	=MID(CELL("filename"),SEARCH("[",CELL("filename"),1)+1,SEARCH("]",CELL("filename"),1)-SEARCH("[",CELL("filename"),1)-1)
C2	C2	=INDEX(TEXTSPLIT(CELL("filename"),{"[","]"}),2)
B3	B3	=MID(CELL("filename"),SEARCH("]",CELL("filename"),1)+1,99)
C3	C3	=TEXTAFTER(CELL("filename"),"]")
B4	B4	=LEFT(CELL("filename"),SEARCH("[",CELL("filename"),1)-2)&""&MID(CELL("filename"),SEARCH("[",CELL("filename"),1)+1,SEARCH("]",CELL("filename"),1)-SEARCH("[",CELL("filename"),1)-1)
C4	C4	=SUBSTITUTE(TEXTBEFORE(CELL("filename"),"]"),"[","")