Separate values (in a row) by rows

I need help and experise on how can I separate values (separated by (alt+enter)) within row and create a new row for each separated values. - similar to text to columns functionality in excel however it lacks options for values separated by (alt+enter).

Kindly refer to the attached file:

'Problem row' sheet is the raw file that needs to be updated

'Expected output' sheet is the ambition

Rows for splitting.xlsx

Workflow

Accepted answers

BenMoss

Hi @JoseG_Uni

Here is my attempt; i've used a transpose tool and the text to columns tool with the new line delimiter to parse the data. I then cross-tab this back into the format desired before filling in appropriate columns.

I've ammended the incoming data so it has two rows, just to ensure that the workflow would still work (under the assumption you have multiple rows for each of the different supervisory organisations. If this is not the case this workflow will still work fine).

Ben

Parse New Lines.yxzp

All comments

BenMoss

Hi @JoseG_Uni

Ben

Parse New Lines.yxzp

MarqueeCrew

Try this:

Text to columns with \n as the delimeter then split to rows.

Cheers,
Mark

JoseG_Uni

Thank you @BenMoss. It works for the rest of the file.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335