Get the file path without the file name

jwalkerack

Board Regular
Joined
Jun 19, 2013
Messages
81
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi Ranman ,

This directories are not linked to the active workbook,


They are file locatons which have been put onto a spreadsheet for example one cell will contain similar as below
the file names are not need to every after the last \ can be deleted , But there is not a set amount of \ , sometime there will be more than above and below

S:\AppsData\Excel\Work\tp\2s.xls</SPAN>
S:\AppsData\Excel\Work\tp\ABE_Luva_prospects.xls</SPAN>
S:\AppsData\Excel\Work\tp\ABE_MidNor_Index_map.xls</SPAN>
S:\AppsData\Excel\Work\tp\A.xls</SPAN>
S:\AppsData\Excel\Work\tp\ABE.xls</SPAN>
S:\AppsData\Excel\Work\tp\ABE_.xls</SPAN>
S:\AppsData\Excel\Work\tp\ABE_M.xls</SPAN>
S:\AppsData\Excel\Work\tp\ABE_.xls</SPAN>
S:\AppsData\Excel\Work\tp\ABE_.xls</SPAN>
S:\AppsData\Excel\Work\tp\ABExls</SPAN>
S:\AppsData\Excel\Work\tp\ABE_.xls</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>
 
Last edited:
Upvote 0
hi guys , maybe i am using the wrong terminology

Maybe its not the file path that i want . i guess what i want is the objectname of the file minus the file name.

So if my file path/ Object name was P:\Training Videos\Andrew GIS Training\Training_1.txt and this was on one column on the spreadsheet . Do i need to use VBA , IS there not a funcation which will do this ??

Column a </SPAN>Column b </SPAN>
Input cell </SPAN>Output cell </SPAN>
P:\Training Videos\Andrew GIS Training\Training_1.txt</SPAN>P:\Training Videos\Andrew GIS Training\</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>
 
Upvote 0
A​
B​
C​
1​
S:\AppsData\Excel\Work\tp\2s.xlsS:\AppsData\Excel\Work\tp\B1: =LEFT(A1, FIND("|", SUBSTITUTE(A1, "\", "|", LEN(A1) - LEN(SUBSTITUTE(A1, "\", "")))))
2​
P:\Training Videos\Andrew GIS Training\Training_1.txtP:\Training Videos\Andrew GIS Training\
 
Upvote 0
Here is another formula you can consider putting in cell B1 (text assumed to be in cell A1) to get the file's path...

=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",300)),300)),"")

By the way, you can use this formula to get the file name itself...

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",300)),300))


EDIT NOTE
-------------------
Be sure to note shg's warning in Message #8... if your filenames or paths could have multiple adjacent spaces in them, then don't use my posted solution.
 
Last edited:
Upvote 0
Names for directories and files don't commonly have two sequential spaces, but if one did, ...
 
Last edited:
Upvote 0
the array formula (ctrl shift Enter)

Code:
=LEFT(A2,MAX((MID(A2,ROW(1:256),1)="\")*(ROW(1:256))))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top