Forum Discussion

Beck Keller's avatar
Beck Keller
Icon for Microsoft rankMicrosoft
Nov 09, 2018

IF 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/zap | www.foo.com/bar/zap

I want to create a formula for the third cell which flag instances where there is this difference. I'm pretty sure that an IF formula could do so, but I don't know how to construct it. Does anyone have any ideas?

Thanks in advance!

Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Nov 09, 2018

    Hi Beck,

     

    Not sure I understood you correctly - you'd like to flag if value is first column is not the same as in the second (=A1=B1), or to extract the text from the middle and show it; or check if texts except middle string are the same; or what?

    • Beck Keller's avatar
      Beck Keller
      Icon for Microsoft rankMicrosoft
      Nov 09, 2018
      Hi Sergei,

      The third option--I'd like to check if the texts except the middle string are the same.
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Nov 09, 2018

        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

         

Resources