Excel 365. Splitting Cell into multiple rows whilst copying neighboring rows

Question

Not sure if there is a way to do this but here goes.

I have a table with Columns named Vuln, Vuln type, Devices, Comments. Each Devices cell has multiple values in it separated by a comma. I would like to split these out in tho the rows below, but also copy the details next to them in the other rows. Example below.

Original

Vuln1 | VulnType1 | Device 1, Device 2, Device 3 | Comment1
Vuln2 | VulnType2 | Device 1, Device 4, Device 5 | Comment2

Required Output

Vuln1 | VulnType1 | Device 1 | Comment1
Vuln1 | VulnType1 | Device 2 | Comment1
Vuln1 | VulnType1 | Device 3 | Comment1
Vuln2 | VulnType2 | Device 1 | Comment2
Vuln2 | VulnType2 | Device 4 | Comment2
Vuln2 | VulnType2 | Device 5 | Comment2

Thanks in advance

Answer 1

You can use PowerQuery to handle this.

1. Click Data > From Table. Depending on whether your table has headers or not, make sure the box that pops up is ticked appropriately and click Ok. This will load PowerQuery.

2. Once inside PowerQuery, select the column that has the comma-delimited data. Then, click Split Column and select By Delimiter.

3. Specify that the delimiter is a comma, that you want to split on each occurrence, and, under the advanced options, tick the radio box for Rows. You can leave the quote character as a double quote ("). Then click Ok.

4. At this point, PowerQuery shows the table with the values split into separate rows. If you click Close & Load, then the table will be saved to a new sheet with the data in this format.

5. Observe the data in a new sheet in a new table, formatted as the required output.

Answer 2

Thank you! This works like a charm.

Note: In Microsoft 365, we do not see this "From Table/Range", we can click on Data -> Get Data(Power Query) -> Select the xls -> Transform -> split -> rest the same as mentioned above