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.

3 Answers 3

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)

4
  • 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?
    – RobN
    Sep 8, 2015 at 12:14
  • I just wrote this based on your example. I have amended my answer for variable number of folders.
    – Todd
    Sep 8, 2015 at 14:05
  • Thanks for updating your formula. Why are the formulas for cells C1 and D1 referencing C15?
    – RobN
    Sep 8, 2015 at 14:14
  • 1
    Typos, fixed it.
    – Todd
    Sep 8, 2015 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

1
  • That's pretty slick. Would be nice if Excel supported regular expressions natively.
    – Todd
    Sep 8, 2015 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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .