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


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?
    – 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

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, 2015 at 14:54

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

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


... 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 agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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