TEXTSPLIT into Rows but Maintain Grouping

[rfhandel]

This is harder to explain than show. I have metadata being imported into an Excel spreadsheet that defines a single item (an image title) on a given row, but much of the metadata is in separate columns with each column cell containing data that is separated by delimiters (. These are all evenly spaced out. If I have 5 sizes, I will also have 5 weights and 5 prices. However, I will still only have one item (the image title).

I want to split this data into separate rows but keep the separated metadata rows grouped around the single-item image title.

I have managed to use TEXTSPLIT and TEXTJOIN to break up the metadata into separate rows; however, I cannot figure out how to keep the single-item cell with the first line/row of the metadata. I'll drop the samples in, but I am faking the desired results in the second sample and showing the outcome with highlighting. Hope it all makes sense. This might not be feasible--or it may just be obvious. Thanks in advance!

Array Formula.xlsx
	B	C	D	E	F	G	H	I
1	Title	Size	Weight	Cost	Split Title	Split Size	Split Weight	Split Cost
2	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	2.9 oz; 0.3 oz; 1.5 oz	50.00; 5.00; 22.00	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper	2.9 oz	50.00
3	3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper; 10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper; 8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper; 6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	6.3 oz; 3.9 oz; 2.8 oz; 1.8 oz; 0.3 oz; 1.2 oz	110.00; 65.00; 50.00; 35.00; 5.00; 22.00	3 little birds sing	5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
4						Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.5 oz	22.00
5						14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper	6.3 oz	110.00
6						10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper	3.9 oz	65.00
7						8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper	2.8 oz	50.00
8						6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper	1.8 oz	35.00
9						5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
10						Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.2 oz	22.00
Sheet3

Cell Formulas
Range	Formula
F2:F3,G2:I10	F2	=TEXTSPLIT(TEXTJOIN("; ",,B2:B3),,"; ")
Dynamic array formulas.

Array Formula.xlsx
	M	N	O	P	Q	R	S	T
1	Title	Size	Weight	Cost	Split Title	Split Size	Split Weight	Split Cost
2	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	2.9 oz; 0.3 oz; 1.5 oz	50.00; 5.00; 22.00	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper	2.9 oz	50.00
3	3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper; 10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper; 8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper; 6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	6.3 oz; 3.9 oz; 2.8 oz; 1.8 oz; 0.3 oz; 1.2 oz	110.00; 65.00; 50.00; 35.00; 5.00; 22.00		5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
4						Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.5 oz	22.00
5					3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper	6.3 oz	110.00
6						10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper	3.9 oz	65.00
7						8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper	2.8 oz	50.00
8						6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper	1.8 oz	35.00
9						5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
10						Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.2 oz	22.00
Sheet3

Cell Formulas
Range	Formula
Q2	Q2	=M2
R2:T10	R2	=TEXTSPLIT(TEXTJOIN("; ",,N2:N3),,"; ")
Q5	Q5	=M3
Dynamic array formulas.

 

[rfhandel]

PS: I am perfectly fine with the single-item being repeated in the results:

Split Title​	Split Cost​
123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper	2.9 oz	50.00
123 go	5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
123 go	Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.5 oz	22.00
3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper	6.3 oz	110.00
3 little birds sing	10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper	3.9 oz	65.00
3 little birds sing	8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper	2.8 oz	50.00
3 little birds sing	6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper	1.8 oz	35.00
3 little birds sing	5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
3 little birds sing	Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.2 oz	22.00

[th width="60.6498%"]

Split Size​

[/th]
[th width="11.7773%"]

Split Weight​

[/th]​

 

[Cubist]

This should give you all 4 columns:

Book1
	M	N	O	P	Q	R	S	T
1	Title	Size	Weight	Cost	Split Title	Split Size	Split Weight	Split Cost
2	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	2.9 oz; 0.3 oz; 1.5 oz	50.00; 5.00; 22.00	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper	2.9 oz	50.00
3	3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper; 10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper; 8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper; 6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	6.3 oz; 3.9 oz; 2.8 oz; 1.8 oz; 0.3 oz; 1.2 oz	110.00; 65.00; 50.00; 35.00; 5.00; 22.00		5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
4						Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.5 oz	22.00
5					3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper	6.3 oz	110.00
6						10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper	3.9 oz	65.00
7						8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper	2.8 oz	50.00
8						6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper	1.8 oz	35.00
9						5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
10						Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.2 oz	22.00
Sheet6

Cell Formulas
Range	Formula
Q2:T10	Q2	=IFNA(DROP(REDUCE("",BYROW(M2:P3,LAMBDA(bc,TEXTJOIN("|",,bc))),LAMBDA(a,b,VSTACK(a,REDUCE("",TEXTSPLIT(b,"|"),LAMBDA(x,y,HSTACK(x,TEXTSPLIT(y,,";"))))))),1,1),"")
Dynamic array formulas.

 

[Cubist]

This gives the split title column only.

Book1
	M	N	O	P	Q	R	S	T
1	Title	Size	Weight	Cost	Split Title	Split Size	Split Weight	Split Cost
2	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	2.9 oz; 0.3 oz; 1.5 oz	50.00; 5.00; 22.00	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper	2.9 oz	50.00
3	3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper; 10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper; 8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper; 6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	6.3 oz; 3.9 oz; 2.8 oz; 1.8 oz; 0.3 oz; 1.2 oz	110.00; 65.00; 50.00; 35.00; 5.00; 22.00	123 go	5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
4					123 go	Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.5 oz	22.00
5					3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper	6.3 oz	110.00
6					3 little birds sing	10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper	3.9 oz	65.00
7					3 little birds sing	8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper	2.8 oz	50.00
8					3 little birds sing	6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper	1.8 oz	35.00
9					3 little birds sing	5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
10					3 little birds sing	Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.2 oz	22.00
Sheet7

Cell Formulas
Range	Formula
Q2:Q10	Q2	=LET(a,LEN(N2:N3)-LEN(SUBSTITUTE(N2:N3,";",""))+1,TOCOL(IFS(a>=SEQUENCE(,MAX(a)),M2:M3),2))
R2:T10	R2	=TEXTSPLIT(TEXTJOIN("; ",,N2:N3),,"; ")
Dynamic array formulas.

 

[rfhandel]

This should give you all 4 columns:

Book1
	M	N	O	P	Q	R	S	T
1	Title	Size	Weight	Cost	Split Title	Split Size	Split Weight	Split Cost
2	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	2.9 oz; 0.3 oz; 1.5 oz	50.00; 5.00; 22.00	123 go	4 x 16 Lim. Ed. Print on 6.5 x 19 watercolor paper	2.9 oz	50.00
3	3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper; 10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper; 8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper; 6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper; 5 x 6 7/8 Greeting Card on watercolor paper; Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	6.3 oz; 3.9 oz; 2.8 oz; 1.8 oz; 0.3 oz; 1.2 oz	110.00; 65.00; 50.00; 35.00; 5.00; 22.00		5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
4						Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.5 oz	22.00
5					3 little birds sing	14 x 14 Lim. Ed. Print on 17 x 17 watercolor paper	6.3 oz	110.00
6						10 x 10 Lim. Ed. Print on 13 x 13 watercolor paper	3.9 oz	65.00
7						8 x 8 Lim. Ed. Print on 11 x 11 watercolor paper	2.8 oz	50.00
8						6 x 6 Lim. Ed. Print on 8 x 8 watercolor paper	1.8 oz	35.00
9						5 x 6 7/8 Greeting Card on watercolor paper	0.3 oz	5.00
10						Set of 5 Greeting Cards (5 x 6 7/8 watercolor paper)	1.2 oz	22.00
Sheet6

Cell Formulas
Range	Formula
Q2:T10	Q2	=IFNA(DROP(REDUCE("",BYROW(M2:P3,LAMBDA(bc,TEXTJOIN("|",,bc))),LAMBDA(a,b,VSTACK(a,REDUCE("",TEXTSPLIT(b,"|"),LAMBDA(x,y,HSTACK(x,TEXTSPLIT(y,,";"))))))),1,1),"")
Dynamic array formulas.

Thank you for the speedy reply and solution!! Could a formula like this be done by pulling data from a tab-delimited text file or Excel file? I am trying to streamline and automate a process where I 1) generate metadata from an Adobe app (Bridge), 2) export that metadata as a TXT file with relevant data, 3) merge and sort data into an Excel spreadsheet using formulas (such as the one above), and the 4) export the formulas' data into a values only spreadsheet.

 

[Cubist]

I believe it's possible to achieve this more efficiently and in an automated manner using Power Query (PQ). While I'm not an expert in PQ, I recommend posting your question in the Power Query sub-forum for more targeted advice. I'd also link this thread.