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.
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.
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
@DanFeliciano
Please note that there is now an option for splitting columns using a regular expression. See: