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/...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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
      Diamond Contributor
      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

       

      • Beck Keller's avatar
        Beck Keller
        Icon for Microsoft rankMicrosoft
        Nov 09, 2018
        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')").
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 09, 2018

      Okay, thank you. And middle string is everything between first and last slashes or nothing of only one slash. Is where an option if no slashes at all in URL?

Resources