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 10, 2018

        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.

  • 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