Problem description

In the following Excel file, there is a row of two columns containing comma-separated values, as shown below:

We are trying to split each value by comma, reorganize the split values as a two-dimensional table, and put it in Sheet2. The desired result is as follows:

Solution

Use SPL XLL plug-in

Write the formula in a blank cell of Sheet2:

在 Sheet2 空白单元格填入公式：

=spl("=((a=?(1).(~.split@c()),a(1).([~,a(2)(#)])))",Sheet1!A1:B1)

As shown:

Return:

Explanation:

Split each member value into a sequence by comma. Reconstruct the second column as a two-dimensional table according to the first column.

Q & A Collection

https://stackoverflow.com/questions/63710376/how-to-vlookup-each-comma-separated-value-from-range