Replace part of cell's text with another cell's text

[chive90]

In C2 I have a URL string, for example:

http://blah/test/library/folder/folder2/folder3

In D2 I have another URL string, for example:

http://blah/test/library/folder/folder2/folder20

/folder30/documentname.pdf

I need the text of C2 to replace the text of D2, but only up until the same number of text / combinations.

So from the above, only the element in bold would be replaced, with the remainder still remaining present in D2. The output of the above in E2 would be:

http://blah/test/library/folder/folder2/folder3/folder30/documentname.pdf

Note that folder3 replaces folder20 but the text after remains.

It won't always be the same number of text/ occurrences in Column C, for example C3 could be:

http://blah/test/library/folder/folder2/folder3/folder4/folder5

and D3 could be:

http://blah/test/library/folder/folder2/folder3/folder4/folder30/folder60/folder90/test.doc

But all of C needs to replace all of D up to that point, so again, above example would output as:

http://blah/test/library/folder/folder2/folder3/folder4/folder5/folder60/folder90/test.doc

Thanks!

 

[chive90]

Could you clarify by clearly giving us the following?

• The column B text
• The column C text
• The desired result

I assume you mean Column C and D rather than B and C as I have not mentioned B at all. Assuming you mean C and D, they are as follows:

Column C text = http://sitename/site/subsite/subsite2/Q1/2016
Column D text = http://sitename/site/subsite/subsite2/Q12016/template.doc

For what it's worth, Column B contains the library name, so in the above example, B = Q1/2016

Desired result in Column E = http://sitename/site/subsite/subsite2/Q1/2016/template.doc

The formula I've used (the one kindly posted by WaterGypsy above) seems to work for 99% of the data, but for the above example, it is throwing a #VALUE! error.

After analysing those that are throwing a #VALUE! error, it seems as though the only difference is where the library name (in the above example "Q1/2016") contains a "/"

I assume this is because the formula is counting / and having a / character in the desired replacement value is causing an issue?

Thanks for your help

 

[Peter_SSs]

I assume you mean Column C and D rather than B and C

I did. Sorry about that.
The good part is that it prompted you to tell us about column B which I think might help solve the problem.
Does this work better?

23 12 15.xlsm
	B	C	D	E
2	folder3	http://blah/test/library/folder/folder2/folder3	http://blah/test/library/folder/folder2/folder20/folder30/documentname.pdf	http://blah/test/library/folder/folder2/folder3/folder30/documentname.pdf
3	Q1/2016	http://sitename/site/subsite/subsite2/Q1/2016	http://sitename/site/subsite/subsite2/Q12016/template.doc	http://sitename/site/subsite/subsite2/Q1/2016/template.doc
Replace Text

Cell Formulas
Range	Formula
E2:E3	E2	=REPLACE(D2,1,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))+ISERROR(FIND("/",B2))))-1,C2)

 

[chive90]

I did. Sorry about that.
The good part is that it prompted you to tell us about column B which I think might help solve the problem.
Does this work better?

23 12 15.xlsm
	B	C	D	E
2	folder3	http://blah/test/library/folder/folder2/folder3	http://blah/test/library/folder/folder2/folder20/folder30/documentname.pdf	http://blah/test/library/folder/folder2/folder3/folder30/documentname.pdf
3	Q1/2016	http://sitename/site/subsite/subsite2/Q1/2016	http://sitename/site/subsite/subsite2/Q12016/template.doc	http://sitename/site/subsite/subsite2/Q1/2016/template.doc
Replace Text

Cell Formulas
Range	Formula
E2:E3	E2	=REPLACE(D2,1,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))+ISERROR(FIND("/",B2))))-1,C2)

Thank you very much. This seems to work better, and for the example I provided above, now provides the desired output rather than a #VALUE! error

There is still one #VALUE! error occurring though, again on a particularly niche library name, where the library name in B2 is as follows: "example database 5 (email to 17/12/23)"

Column C text = http://sitename/site/subsite/subsite2/example database 5 (email to 17/12/23)
Column D text = http://sitename/site/subsite/subsite2/differentlibraryname/test.pdf

