Find matching parts

[chilly_bang]

Hi

I have in A and B urls. They are all different, but some of them, like in A2 and B2, have same IDs. Example:

[TABLE="width: 390"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]example.com/id/1/baz/[/TD]
[TD]site.com/id/2/ban/[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]example.com/id/3/boom/[/TD]
[TD]site.com/id/3/baff/[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]example.com/id/123/foo/[/TD]
[TD]site.com/id/321/bar/[/TD]
[/TR]
</tbody>[/TABLE]

According to this example i want to get

• into C1 - false,
• into C2 - true,
• into C3 - false

`True` means IDs are same.

How can i compare certain parts of strings to do so?

 

[Eric W]

Try in C1:

=TRIM(MID(SUBSTITUTE(A1,"/",REPT(" ",199)),400,199))=TRIM(MID(SUBSTITUTE(B1,"/",REPT(" ",199)),400,199))

if the IDs are always between the 2nd and 3rd slashes.

 

[Eric W]

A little longer:

=LEFT(SUBSTITUTE(MID(A1,FIND("/id/",A1)+4,99),"/",REPT(" ",99)),99)=LEFT(SUBSTITUTE(MID(B1,FIND("/id/",B1)+4,99),"/",REPT(" ",99)),99)

but it looks for /id/ in the text, and compares what follows it. There should always be a trailing slash.

 

[chilly_bang]

Thank you! The second one works like a charm - had some difficulties to adopt it to german Excel, but now it works as expected!

 

[Eric W]

Glad you got it working for you!

 

[chilly_bang]

I even realized some cases, where the formula doesn't work: there are cases, where at one of the compared urls the part after ID is missing, like https://www.example.com/id/2736/page.html (A1) and https://www.example.com/id/2736 (B1) or vise versa or parts after ID are missing on both compared urls. In such cases the formula gives false negatives out.