Convert list with commas and spaces in single cell, into list in separate rows - Assistance

[Quiinn]

I'm looking to convert an entire column within a spreadsheet, to ensure each cell containing a list is converted into rows underneath with single postcodes as per the example below.
I wasn't sure if there was a function or a formula I could use to execute this.

Any help would be much appreciated!

All I need is to take all of the data in column B and extend it to only feature one district code in each cell.

E.g,

Postcode area	Postcode districts	Post Town	Former postal county
EX	EX1, EX2, EX3, EX4, EX5, EX6	Exeter	Devon

To instead be listed as;

Postcode area	Postcode districts	Post Town	Former postal county
EX	EX1	Exeter	Devon
EX	EX2	Exeter	Devon
EX	EX3	Exeter	Devon
EX	EX4	Exeter	Devon
EX	EX5	Exeter	Devon

Many thanks,
Q

 

[JamesCanale]

This got ugly fast. I presume that you wanted to handle multiple rows of inputs. It only works right if the postcodes are always three characters separated by a comma-space. If it's going to go to EX10 or more, I'll have to think of something.

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G	H	I
1	Post Area	Codes	Town	Former County		Post Area	Codes	Town	Former County
2	EX	EX1, EX2, EX3, EX4, EX5, EX6	Exeter	Devon		EX	EX1	Exeter	Devon
3	JA	JA1, JA2, JA3	Jamboville	Goosetown		EX	EX2	Exeter	Devon
4	BO	BO1, BO2, BO3, BO4	Bozoton	Clownville		EX	EX3	Exeter	Devon
5						EX	EX4	Exeter	Devon
6						EX	EX5	Exeter	Devon
7						EX	EX6	Exeter	Devon
8						JA	JA1	Jamboville	Goosetown
9						JA	JA2	Jamboville	Goosetown
10						JA	JA3	Jamboville	Goosetown
11						BO	BO1	Bozoton	Clownville
12						BO	BO2	Bozoton	Clownville
13						BO	BO3	Bozoton	Clownville
14						BO	BO4	Bozoton	Clownville
Sheet6

Cell Formulas
Range	Formula
F2:I14	F2	=LET(w,A2:A4,x,B2:B4,y,C2:C4,z,D2:D4,a,LEN(x)-LEN(SUBSTITUTE(x,",",""))+1,b,MAKEARRAY(ROWS(a),ROWS(a),LAMBDA(r,c,IF(r>=c,1,0))),c,TRANSPOSE(MMULT(b,a)),d,SEQUENCE(SUM(a)),e,IF(d<=c,d,0),f,HSTACK(SEQUENCE(ROWS(d),1,0,0),DROP(e,,-1)),g,e-BYCOL(f,LAMBDA(array,MAX(array))),h,BYROW(g,LAMBDA(array,MAX(array))),ee,IF(d<=c,1,0),eee,1+ROWS(w)-MMULT(ee,SEQUENCE(ROWS(w),1,1,0)),n,INDEX(x,eee),o,MID(n,5*(h-1)+1,3),q,o,m,HSTACK(INDEX(w,eee),q,INDEX(y,eee),INDEX(z,eee)),m)
Dynamic array formulas.

 

[JamesCanale]

This is better for the variable length Codes:

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G	H	I
1	Post Area	Codes	Town	Former County		Post Area	Codes	Town	Former County
2	EX	EX1, EX2, EX3, EX4, EX5, EX6	Exeter	Devon		EX	EX1	Exeter	Devon
3	JA	JA1, JA11, JA3	Jamboville	Goosetown		EX	EX2	Exeter	Devon
4	BO	BO1, BO2, BO3, BO4	Bozoton	Clownville		EX	EX3	Exeter	Devon
5						EX	EX4	Exeter	Devon
6						EX	EX5	Exeter	Devon
7						EX	EX6	Exeter	Devon
8						JA	JA1	Jamboville	Goosetown
9						JA	JA11	Jamboville	Goosetown
10						JA	JA3	Jamboville	Goosetown
11						BO	BO1	Bozoton	Clownville
12						BO	BO2	Bozoton	Clownville
13						BO	BO3	Bozoton	Clownville
14						BO	BO4	Bozoton	Clownville
Sheet6

