Parse Comma separated values in multiple columns to multiple columns

[Praneeth]

Hello all,

I need your help and suggestions with the following scenario. I have the following file 

ID	Fruit	#	Day
1	Apple, Banana, Mango	1,2,3,4	Mon, Tue
2	Apple, Banana, Mango, Kiwi	1,2,3,4,5,6	Tue, Wed, Thu, Fri
3	Apple, Banana	1,2,3	Tue, Wed, Mon, Sun
4	Apple, Banana, Mango, Plums	1,2,3,4,5,6,7	Tue, Wed, Mon, Sun
5	Apple, Banana, Mango, Strawberry	2,3,4,5,6	Mon, Tue, Wed, Thu, Sat

My requirement is to split these values to different columns like below. 

ID

Fruit_1

Fruit_2

Fruit_3

Fruit_4

#_1

#_2

#_3

#_4

#_5

#_6

Day_1

Day_2

Day_3

Day_4

Day_5

1

Apple

Banana

Mango

1

2

3

4

Mon

Tue

2

Apple

Banana

Mango

Kiwi

1

2

3

4

5

6

Tue

Wed

Thu

Fri

3

Apple

Banana

1

2

3

Tue

Wed

Mon

Sun

4

Apple

Banana

Mango

Plums

1

2

3

4

5

6

Tue

Wed

Mon

Sun

5

Apple

Banana

Mango

Strawberry

2

3

4

5

6

Mon

Tue

Wed

Thu

Sat

I understand we can use text-to-columns to get this output, but let's say I have 40 columns and I have to de-limit those values? Can't think of a solution. Can anyone help?

[danilang · Accepted solution]

Hi @Praneeth 

As is usually the case when dealing with multiple columns, the best strategy is to transpose all the columns you want to work on, perform the operation on the single pair of columns and Cross tab to get the final grid.

After transposing, split the value field to rows, and then number them sequentially within each group with the Multi row formula tool.  Use a formula tool to generate the final header names.  I replaced # with aaaaaa a since the Cross tab tool replaces non alpha and digit characters with underscore.  After the Cross Tab, rename the aaaaaa_ columns to #_

 

Another fun thing the Cross tab tool does is order the columns alphabetically.  You can produce the output in the original column order, but since you have varying numbers of items in the concatenated string, the logic will be quite complex.

Dan 

[Praneeth]

Thanks so much! works perfectly fine! 

[jroddy]

Pure Genius! That was exactly what I was looking for. Thanks.