3

I have a single column in Excel which has a file and folder path. e.g. C:\1_Folder\2_Folder\3_Folder\my_file.txt

I would like to extract the name of the final folder and place this in a new column. In this example, 3_Folder.

Can this be achieved using a formula rather than VBA?

Edit: the number of nested folders can vary.

2

You can use the FIND and MID text functions. This will work for variable number of folders

Path text C:\1_Folder\2_Folder\3_Folder\my_file.txt

Find position of next to last slash (B1): FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-1))

Find position of last slash (C1): FIND("\",A1,B1+1)

Get the characters between next to last and last slash: MID(A1,B1+1,(C1-B1)-1)

  • That appears to work with a maximum of three nested folders. In some instances I have 7 nested folders. In these cases these formulas do not work? – BobJim Sep 8 '15 at 12:14
  • I just wrote this based on your example. I have amended my answer for variable number of folders. – Todd Sep 8 '15 at 14:05
  • Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15? – BobJim Sep 8 '15 at 14:14
  • 1
    Typos, fixed it. – Todd Sep 8 '15 at 14:21
2

I use regex addin for tasks like this, with regular expression:

=RegExReplace(A1,".*\\([^\\]*)\\[^\\]*","$1") - this extracts the substring before the last \ (practically the last folder as you need)

enter image description here

  • That's pretty slick. Would be nice if Excel supported regular expressions natively. – Todd Sep 8 '15 at 14:54
0

This answer is based on the accepted answer to a very similar question.

If you want it in one formula, you can use:

=RIGHT(A6,LEN(A6)+1-FIND("@",SUBSTITUTE("\"&A6,"\","@",(LEN(A6)-LEN(SUBSTITUTE("\"&A6,"\",""))+1))))

... which basically calculates the number of backslashes by removing them all and comparing lengths. It uses that number to substitutes the last occurrence with a "@" and then turns back around and finds the position of the "@" which it feeds into the RIGHT formula to grab the substring trailing the last backslash. An extra backslash and a "+1" are added in to handle the case of zero backslashes in the source. If there are no backslashes, the original string is returned.

If your data already contains "@" then you will need to pick a different substitution character.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.