Split rows into multiple based on word or comma count

Hi Experts,

As a requirement where I to put 5(let) words in each row. but in input file I am getting more in each row. Example(Sample)

Input File-

Language	Name	Market
Hindi	Akash Kumar,Aman,Shyam,Lokesh,Mukesh,Tushar,Saha	IN
Punjabi	Charan,Sukh,AK Singh,Lovish,Kush,Kumar	US

So here In Name column we have 7 words(asperated by comma) in row 1 and in row 2 we have 6. So I just need 5 name in row 1 hence Tushar and Saha should come in 2nd row in output. Is this possible in ALteryx?

Output Expected-

Language	Name	Market
Hindi	Akash Kumar,Aman,Shyam,Lokesh,Mukesh	IN
Hindi	Tushar,Saha	IN
Punjabi	Charan,Sukh,AK Singh,Lovish,Kush	US
Punjabi	Kumar	US

Thanks in advance

Developer

Transformation

Data Investigation

Accepted answers

Jean-Balteryx

Hi @Anuj_saxena01 ,

here is a workflow that splits your rows then rebuild them with a limit of 5 name for each row.

If you want to change how many names you get per row, you just need to change the value of the modulo function in the second "Multi-Row formula tool".

five_per_row.yxmd

atcodedog05

Hi @Anuj_saxena01

Here is how you can do it.

Workflow:

1. Split names into rows using text to col.

2. Using multi-row formula tool create groups sub id.

3. Using formula grouping into 5 names.

4. Summarize on group to conact names of 5.

5. Using select tool keep required columns.

Hope this helps : )

5 names.yxmd

All comments

Jean-Balteryx

Hi @Anuj_saxena01 ,

here is a workflow that splits your rows then rebuild them with a limit of 5 name for each row.

If you want to change how many names you get per row, you just need to change the value of the modulo function in the second "Multi-Row formula tool".