2 Replies Latest reply on Oct 12, 2018 5:26 PM by Shinichiro Murakami

    Comparing Calculated Field Strings

    Like Like

      Issue:

      I'm trying to wrangle several excel sheets together.  One column in each sheet has matching data (in this example, websites).  The master page (sheet1) has ".com, .net, .co.jp, etc." appended to each domain, however, some pages (sheet2) do not have ".com, .net, .co.jp, etc.".  I only care about the sites listed in sheet1, thus should be left joined (see image below).  I can create a calculated field that will remove the suffix, but that's after the "join" has already happened.

       

      Question:

      How do I easily allow the calculation to occur before the join so the websites will match up (e.g. "Apple.com" (sheet1) will match to "Apple" (sheet2))?

       

      Sample workbook attached.

       

       

      ---------------Calculation to remove website suffix---------------------

      //Remove .com from Sheet2 websites

      IF RIGHT([Website (Sheet1)],4)=".com"

      Then MID([Website (Sheet1)],1,FIND([Website (Sheet1)],'.com')-1)

       

      ELSE

       

      [Website (Sheet1)]

       

      END

      ---------------Calculation to remove website suffix---------------------

       

       

       

       

      WebsiteGraphic1.PNGWebsiteGraphic2.PNG