FIND text between backslashes in string

[djl0525]

How do I get the text between the last two backslashes in a string?

For example, C:\Users\Folder 1\Folder 2\Folder 3\Folder 4\Folder 5\Folder 6\Folder 7\Folder 8\

I want "Folder 8"

Another example, C:\Users\Folder 1\Folder 2\Folder 3\Folder 4\

I want "Folder 4"

Any assistance will be greatly appreciated!

DJ

 

[AliGW]

Why have you started another thread?

=TRIM(LEFT(SUBSTITUTE(RIGHT(SUBSTITUTE(SUBSTITUTE( LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",""),"\",REPT(" ",30)),60),REPT(" ",30),"\"),FIND("\",SUBSTITUTE(RIGHT(SUBSTITUTE(SUBSTITUTE( LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[",""),"\",REPT(" ",30)),60),REPT(" ",30),"\"))-1))

 

[djl0525]

I did it when you were moving on and I thought it would be confusing to change my strategy/request for someone else 10 replies into the thread.

DJ

 

[Aladin Akyurek]

One way...

=SUBSTITUTE(REPLACE(A2,1,FIND("#",SUBSTITUTE(A2,"","#",LEN(A2)-LEN(SUBSTITUTE(A2,"",""))-1)),""),"","")

 

[AliGW]

I did it when you were moving on and I thought it would be confusing to change my strategy/request for someone else 10 replies into the thread.

DJ

Moving on???

 

[djl0525]

By "moving on", I am referring to when you said you were getting an error. I took that to mean you would not be able to help me. Then I started this thread. Then I suggested you save your file...

DJ

 

[Rick Rothstein]

How do I get the text between the last two backslashes in a string?

For example, C:\Users\Folder 1\Folder 2\Folder 3\Folder 4\Folder 5\Folder 6\Folder 7\Folder 8\

I want "Folder 8"

Give this formula a try...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",300)),600),300))

 

[deciog]

djl0525

=MID(CELL("filename",A1);FIND("]",CELL("filename",A1))+1,31)


Would that be what you want?


Decio

 

[AliGW]

Decio - this returns the sheet name, not the folder name.

 

[Aladin Akyurek]

In A1 enter:

=CELL("filename",$A1)<strike></strike>

In B1 enter:

=LEFT(A1,FIND("\[",A1))<strike></strike>

Finally, in C1 enter in order to get the folder housing the file in A1:

=SUBSTITUTE(REPLACE(B1,1,FIND("#",SUBSTITUTE(B1,"","#",LEN(B1)-LEN(SUBSTITUTE(B1,"",""))-1)),""),"","")