Desired output = http://sitename/site/subsite/subsite2/example database 5 (email to 17/12/23)/test.pdf

Current output = #VALUE!

Tens of thousands of rows and this is the only one throwing the error so not sure if it has anything to do with bracket/date/forward slash combination, or string length, or something else.

Many thanks for your help!

 

[Peter_SSs]

Tens of thousands of rows and this is the only one throwing the error so not sure if it has anything to do with bracket/date/forward slash combination, or string length, or something else.

As far as I can see it is directly related to how many "/" characters are in column B. Try this

chive90.xlsm
	B	C	D	E
2	folder3	http://blah/test/library/folder/folder2/folder3	http://blah/test/library/folder/folder2/folder20/folder30/documentname.pdf	http://blah/test/library/folder/folder2/folder3/folder30/documentname.pdf
3	Q1/2016	http://sitename/site/subsite/subsite2/Q1/2016	http://sitename/site/subsite/subsite2/Q12016/template.doc	http://sitename/site/subsite/subsite2/Q1/2016/template.doc
4	example database 5 (email to 17/12/23)	http://sitename/site/subsite/subsite2/example database 5 (email to 17/12/23)	http://sitename/site/subsite/subsite2/differentlibraryname/test.pdf	http://sitename/site/subsite/subsite2/example database 5 (email to 17/12/23)/test.pdf
5	a/b/c/d/e/f	http://sitename/site/subsite/subsite2/a/b/c/d/e/f	http://sitename/site/subsite/subsite2/otherlibraryname/xxx.pdf	http://sitename/site/subsite/subsite2/a/b/c/d/e/f/xxx.pdf
Replace Text (2)

Cell Formulas
Range	Formula
E2:E5	E2	=REPLACE(D2,1,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))+1-(LEN(B2)-LEN(SUBSTITUTE(B2,"/","")))))-1,C2)

 

[chive90]

As far as I can see it is directly related to how many "/" characters are in column B. Try this

chive90.xlsm
	B	C	D	E
2	folder3	http://blah/test/library/folder/folder2/folder3	http://blah/test/library/folder/folder2/folder20/folder30/documentname.pdf	http://blah/test/library/folder/folder2/folder3/folder30/documentname.pdf
3	Q1/2016	http://sitename/site/subsite/subsite2/Q1/2016	http://sitename/site/subsite/subsite2/Q12016/template.doc	http://sitename/site/subsite/subsite2/Q1/2016/template.doc
4	example database 5 (email to 17/12/23)	http://sitename/site/subsite/subsite2/example database 5 (email to 17/12/23)	http://sitename/site/subsite/subsite2/differentlibraryname/test.pdf	http://sitename/site/subsite/subsite2/example database 5 (email to 17/12/23)/test.pdf
5	a/b/c/d/e/f	http://sitename/site/subsite/subsite2/a/b/c/d/e/f	http://sitename/site/subsite/subsite2/otherlibraryname/xxx.pdf	http://sitename/site/subsite/subsite2/a/b/c/d/e/f/xxx.pdf
Replace Text (2)

Cell Formulas
Range	Formula
E2:E5	E2	=REPLACE(D2,1,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))+1-(LEN(B2)-LEN(SUBSTITUTE(B2,"/","")))))-1,C2)

Works great, thank you so much! Do you mind explaining how the formula is working again like you did before with colours? I always like to understand what is happening so I can try and use it going forwards without always needing to ask on here

Thank you!

 

[Peter_SSs]

It is basically the same as before for red, blue and purple, but when working out what number the "next /" is we need to reduce that value by the number of "/" characters in column B.
That is what the green part here does

=REPLACE(D2,1,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))+1-(LEN(B2)-LEN(SUBSTITUTE(B2,"/","")))))-1,C2)

So ..
if there are no "/" characters in col B (row 2 above) then the "next /" is number of /s in C +1 - 0 That is (7+1-0 =8)
if there is 1 "/" character in col B (row 3) then the "next /" is number of /s in C +1 - 1 That is (7+1-1 =7)
if there are 5 "/" characters in col B (row 5 above) then the "next /" is number of /s in C +1 - 5 That is (11+1-5 =7)