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

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.