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
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.

Resources