Multi-Column Text to row table

ID	Acc_Desc	Acc_Numb	Box_1	Box_1_Split
0	CE	1	20	20
1	SA CH	3 4	50	30 20
2	CE SA CH	5 6 7	70	10 30 30

I have Data that looks like this, data within cells that need to be split are delimited with a spaces.

I want It to look like the below table, where the values with the same positions within the cells are placed into separate rows. SO ID 1 would split into two rows one with

1-SA-3-50-30 and the following row being 1-CH-4-50-20

ID	Acc_Desc	Acc_Numb	Box_1	Box_1_Split
0	CE	1	20	20
1	SA	3	50	30
1	CH	4	50	20
2	CE	5	70	10
2	SA	6	70	30
2	CH	7	70	30

There will be cells that need to be split into more than 3 rows as well

Thank you,

Ayush

Regex

Parse

Help

Spatial Analysis

Preparation

Accepted answers

CoG

Here is a simple solution that solves your problem as stated! I think this is quite a simple and elegant solution, which I did not expect when initially thinking about how to approach this.

Sample Workflow.yxmd

All comments

griffinwelsh

@Ayushkadel

Please see the attached solution workflow

Solution.yxmd

CoG

Here is a simple solution that solves your problem as stated! I think this is quite a simple and elegant solution, which I did not expect when initially thinking about how to approach this.

Sample Workflow.yxmd

griffinwelsh

Very cool. This is simpler and more dynamic than my solution and you can make it even simpler by using the multi-field formula.

Carolyn

@CoG - I can't get over how pretty your solution is. That's insanely beautiful.

Ayushkadel

Both are super impressive, ive been working in this all day and did not get very far. Thank you both so much

Ayushkadel

Hey Andrew,

Could you explain your solution to me i'm a bit green. It worked perfectly for me and I was able to build on this as well, but I'd like to understand what going on.

Thank you again,

Ayush Kadel

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335