Split folder paths in Excel to return the final folder

Question

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.

Answer 1

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)

Answer 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)

Answer 3

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.