Separate string based on another cells value and another parameter

[chive90]

Hello

In D2 I have a URL string.

Example: http://test/example/blah/name/aaa/bbb/ccc/document.xls

"name/" is also present in C2

In E2, I require the part of the string highlighted in bold (i.e. aaa/bbb/ccc/)

Is there a formula that I can use to only pick up the text in D2 after what the value is in C2 (in this case "name/") and to also not pick up anything after the final / (in this case "document.xls")

Thanks

 

[dalvin647]

Hi chive90,

This should work, thanks!

=TEXTBEFORE(TEXTAFTER(D2,C2),"/",3)&"/"

Logic as follows: Text after C2 -> then Text before 3rd instance of /

Edit: Added a &"/" as you need that last /

Production Template.xlsm
	C	D	E
2	name/	http://test/example/blah/name/aaa/bbb/ccc/document.xls	aaa/bbb/ccc
Sheet2

Cell Formulas
Range	Formula
E2	E2	=TEXTBEFORE(TEXTAFTER(D2,C2),"/",3)

 

[chive90]

Hi chive90,

This should work, thanks!

=TEXTBEFORE(TEXTAFTER(D2,C2),"/",3)&"/"

Logic as follows: Text after C2 -> then Text before 3rd instance of /

Edit: Added a &"/" as you need that last /

Production Template.xlsm
	C	D	E
2	name/	http://test/example/blah/name/aaa/bbb/ccc/document.xls	aaa/bbb/ccc
Sheet2

Cell Formulas
Range	Formula
E2	E2	=TEXTBEFORE(TEXTAFTER(D2,C2),"/",3)

Hey, thanks for replying. I should have probably stated that I am using Excel 2016 and I don't believe the TEXTBEFORE or TEXTAFTER functions work in 2016?

Is there any alternative that can be used which will achieve the same result?

Thank you

 

[Fluff]

I should have probably stated that I am using Excel 2016

No need, it's shows that in your profile.

 

[Fluff]

How about

Fluff.xlsm
	C	D	E
1
2	name/	http://test/example/blah/name/aaa/bbb/ccc/document.xls	aaa/bbb/ccc
Master

Cell Formulas
Range	Formula
E2	E2	=REPLACE(LEFT(D2,FIND("^",SUBSTITUTE(D2,"/","^",LEN(D2)-LEN(SUBSTITUTE(D2,"/",""))))-1),1,SEARCH(C2,D2)+LEN(C2)-1,"")