Extract Text before last slash

[Zakkaroo]

I feel somewhat guilty for this, because there seems to be a fair bit of information out there on this topic, but I couldn't find the specific example I needed after much googling, I've struggled to adapt the example formulas I've seen (using TRIM, MID, LEFT, FIND, etc. and I'm under a bit of time pressure.

What I have is a long list of file locations and names

e.g.

c:\Test\Folders\My Stuff\wordDoc.docx
c:\My work\Safety Test.doc
c:\Trial\2015\June\Alpha\Manual Handling.docx

I need all the text before the last slash ... i.e. just the file location

So the results for the above, would be:

c:\Test\Folders\My Stuff
c:\My work
c:\Trial\2015\June

Any help would be appreciated.

 

[Special-K99]

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))-1)

NOTE: There's an error in your results, the last result should be

c:\Trial\2015\June\Alpha\

ie, the last slash is after Alpha not June

 

[Zakkaroo]

Thanks so much, that appears to have worked - and yes, well spotted ... in my rush to post, I deleted too much of the text!

 

[Puertorekinsam]

=left(a1,len(a1)-len(right(a1,len(a1)-find("|",substitute(a1,"\","|", len(a1)-len(substitute(a1,"\","")))))))

 

[István Hirsch]

Another version:

=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",200)),200)),"")

 

[Rick Rothstein]

Here is another formula, an array-entered** one, that will also work...

=LEFT(A1,MAX((MID(A1,ROW(1:300),1)="\")*(ROW(1:300)))-1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Zakkaroo]

Thanks all for the various solutions! Much appreciated.

 

[Crystaline]

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))-1)

NOTE: There's an error in your results, the last result should be

c:\Trial\2015\June\Alpha\

ie, the last slash is after Alpha not June

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",(LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))/LEN("\")))-1) - this formular on my side give an error of "Value" in excel can you kindly assist

 

[jtakw]

Hi,

I think you should start a new thread, with multiple samples of your text strings, and your expected results.
We'll be more than happy to help.