Excel Search for Each Comma-separated Value from Strings

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:



Use SPL XLL plug-in

Write the formula in a blank cell of Sheet2:

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


As shown:





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