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?
Solved! Go to 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.
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