LinkBack URL
About LinkBacks
Any Excel formula I can use to extract only the folder path (D:\Shares\Admin\110122.CTOM-Annual-Dinner\IMG_0044.jpg) into D:\Shares\Admin\110122.CTOM-Annual-Dinner without the filenames
I;m trying not to use any Macro.
Return Fullpath without filename
Any Excel formula I can use to extract only the folder path (D:\Shares\Admin\110122.CTOM-Annual-Dinner\IMG_0044.jpg) into D:\Shares\Admin\110122.CTOM-Annual-Dinner without the filenames
I;m trying not to use any Macro.
Last edited by andywwc; 05-19-2011 at 05:32 AM.
Hi,
I am guessing that you have a list of filenames already in Excel.
Second guess is that the file names differ in length.
Correct me if my assumptions are wrong.
abousetta
Sorry... I should have began with Welcome to the Forum
abousetta
This is the sample of the data in the Excel spreadsheet.
D:\Shares\Admin
D:\Shares\Admin\110122.CTOM-Annual-Dinner
D:\Shares\Admin\110122.CTOM-Annual-Dinner\IMG_0044.jpg
D:\Shares\Admin\110122.CTOM-Annual-Dinner\P1000774.JPG
D:\Shares\Admin\CHEERS Photo\P1010084.JPG
D:\Shares\Admin\CHEERS Photo\P1010085.JPG
D:\Shares\Admin\CHEERS Photo\P1010086.JPG
D:\Shares\Admin\CHEERS Photo\Thumbs.db
D:\Shares\Admin\CTOM Photos
D:\Shares\Admin\CTOM Photos\101015.MarkLwinBirthday\MarkLwinBirthday-0007.jpg
D:\Shares\Admin\CTOM Photos\101015.MarkLwinBirthday\MarkLwinBirthday-0008.jpg
D:\Shares\Admin\CTOM Photos\101015.MarkLwinBirthday\Thumbs.db
D:\Shares\Admin\CTOM Photos\101022.Teambuilding\P1000045.JPG
D:\Shares\Admin\CTOM Photos\101022.Teambuilding\P1000046.JPG
Hi,
OK, here is my first attempt. It seems to be working fine on the list you posted. The original formula was posted by Aladin Akyurek (other options available including a different approach by DonkeyOte).
I have modified formula to be as follows:
The attachment may be a little more intuiative.
Please Login or Register to view this content.
Let me know if it works out for you or if you have any scenarios in which is doesn't perform as expected.
Good luck.
abousetta
Thanks, it's working great. I have modified the code to remove filenames with 4 extension, i.e..docx
=IF(ISERROR(FIND(".",RIGHT(A2,5))),A2,LEFT(A2,LEN(A2)-(LEN(A2)-SEARCH("@",SUBSTITUTE(A2,"\","@",LEN(A2)-LEN(SUBSTITUTE(A2,"\","")))))))
From the file, can we show the result of the pathname without the '\' i.e. D:\Shares\Admin\110122.CTOM-Annual-Dinner instead of D:\Shares\Admin\110122.CTOM-Annual-Dinner\
Last edited by andywwc; 05-18-2011 at 09:35 PM.
Hi,
I thought about that after the post, but since your examples excluded four letter file extensions, then I didn't investigate further. Even so, I think you got the hang of the formula if you were able to modify it.
As for the second request, let me get back to you in five minutes.. need to test it in real life, but shouldn't be a problem (I think).
abousetta
It looks ugly and it will have to try and see if I can streamline it, but this seems to work fine for the possible scenarios so far:
Now I am putting all the formulas in one cell because I am assuming that you don't want to use helper cells (columns) and I think you said that you don't want any vba.Please Login or Register to view this content.
abousetta
The attachment demonstrates the differences between the two techniques (one formula - one column vs. two formulas - two columns). Results are the same and the latter is probably easier to maintain, but this is a matter of personal preference.
abousetta
Thanks a lot abousetta!!! You make my day.
I will stick to the later formula. It saves me a lot of time.
Glad I could be of assistance.
If you are satisfied with the results, please mark the thread as [SOLVED] (next to the title on the first post). Also positive recognition (scales) are always welcomed.
Good luck.
abousetta
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks