Forum Discussion
Beck Keller
Microsoft
Nov 09, 2018IF formula for text-string difference?
I have two columns of URLs. In some cases, the difference between the URLs is the same text string, in the middle of the URL. e.g., www.foo.com/grok-it | www.foo.com/bar/grok-it www.foo.com/...
Beck Keller
Microsoft
Nov 09, 2018Hi Sergei,
The third option--I'd like to check if the texts except the middle string are the same.
The third option--I'd like to check if the texts except the middle string are the same.
SergeiBaklan
Nov 09, 2018Diamond Contributor
Beck, if at least one slash in each URL
=LEFT(A2, SEARCH("/",A2)-1) & RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))=
LEFT(B2, SEARCH("/",B2)-1) & RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2,"/","@",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))),1))
and attached.
UPDATE. If no slashes as well
= IF(ISNUMBER(SEARCH("/",A2))*ISNUMBER(SEARCH("/",B2)),
LEFT(A2, SEARCH("/",A2)-1) & RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))=
LEFT(B2, SEARCH("/",B2)-1) & RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2,"/","@",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))),1)),
A2=B2)
ONE MORE UPDATE. With Power Query, but into separate table
let
Source = Excel.CurrentWorkbook(){[Name="URLs"]}[Content],
URL1ToList = Table.AddColumn(Source, "List1", each Text.Split(Text.Lower([URL 1]),"/")),
URL1NoMiddle = Table.AddColumn(URL1ToList, "URL1mod",
each List.First([List1]) & (if List.Count([List1]) = 1 then "" else List.Last([List1]))),
URL2ToList = Table.AddColumn(URL1NoMiddle, "List2", each Text.Split(Text.Lower([URL 2]),"/")),
URL2NoMiddle = Table.AddColumn(URL2ToList, "URL2mod",
each List.First([List2]) & (if List.Count([List2]) = 1 then "" else List.Last([List2]))),
CompareThem = Table.AddColumn(URL2NoMiddle, "Match", each if [URL1mod] = [URL2mod] then "Yes" else "No"),
RemoveUnused = Table.SelectColumns(CompareThem,{"URL 1", "URL 2", "Match"})
in
RemoveUnused
- Beck KellerNov 09, 2018
Microsoft
Interesting--thank you very much! I would just need a formula for each row, though (i.e., something that compares the URL in Column A to the URL in Column B, and either says "yes (the difference is 'bar')" or "no (the difference is not 'bar')").- SergeiBaklanNov 10, 2018Diamond Contributor
Beck, if you'd like to compare middle string specifically to 'bar' then
= IFERROR( IF( (LEFT(A2, SEARCH("/",A2)-1) & RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))= LEFT(B2, SEARCH("/",B2)-1) & RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2,"/","@",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))),1)))* (MID(B2, SEARCH("/",B2)+1,FIND("@",SUBSTITUTE(B2,"/","@",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))))-SEARCH("/",B2)-1)=$F$1), "Yes (the difference is '" & $F$1 & "')", "No (the difference is not '" & $F$1 & "')"), "No middle string")If the value of the middle string doesn't matter and you'd like only to mention its value, then
= IFERROR( IF( LEFT(A2, SEARCH("/",A2)-1) & RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2,"/","@",LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))),1))= LEFT(B2, SEARCH("/",B2)-1) & RIGHT(B2,LEN(B2)-FIND("@",SUBSTITUTE(B2,"/","@",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))),1)), "Yes (the difference is '" & MID(B2, SEARCH("/",B2)+1,FIND("@",SUBSTITUTE(B2,"/","@",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))))-SEARCH("/",B2)-1) & "')", "No (the difference is not middle string)"), "No (the difference is not middle string)")Both variant are in attached file.
It is assumed what the middle string, if exists, could be only in second column. If mix the formula will be bit more complicated, but idea is the same.