1 Reply Latest reply on Jul 25, 2016 3:03 AM by Carl Slifer

    Split cells in Excel

    shankar t

      Hi I have Excel sheet like this

       

      Column1        Column2    Column3

      A                     A1,B2,C3   Tes1, Test3,Test5

      B                    D2,B2,C1    Test2,Test3

      C                    C3,B1,A2    Test1, Test3,Test4

       

      I need to convert like this

       

      Column1    Column2     Column3

      A                     A1            Test1

      A                    A1              Test3

      A                   A1               Test5

      A                   B2              Test1

      A                   B2              Test3

      A                   B2              Test5

      A                   C3              Test1

      A                   C3               Test3

      A                   C3             Test5

      B                   D2            Test2

      B                   D2            Test3

      B                   B2              Test2

      B                   B2              Test3......

      Could you please give me guidence and steps on this once

        • 1. Re: Split cells in Excel
          Carl Slifer

          Howdy Shankar,

           

          Do you have access to any ETL tools? Are you able to make these changes in excel? Tableau can use functions called split and pivot. However this would  be a split on the second column. Pivoting on that and then it would be almost like pivoting on the third column.

           

          Really what we are asking for is 3x the rows we currently have. We want Column one to be the grouping point. And then we want the pairs from columns 2 and 3 such that row 1 is from the 1st column with pieces from column 2 and column 3 in the first split. I do not think that this is going to be possible just within tableau.

           

          You will most likely need to use the excel or another ETL tool to make this happen. To be fair this data structure is by far less than optimal.

           

          Cheers

          Carl Slifer

          InterWorks