3 Replies Latest reply on Apr 30, 2013 7:37 AM by Mark Holtz

    how can i search though the text and find a word to create a calculated field

    Stephen Crocker

      twitter data has a source field that will show

      <a href=".........>Twitter for Iphone</a>  I want to just pull out the twitter for iphone not everything any easy way to do this in tableau or do i need to manipulate the data?

        • 1. Re: how can i search though the text and find a word to create a calculated field
          Mark Holtz

          Hi Stephen,

           

          I believe you can use the MID() function with a nested FIND() to accomplish this.

           

          If you know that the first time the ">" character will appear in the string is the end of the <a href="..." clause, and that the first </a> that will appear denotes your end-point, you can just use:

          MID([StringFieldToSearch], //tells formula the field to search

          FIND([StringFieldToSearch],'>')+1, //denotes start point, which is 1 character beyond the first ">"

          FIND([StringFieldToSearch],</a>)  //denotes end point, which is at character first character of "</a>"

           

          I find it helps when doing these nested MID+FIND functions to do them in steps.

          You can create a separate calculated field for "start point" and "end point" to help keep it easy to read.

           

          If your field holds more than one ">" character BEFORE the <a href="..." then please attach an example and we can get you what you need. (Have to use a series of MID-FIND's in that case), gets more complicated to explain...

          • 2. Re: how can i search though the text and find a word to create a calculated field
            Stephen Crocker

            Hi Mark, that got me about 99% of what I needed.  Here is the calcualtion I put in

            MID([source],

            FIND([source],'>')+1,

            FIND([source],'</'))

             

            I get something like Twitter for Iphone</a>  How can i remove the </a>?

             

            Awesome help and quick very much appreciated.

            1 of 1 people found this helpful
            • 3. Re: how can i search though the text and find a word to create a calculated field
              Mark Holtz

              Hi Stephen,

              You can certainly add more nested MID+FINDS into the calculated field that is giving you the result of "Twitter for Iphone</a>", but I think it's easier to just have multiple calculated fields that do it in "steps."

               

              And since you've already made sure the string starts with the substring you want, you can use LEFT instead of MID. LEFT is just the MID function starting at the leftmost character, and then you still specify the length to return.

               

              So just create a new calculated field as: LEFT([calc field 1],FIND([calc field 1],'</a>')). You might need to use RTRIM(LEFT([calc field 1],FIND([calc field 1],'</a>'))) if you have spaces at the end of your string before the "</a>."

               

              Cheers