Convert wide to tall format after splitting to columns

[96sudeshnasen]

Hi all,

I have a table that looks like this:

Name	Customers
A	C1,C2,C5
B	C2,C8
C	C1,C9,C4,C7,C3
D	C3,C1

The column customers have n number of customers mapped and its not fixed. So while splitting to columns by "," as the delimiter, how do I know how many columns it should be split into?

I want to do a transpose on the resulting splitted columns to make them look like this:

Name	Customer
A	C1
A	C2
A	C5
B	C2
B	C8
C	C1
C	C9
C	C4
C	C7
C	C3
D	C3
D	C1

How do I do this?

[pimmobrown · Accepted solution]

Hi Sudeshna,

Try this:

Use text to columns tool. But in the configuration window select "Split to rows" and select "Skip empty columns" in advanced options. In this way, you neither have to use two tools, now have to set a limit for the number of columns to split to.

[MarqueeCrew]

Use a text to columns tool!

 Configure it to ROWS

 that will do the trick (excerpt column name will be customers instead of customer. 

cheers,

 mark