4 Replies Latest reply on Feb 27, 2016 2:39 AM by Tharashasank Davuluru

    How to split a string

    Ramesh Chittiprolu

      Hi Community,

       

      My tableau connection is having a field ADDRESS and it is having the data in the following format,

       

      Ex:  1s Walker drive, STE 2020,,,,CHICAGO,60606,IL,US

             1 HUA TUO Road ZHANGJIANG HITECH PARK BKH, PUDONG NEW DISTRICT,,,, SHANGHAI,201203,CN

             110 TEMP ROAD, SRP-TEMPE,,,TEMPE,85284,AZ,US

       

      I should extract City, Postal code, State and country fields from the above data and my output should be like this below,

      City               Postal Code     State     Country

      CHICAGO     60606               IL          US

      SHANGHAI     201203                         CN

      TEMPE          85284               AZ          US

       

      There are addresses for different counties in different fomats like above(i shown above only for US and China) in my ADDRESS field,

      where Delimiter ,(comma) is not uniformly maintained for all address of different countries.

       

      Could someone help me to extract output like above?

        • 1. Re: How to split a string
          Mark Fraser

          Hi Ramesh

           

          I am going to move your thread to the main forum, it will get more attention there.

          and I can give you a huge head start with this link > String Calculations in Tableau - Clearly and Simply

           

          Cheers

          Mark

          1 of 1 people found this helpful
          • 2. Re: How to split a string
            nicolas.allerhand.0

            Hello,

             

            You can use split() function in a calculated field if the position of each data is always at the same position..

             

            Sample for the city :

            split([Adress],',',6) will work for Shangai and Chicago but not for Tempe

            1 of 1 people found this helpful
            • 3. Re: How to split a string
              Adam Crahen

              Hi Nicolas-

               

              Take a look at this thread.  There is an attached workbook on there where I did something similar recently.

              Is a custom split possible given the following scenario?

              1 of 1 people found this helpful
              • 4. Re: How to split a string
                Tharashasank Davuluru

                HI ,

                Split
                There are many columns that contain compound information such as phone numbers (area code), email addresses, URL’s and many more constructs that may be unique to your data. With “Split”, Tableau will automatically sample the data, detect multiple delimiters, and create new columns for you. I have a column with zip code and location combined together. A data value looks like this: 90001 (33.973271508000494, -118.24896959899968). Consider the crazy string calculations you would have to write to parse out the three parts.

                Regex (the ability to use regular expressions) is an improvement, and Tableau has those too now. But writing regex here is still unapproachable for many. Instead, right click and choose “Split” and you get:

                No prompts, no choices, just three nicely cleaned up columns. If the autosplit can’t find a structure to your field, it will ask you to provide a delimeter (typically a comma, space or other character) and you can always edit the resulting calculations with our simple SPLIT() function. That’s three new ways to easily split a column into just the parts you need.

                1 of 1 people found this helpful