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!

 

[WaterGypsy]

Try this formula in E2
=C2&"/"&RIGHT(D2,LEN(D2)-FIND("/",D2,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))))+1))

 

[ExcelToDAX]

WaterGypsy's works perfect. I noticed you only have Excel 2016, but if you happen to have Office 365, you can do this:

=LET(firstURL,TEXTSPLIT(A1,"/"),
secondURL,TEXTSPLIT(B1,"/"),
TEXTJOIN("/",FALSE,HSTACK(firstURL,TAKE(secondURL,,-SUM(IFNA(EXACT(firstURL,secondURL),1))))))

 

[NdNoviceHlp]

Here's my trial before finishing coffee..
Module code...

Public Function MergeHTTP(HTP1 As Range, HTP2 As Range) As String
Dim SplitA As Variant, SplitB As Variant
Dim Cnt As Integer, Cnt2 As Integer, TempStr As String
SplitA = Split(HTP1, "/")
SplitB = Split(HTP2, "/")
For Cnt = LBound(SplitA) To UBound(SplitA)
If Cnt <= UBound(SplitB) Then
Cnt2 = Cnt
Else
Cnt2 = UBound(SplitB)
End If
If SplitA(Cnt) = SplitB(Cnt2) Then
TempStr = TempStr & "/" & SplitB(Cnt2)
Else
TempStr = TempStr & "/" & SplitA(Cnt)
End If
Next Cnt
MergeHTTP = Right(TempStr, Len(TempStr) - 1)
End Function

To operate...

=MergeHTTP(A3,A4)

where A3 is the longer address. I like the formula approach but I thought I'd post anyways. HTH. Dave

 

[Micron]

I had a notion to code as well but dumped my reply when I saw the formula posted. I would also have used 2 arrays, however my thinking was to not put any part of "http//" into the array but rather to omit that from the string before looping over the first array. Then concatenate the values and slashes onto "http//", then start in the 2nd array at the nth+1 position (dictated by the size of the first array) and concatenate those. At least I thought it should be nth+1 but didn't try coding it.

 

[chive90]

Thanks for all of the replies, much appreciated. The formula works perfect so wasn't any need for me to use any of the code posted.

Anyone care to break the formula down for me and explain how it works?

 

[Peter_SSs]

If you are interested, here is a slightly shorter way
=REPLACE(D2,1,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))+1))-1,C2)

Red part works out how many / characters in C2 (7 in the example)
Blue part then notionally puts # character in place of the next (8th) / in D2
Purple part then replaces characters 1 to 1 less than where the # is with the contents of C2

 

[WaterGypsy]

Well I guess that I better do the decent thing and explain my workings

I built it up step by step and then merged it all into one string.

The first step was to find out how many / were in the first cell, by removing the / and checking the difference in length: LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))
Then find the position in the 2nd cell of that occurrence of / and replacing it with # : SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))
Add 1 to that so you can find the position of the next / in the second cell: FIND("/",D2,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))))+1)
So finally you can merge the first cell, an extra / and the rightmost characters into a single string

=C2&"/"&RIGHT(D2,LEN(D2)-FIND("/",D2,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))))+1))

There might be a more efficient way to do it and it will fail if there is a # in either of the cell values (I couldn't think of a character that isn't allowed in file names at the time)

 

[chive90]

If you are interested, here is a slightly shorter way
=REPLACE(D2,1,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))+1))-1,C2)

Red part works out how many / characters in C2 (7 in the example)
Blue part then notionally puts # character in place of the next (8th) / in D2
Purple part then replaces characters 1 to 1 less than where the # is with the contents of C2

Well I guess that I better do the decent thing and explain my workings

I built it up step by step and then merged it all into one string.

The first step was to find out how many / were in the first cell, by removing the / and checking the difference in length: LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))
Then find the position in the 2nd cell of that occurrence of / and replacing it with # : SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))
Add 1 to that so you can find the position of the next / in the second cell: FIND("/",D2,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))))+1)
So finally you can merge the first cell, an extra / and the rightmost characters into a single string

=C2&"/"&RIGHT(D2,LEN(D2)-FIND("/",D2,FIND("#",SUBSTITUTE(D2,"/","#",LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))))+1))

There might be a more efficient way to do it and it will fail if there is a # in either of the cell values (I couldn't think of a character that isn't allowed in file names at the time)

Thanks Both.

I think the # element is fine, there doesn't seem to be # in any file names I have here, but if anyone knows of a better character to use I can simply replace the # in the formula.

I have just noticed though that this is failing on one specific scenario, that is when there is a "/" within the Library Name. So to take one of my examples from above:

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

Where "libraryQ1/2016" is the name of the library.

It is then returning a Value Error. Is there any way around this?

 

[Peter_SSs]

I have just noticed though that this is failing on one specific scenario, that is when there is a "/" within the Library Name.

Could you clarify by clearly giving us the following?

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