Help Deleting all text in cell following last \

[maximillianrg]

Hello Excel Masters

Using a command window in Windows 10 I have exported a directory structure to a text file and imported that text file it into excel so each line (over 9,000) contains the full path and full file name of the file. Some files are 4 folders deep and some are 10 folders deep.

C:\FOO\DOG\CAT\BEAR\LION\MEER CAT\GREAT PLAMES OF AFRICA\01021\THE GREAT PLANES.XLS
C:\GARBAGE\RED CANS\BLUE\0192121288452.DOC

I'm looking for a formula that I'm guessing will combine right and search that will locate the last "\" in the string and delete everything after it so the above examples would become the below

C:\FOO\DOG\CAT\BEAR\LION\MEER CAT\GREAT PLAMES OF AFRICA\01021
C:\GARBAGE\RED CANS\BLUE\

Thank you so much in advance for your help

Max

 

[Rick Rothstein]

Give this formula a try...

=LEFT(A1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

 

[maximillianrg]

Give this formula a try...

=LEFT(A1,FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))))

Hello Rick - Than worked like a champ - thank you so much - I always forget about the LEN Function - Nice work

 

[Peter_SSs]

No use for the OP with Excel 2016 but some of these text manipulation problems are getting much simpler with the latest available functions.

22 05 03.xlsm
	A	B
1	C:\FOO\DOG\CAT\BEAR\LION\MEER CAT\GREAT PLAMES OF AFRICA\01021\THE GREAT PLANES.XLS	C:\FOO\DOG\CAT\BEAR\LION\MEER CAT\GREAT PLAMES OF AFRICA\01021
2	C:\GARBAGE\RED CANS\BLUE\0192121288452.DOC	C:\GARBAGE\RED CANS\BLUE
TEXTBEFORE

Cell Formulas
Range	Formula
B1:B2	B1	=TEXTBEFORE(A1,"\",-1)

 

[Rick Rothstein]

@Peter,
That is probably Microsoft's ultimate strategy... improve the formula world so much that people and businesses almost have to upgrade to 365 just for the simplicity it will provide.