5 Replies Latest reply on Dec 4, 2018 8:51 AM by Ombir Rathee

    Working with e-mail subject strings (or, removing multiple instances of a string from a string with a calculation)

    Brendon Troy

      This is a question that's sort of about e-mails, but it's really a question about string calculations.

       

      I'm working with strings that are e-mail subject lines. In order to connect conversation "threads", I need to remove all of the "Re"s and "Fwd"s from the strings. I created a calculated field that is admittedly probably over-complicated to do just that for the first instances of "Re" and such:

      TRIM(

      right([Subject],

      LEN([Subject]) - (

      3*(find([Subject],"Re:")) + (

      3*(find([Subject],"FW:")) + (

      4*(find([Subject],"Fwd:"))

      )))))

      The good news is that this worked great:

      Screen Shot 2018-12-03 at 10.25.24 PM.png

      The bad news is that it only works for the first instance of "Re", etc. And, unfortunately, when e-mail conversation threads stretch on, you get subject lines like "Re: RE: RE: Re: Foo" and such:

      Screen Shot 2018-12-03 at 10.47.44 PM.png

      I can't think of a way, outside of really long workarounds, to clean up strings like that (especially given that they can stretch on to up to, say, five "Re"s.

       

      So, I guess this is really a question about string calculations, and I throw myself on the mercy of kind fellow Tableau authors in the hope that you'll teach me a thing or two. Thanks in advance!