Split cell based on another cell's value

[chive90]

In D2 I have a URL string
In E2 I have a Folder name, which is also contained within the URL string of D2

Can I split D2 where the E2 value occurs?

e.g.

D2 = http://blahblah/blah/test/name/aaa/bbb/ccc/hello.pdf
E2 = name

Desired value in D2 or a new column = http://blahblah/blah/test/
Desired value in E2 or a new column = name/aaa/bbb/ccc/hello.pdf

The E value in D won't always be in the same place for each row, so it needs to use the value rather than a count of "/", for example.

Thanks

 

[FormR]

Hi, here's one way you could try:

Book1
	D	E	F	G
1
2	http://blahblah/blah/test/name/aaa/bbb/ccc/hello.pdf	name	http://blahblah/blah/test/	name/aaa/bbb/ccc/hello.pdf
3	http://blahblah/blah/test/name/aaa/bbb/ccc/hello.pdf	test	http://blahblah/blah/	test/name/aaa/bbb/ccc/hello.pdf
4	http://blahblah/blah/test/name/aaa/bbb/ccc/hello.pdf	blah	http://blahblah/	blah/test/name/aaa/bbb/ccc/hello.pdf
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=LEFT(D2,FIND("/"&E2&"/",D2))
G2:G4	G2	=RIGHT(D2,LEN(D2)-FIND("/"&E2&"/",D2))

 

[chive90]

Thank you!

 

[Scott Huish]

Hi, here's one way you could try:

Book1
	D	E	F	G
1
2	http://blahblah/blah/test/name/aaa/bbb/ccc/hello.pdf	name	http://blahblah/blah/test/	name/aaa/bbb/ccc/hello.pdf
3	http://blahblah/blah/test/name/aaa/bbb/ccc/hello.pdf	test	http://blahblah/blah/	test/name/aaa/bbb/ccc/hello.pdf
4	http://blahblah/blah/test/name/aaa/bbb/ccc/hello.pdf	blah	http://blahblah/	blah/test/name/aaa/bbb/ccc/hello.pdf
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=LEFT(D2,FIND("/"&E2&"/",D2))
G2:G4	G2	=RIGHT(D2,LEN(D2)-FIND("/"&E2&"/",D2))

The G2 formula could just use Substitute using the value from F2:
=SUBSTITUTE(D2,F2,"")