3 Replies Latest reply on Sep 8, 2016 8:03 AM by Tom W

    Excel Search Function as

    brandon.ercoline.0

      PREFACE:

      I use a formula in excel to extract a part of some URLs that share a similar starting point via the "BX_" beginning of the the string value and the "/" after that starting point is the end value (numeric for both).  In Excel MID and SEARCH works fine! 

       

      =MID(A1,SEARCH("BX_",A1),SEARCH("/",A1,SEARCH("BX_",A1))-SEARCH("BX_",A1))

      1. 1) It calculates the position of the shared characters “BX_” in the URL which is where I want to start
      2. 2) Then it calculates the ending position via the first “/” after the first position from "BX_" founded above
      3. 3) It then takes all of the characters between that start and end point and returns the value I want

       

      EXAMPLE:

      CELL A1: subdom.demo.com/dbace/US/BX_DEMOEVALV01A/anycontent.xhtml

      I want to extract the highlighted characters in

      CELL B1: =MID(A1,SEARCH("BX_",A1),SEARCH("/",A1,SEARCH("BX_",A1))-SEARCH("BX_",A1))

       

      This is the result – and it is correct (in excel).

       

      A

      B

      1

      subdom.demo.com/dbace/US/BX_DEMOEVALV01A/anycontent.xhtml

      BX_DEMOEVALV01A

       

       

      PROBLEM:

      I cannot find the proper command to do this for my imported URL values in Tableau.  I have tried a mix of MID and FIND, and [Pages] (the dimension I am trying to run the calculated formula for that contains the URLs), but it doesn’t seem to calculate the positions.  Pages is a string value, so I tried making it an integer value as well.  Still no luck.

       

      ASK:

      Is there a way to replicate the excel formula above for a dimension in Tableau?  The dimension is named [Pages]. 

      Keep in mind:

      • - I need to calculate it the way the excel formula did because the URLs are not all exactly the same path or length. 
      • - All values I want do start with “BX_” and the values end with a “/” 
        • 1. Re: Excel Search Function as
          Tom W

          My guess is you are using the MID/FIND functions wrong, perhaps ordering the parameters incorrectly.

           

          My suggestion when faced with problems like this is break it down into smaller pieces. Like firstly try and solve how to get the position of BX_

          Create a calculated field as;

          find([Pages],"BX_")

           

          Add that to your report as Discrete and you'll see it returns a number, that aligns to the expected value. Problem one solved!

           

          Now build on that to use the mid function and keep stepping it out like that.

           

          I.e. step two, use the MID function with no length specified;

          MID([Urlval],FIND([Urlval],"BX_"))

           

          Then figure out how to get the position of the first forward slash after the BX_

           

          Chain it all together.... It looks like;

           

          MID(

              [Urlval],

              //Start at the position of the BX_

              FIND([Urlval],"BX_"),

              //Length parameter

              //Calculate the position of the first / after the BX_ minus the position of BX_

              FIND([Urlval],"/",FIND([Urlval],"BX_"))-FIND([Urlval],"BX_")

          )     

          2 of 2 people found this helpful
          • 2. Re: Excel Search Function as
            brandon.ercoline.0

            Thanks, Tom W!  Worked flawlessly.  Sincerely appreciate the educational aspect as well on stepping through from step 1 and building out from there.

             

            Thnaks again,
            BE

            • 3. Re: Excel Search Function as
              Tom W

              No problem! Please mark the answer as correct to close the thread.