Last Subfolder Name

[sdear]

Windows 10, Excel 365
I'm trying to get just the last subfolder name in a path:
C:\ABC\DEF\ABC.txt will give me ABC
I need: C:\ABC...\WTH\ABC.txt or
C:\DEF...\WTH1\ABC.txt
to give me WTH or WTH1.
I'm trying to get away from the 40 pound path: C:\ABC...\WTH\.
I'm trying to remove multiple files (same or similar names) from multiple folders. My macro gives me all the 40 pound names...
Thank you

 

[Eric W]

Try:

Book1
	A	B
1	C:\ABC\DEF\ABC.txt	DEF
2	C:\ABC…\WTH\ABC.txt	WTH
3	C:\DEF…\WTH1\ABC.txt	WTH1
4	C:\ABC\something\else\DEF\Save files\myfile.pdf	Save files
Sheet3

Cell Formulas
Range	Formula
B1:B4	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),198),99))

 

[sdear]

For C:\ABC\DEF\GHI\JKL\ABC\SWF\ABC.txt this gives me DEF. I'm trying to get the last subfolder name (SWF).

 

[Eric W]

It works for me:

Book1
	A	B
1	C:\ABC\DEF\ABC.txt	DEF
2	C:\ABC…\WTH\ABC.txt	WTH
3	C:\DEF…\WTH1\ABC.txt	WTH1
4	C:\ABC\something\else\DEF\Save files\myfile.pdf	Save files
5	C:\ABC\DEF\GHI\JKL\ABC\SWF\ABC.txt	SWF
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),198),99))

If it still doesn't work for you, try using the XL2BB tool (see the link in the reply box) to show what your sheet looks like. It may be that the slash character on your sheet is a different ASCII code that what my formula expects.

 

[sdear]

I'm such an idiot: I looked at another worksheet in the file I'm working with and found I already had a macro that gives me what I want...
BTW: the ASCII for \ on my pc is 92

 

[Eric W]

My ASCII code for \ on my PC is also 92, so the formula should work for you. Regardless, it appears that you found a solution. ?

 

[sdear]

Your formula works. I'm not sure what happened yesterday. I must have sent it looking in the wrong cell.
I'll mark your solution as THE solution.
Thank you for your help

 

[sdear]

BTW: my folder names didn't have \ at the end. That's why it didn't work.