Excel - How to split semi-colon separated values in multiple columns into rows-CodePudding

I have a table in Excel with following format

Col1	Col2	Col3
A;B;C;D	1;2;3;4	a;b;c;d
E;F	0;5	x;y

How do I split each corresponding values in columns to rows?

Col1	Col2	Col3
A	1	a
B	2	b
C	3	c
D	4	d
E	0	x
F	5	y

I tried power query (from Table/Range) -> Split column by delimiter (Advanced -> into rows). But I quickly get a lot of duplicated values and removing duplicates can be challenging.

Any suggestions on how to achieve this without a macro?

CodePudding user response：

In powerquery

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes (Source,List.Transform(Table.ColumnNames(Source), each {_, type text})),
TableTransform = Table.Combine(List.Transform(List.Transform(Table.ToRecords(#"Changed Type"), (x) => List.Transform(Record.ToList(x), each Text.Split(_,";"))), each Table.FromColumns(_, Table.ColumnNames(#"Changed Type"))))
in  TableTransform

CodePudding user response：

If you have the data in the following range: B1:D3 (including the header). You can achieve it for each column as follows in F2;

=TEXTSPLIT(TEXTJOIN(";",,B2:B3),,";")

For the first column and then to extend the formula to the following columns.

A more concise way to achieve it for a large number of columns is to use the following formula:

=TRANSPOSE(
  TEXTSPLIT(REDUCE("", 
  BYCOL(B2:D3, LAMBDA(x, TEXTJOIN(";",,x))), 
  LAMBDA(a,b,IF(a="", b, a&","&b))),";",",",,,""))

Notes:

Added more values to columns 1 and 2, to validate it works when we have different number of rows in the table.
We use pad_with input argument from TEXTSPLIT to consider columns with different number of elements and to pad is as blank.
REDUCE is used to convert the entire input to a single string, adding a column delimiter (,)
Finally using TEXTSPLIT to convert it back to an array format, we need to transpose the result because this function populates the information by row.