Multi-Column Text to row table

[Ayushkadel]

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

[griffinwelsh]

@Ayushkadel 

Please see the attached solution workflow

[AndrewDMerrill · Accepted solution]

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.

[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]

@AndrewDMerrill - 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