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

 

[AlKey]

Or

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",LEN(A1))),LEN(A1)*2))

Excel 2016 (Windows) 32 bit
	A	B
1	C:\Users\Folder 1\Folder 2\Folder 3\Folder 4\Folder 5\Folder 6\Folder 7\Folder 8\	Folder 8
2	C:\Users\Folder 1\Folder 2\Folder 3\Folder 4\	Folder 4
Sheet5

 

[Rick Rothstein]

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

@Aladin,

Did this formula post the way you intended or am I reading it wrong? You appear to be searching for the # sign, substituting a # sign for the empty string and substituting the empty string for the empty string twice

 

[Aladin Akyurek]

@Aladin,

Did this formula post the way you intended or am I reading it wrong? You appear to be searching for the # sign, substituting a # sign for the empty string and substituting the empty string for the empty string twice

Let B1 houses say C:\Aladin\Excel\djl0525\

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

>>

SUBSTITUTE(REPLACE(B1,1,FIND("#",SUBSTITUTE(B1,"","#",3)),""),"","")

>>

SUBSTITUTE(REPLACE(B1,1,FIND("#","C:\Aladin\Excel#djl0525"),""),"","")

>>

SUBSTITUTE(REPLACE(B1,1,16,""),"","")

>>

SUBSTITUTE("djl0525","","")

>>

djl0525

 

[AlKey]

*** There appears to be a problem with forum's software. It compresses even single space. To avoid this place formula in inside the CODE tags.

 

[Aladin Akyurek]

*** There appears to be a problem with forum's software. It compresses even single space. To avoid this place formula in inside the CODE tags.

It has a problem with backslash apparently...

 

[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)),""),"$","")

Replace the $ sign with \ sign. (Board software seems to eat up the \ sign.)

 

[Aladin Akyurek]

@Aladin,

Did this formula post the way you intended or am I reading it wrong? You appear to be searching for the # sign, substituting a # sign for the empty string and substituting the empty string for the empty string twice

Let B1 houses say C:\Aladin\Excel\djl0525\

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

>>

SUBSTITUTE(REPLACE(B1,1,FIND("#",SUBSTITUTE(B1,"$","#",3)),""),"$","")

>>

SUBSTITUTE(REPLACE(B1,1,FIND("#","C:\Aladin\Excel#djl0525"),""),"$","")

>>

SUBSTITUTE(REPLACE(B1,1,16,""),"$","")

>>

SUBSTITUTE("djl0525","$","")

>>

djl0525

We have a problem with the backslash sign. The board software reduces a backslash between double quotes to just "".

So for the record, read $ as backslach.

 

[AlKey]

We have a problem with the backslash sign. The board software reduces a backslash between double quotes to just "".

So for the record, read $ as backslach.

As I said earlier: Use CODE tags when posting formula.

 

[Rick Rothstein]

Replace the $ sign with \ sign. (Board software seems to eat up the \ sign.)

I should have realized what was going on since I had to use the code tags to make the backslash stay visible in my earlier posting. By the way, I believe this formula will also work in cell C2 directly without needing the intermediary result in cell B1...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("\[",A1)-1),"",REPT(" ",300)),300))\

As I said earlier: Use CODE tags when posting formula.

Something has changed with this forum's comment processor then, hasn't it? I do not remember this being a problem in the past.

 

[Aladin Akyurek]

As I said earlier: Use CODE tags when posting formula.

Yes sir.