Cell Formulas
Range	Formula
F2:I14	F2	=LET(w,A2:A4,x,B2:B4,y,C2:C4,z,D2:D4,a,LEN(x)-LEN(SUBSTITUTE(x,",",""))+1,b,MAKEARRAY(ROWS(a),ROWS(a),LAMBDA(r,c,IF(r>=c,1,0))),c,TRANSPOSE(MMULT(b,a)),d,SEQUENCE(SUM(a)),e,IF(d<=c,d,0),f,HSTACK(SEQUENCE(ROWS(d),1,0,0),DROP(e,,-1)),g,e-BYCOL(f,LAMBDA(array,MAX(array))),h,BYROW(g,LAMBDA(array,MAX(array))),ee,IF(d<=c,1,0),eee,1+ROWS(w)-MMULT(ee,SEQUENCE(ROWS(w),1,1,0)),n,INDEX(x,eee),o,IF(h=1,n,TEXTAFTER(n,", ",h-1)),p,LEFT(o,IFERROR(SEARCH(", ",o),99)-1),m,HSTACK(INDEX(w,eee),p,INDEX(y,eee),INDEX(z,eee)),m)
Dynamic array formulas.

 

[Fluff]

Another option

=LET(rs,BYROW(B2:B4,LAMBDA(br,ROWS(TEXTSPLIT(br,,",")))),HSTACK(TOCOL(IF(SEQUENCE(,MAX(rs))<=rs,A2:A4,1/0),2),DROP(REDUCE("",B2:B4,LAMBDA(x,y,VSTACK(x,TRIM(TEXTSPLIT(y,,","))))),1),TOCOL(IF(SEQUENCE(,MAX(rs))<=rs,C2:C4,1/0),2),TOCOL(IF(SEQUENCE(,MAX(rs))<=rs,D2:D4,1/0),2)))

 

[Quiinn]

This got ugly fast. I presume that you wanted to handle multiple rows of inputs. It only works right if the postcodes are always three characters separated by a comma-space. If it's going to go to EX10 or more, I'll have to think of something.

MrExcelPlayground18.xlsx
	A	B	C	D	E	F	G	H	I
1	Post Area	Codes	Town	Former County		Post Area	Codes	Town	Former County
2	EX	EX1, EX2, EX3, EX4, EX5, EX6	Exeter	Devon		EX	EX1	Exeter	Devon
3	JA	JA1, JA2, JA3	Jamboville	Goosetown		EX	EX2	Exeter	Devon
4	BO	BO1, BO2, BO3, BO4	Bozoton	Clownville		EX	EX3	Exeter	Devon
5						EX	EX4	Exeter	Devon
6						EX	EX5	Exeter	Devon
7						EX	EX6	Exeter	Devon
8						JA	JA1	Jamboville	Goosetown
9						JA	JA2	Jamboville	Goosetown
10						JA	JA3	Jamboville	Goosetown
11						BO	BO1	Bozoton	Clownville
12						BO	BO2	Bozoton	Clownville
13						BO	BO3	Bozoton	Clownville
14						BO	BO4	Bozoton	Clownville
Sheet6

Cell Formulas
Range	Formula
F2:I14	F2	=LET(w,A2:A4,x,B2:B4,y,C2:C4,z,D2:D4,a,LEN(x)-LEN(SUBSTITUTE(x,",",""))+1,b,MAKEARRAY(ROWS(a),ROWS(a),LAMBDA(r,c,IF(r>=c,1,0))),c,TRANSPOSE(MMULT(b,a)),d,SEQUENCE(SUM(a)),e,IF(d<=c,d,0),f,HSTACK(SEQUENCE(ROWS(d),1,0,0),DROP(e,,-1)),g,e-BYCOL(f,LAMBDA(array,MAX(array))),h,BYROW(g,LAMBDA(array,MAX(array))),ee,IF(d<=c,1,0),eee,1+ROWS(w)-MMULT(ee,SEQUENCE(ROWS(w),1,1,0)),n,INDEX(x,eee),o,MID(n,5*(h-1)+1,3),q,o,m,HSTACK(INDEX(w,eee),q,INDEX(y,eee),INDEX(z,eee)),m)
Dynamic array formulas.

Thanks James, unfortunately it's just giving me a SPILL error despite trying both formula's you provided.

 

[Fluff]

Make sure that all cells below & to the right of the formula are totally empty & then try the formula again.

 

[Rick Rothstein]

One more formula for you to try...

=VSTACK(A1:D1,TEXTSPLIT(TEXTJOIN(", ",,MAP(A2:A4,B2:B4,C2:C4,D2:D4,LAMBDA(a,b,c,d,a&"/"&SUBSTITUTE(b,", ","/"&c&"/"&d&", "&a&"/")&"/"&c&"/"&d))),"/",", "))

Note: If you have a lot of rows to process, this will probably fail to work because of length limitations in TEXTJOIN.