Split Cells into multiple rows

Occasionally, I get data in an excel format as shown below. I would like to split the second column of data into multiple rows and keep the first column consistent with the rows data.
The first column isn’t merged rows.

@DanFeliciano Can you attach a simple example Excel input file?

Hello, when I attempt to upload an Excel file, I get a message indicating Excel isn’t an allowable upload file type. I’m not sure the attached files will have the same effect.

DMAIC Phase Activities.xml (4.5 KB) DMAIC Phase Activities.csv (580 Bytes)

Sorry, my mistake. Can you email it to the support email?

1 Like

Ok, so it is 2 rows x 2 cols with data separated by carriage returns in the 2nd column.

You can do that it like this:

The Replace uses regex to replace the carriage returns with a delimiter.

dan2

This is then split using Split Col. Then Gather is used turn the columns into rows. The rest is just tidying.

DMAIC Phase Activities.csv (580 Bytes) DanF.transform (2.9 KB)

At some point we will probably allow regex in Split Col, which would reduce a step here and make it more versatile.

Note that the number of columns generated by Split Col may vary. But you can use Min new cols and Max new cols to control this.

There may be a faster/better way.

Great… Thanks it worked well

1 Like