ACLMSPLIT

[Xlambda]

ACLMSPLIT splits chosen column of an array, by a chosen delimiter, embedding the result with the rest of the array. Calls ATEXTSPLIT , APPEND2H
Other function in minisheet ARANDOM

=LAMBDA(a,c,d,
    LET(l,COLUMNS(a),s,SEQUENCE(,l),
       x,INDEX(a,,c),y,ATEXTSPLIT(x,d,),z,FILTER(a,s<c),v,FILTER(a,s>c),
       IFERROR(APPEND2H(APPEND2H(z,y,),v,),"check data")
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	sample						=ACLMSPLIT(A2:E27,2,"_")							=ACLMSPLIT(G2#,3," ")
2	01-01-21	ID-16_Rey Bradly Shelton Ryburn	Gigi	6	MidWest		01-01-21	ID-16	Rey Bradly Shelton Ryburn	Gigi	6	MidWest		01-01-21	ID-16	Rey	Bradly	Shelton	Ryburn	Gigi	6	MidWest
3	01-09-20	ID-12_Alfred Jamel Poulsen Mattei	Sunset	3	MidWest		01-09-20	ID-12	Alfred Jamel Poulsen Mattei	Sunset	3	MidWest		01-09-20	ID-12	Alfred	Jamel	Poulsen	Mattei	Sunset	3	MidWest
4	01-04-20	ID-5_Mel Jere Scheidler	Sunset	3	MidWest		01-04-20	ID-5	Mel Jere Scheidler	Sunset	3	MidWest		01-04-20	ID-5	Mel	Jere	Scheidler		Sunset	3	MidWest
5	01-02-20	ID-14_Houston Grinnell Levison	Majestic	6	South		01-02-20	ID-14	Houston Grinnell Levison	Majestic	6	South		01-02-20	ID-14	Houston	Grinnell	Levison		Majestic	6	South
6	01-11-20	ID-12_Alfred Jamel Poulsen Mattei	Yanaki	12	East		01-11-20	ID-12	Alfred Jamel Poulsen Mattei	Yanaki	12	East		01-11-20	ID-12	Alfred	Jamel	Poulsen	Mattei	Yanaki	12	East
7	01-04-20	ID-9_Ricky Jamel Buskirk	Gigi	12	East		01-04-20	ID-9	Ricky Jamel Buskirk	Gigi	12	East		01-04-20	ID-9	Ricky	Jamel	Buskirk		Gigi	12	East
8	01-01-20	ID-9_Ricky Jamel Buskirk	Yanaki	3	MidWest		01-01-20	ID-9	Ricky Jamel Buskirk	Yanaki	3	MidWest		01-01-20	ID-9	Ricky	Jamel	Buskirk		Yanaki	3	MidWest
9	01-01-21	ID-4_Freddie Jere Otten Levison	Quad	3	Mexico		01-01-21	ID-4	Freddie Jere Otten Levison	Quad	3	Mexico		01-01-21	ID-4	Freddie	Jere	Otten	Levison	Quad	3	Mexico
10	01-11-20	ID-7_Houston Arnoldo Ybarbo	Majestic	12	MidWest		01-11-20	ID-7	Houston Arnoldo Ybarbo	Majestic	12	MidWest		01-11-20	ID-7	Houston	Arnoldo	Ybarbo		Majestic	12	MidWest
11	01-09-20	ID-11_Theodore Grinnell Speth	Yanaki	12	East		01-09-20	ID-11	Theodore Grinnell Speth	Yanaki	12	East		01-09-20	ID-11	Theodore	Grinnell	Speth		Yanaki	12	East
12	01-12-20	ID-12_Alfred Jamel Poulsen Mattei	Yanaki	18	Canada		01-12-20	ID-12	Alfred Jamel Poulsen Mattei	Yanaki	18	Canada		01-12-20	ID-12	Alfred	Jamel	Poulsen	Mattei	Yanaki	18	Canada
13	01-05-20	ID-4_Freddie Jere Otten Levison	Majestic	3	East		01-05-20	ID-4	Freddie Jere Otten Levison	Majestic	3	East		01-05-20	ID-4	Freddie	Jere	Otten	Levison	Majestic	3	East
14	01-07-20	ID-5_Mel Jere Scheidler	Crested	6	Canada		01-07-20	ID-5	Mel Jere Scheidler	Crested	6	Canada		01-07-20	ID-5	Mel	Jere	Scheidler		Crested	6	Canada
15	01-04-21	ID-12_Alfred Jamel Poulsen Mattei	Quad	12	South		01-04-21	ID-12	Alfred Jamel Poulsen Mattei	Quad	12	South		01-04-21	ID-12	Alfred	Jamel	Poulsen	Mattei	Quad	12	South
16	01-12-20	ID-9_Ricky Jamel Buskirk	Crested	3	West		01-12-20	ID-9	Ricky Jamel Buskirk	Crested	3	West		01-12-20	ID-9	Ricky	Jamel	Buskirk		Crested	3	West
17	01-02-20	ID-17_Rudolf Bradly Garth	Sunset	3	NorthWest		01-02-20	ID-17	Rudolf Bradly Garth	Sunset	3	NorthWest		01-02-20	ID-17	Rudolf	Bradly	Garth		Sunset	3	NorthWest
18	01-02-20	ID-19_Houston Myron Enderle	Majestic	18	East		01-02-20	ID-19	Houston Myron Enderle	Majestic	18	East		01-02-20	ID-19	Houston	Myron	Enderle		Majestic	18	East
19	01-12-20	ID-4_Freddie Jere Otten Levison	Sunset	3	NorthWest		01-12-20	ID-4	Freddie Jere Otten Levison	Sunset	3	NorthWest		01-12-20	ID-4	Freddie	Jere	Otten	Levison	Sunset	3	NorthWest
20	01-04-20	ID-18_Eduardo Jarrod Dawkins Fogle	Quad	18	Canada		01-04-20	ID-18	Eduardo Jarrod Dawkins Fogle	Quad	18	Canada		01-04-20	ID-18	Eduardo	Jarrod	Dawkins	Fogle	Quad	18	Canada
21	01-01-21	ID-15_Vance Harry Garth Levison	Yanaki	18	South		01-01-21	ID-15	Vance Harry Garth Levison	Yanaki	18	South		01-01-21	ID-15	Vance	Harry	Garth	Levison	Yanaki	18	South
22	01-02-20	ID-19_Houston Myron Enderle	Gigi	3	South		01-02-20	ID-19	Houston Myron Enderle	Gigi	3	South		01-02-20	ID-19	Houston	Myron	Enderle		Gigi	3	South
23	01-04-21	ID-11_Theodore Grinnell Speth	Gigi	12	MidWest		01-04-21	ID-11	Theodore Grinnell Speth	Gigi	12	MidWest		01-04-21	ID-11	Theodore	Grinnell	Speth		Gigi	12	MidWest
24	01-02-20	ID-4_Freddie Jere Otten Levison	Sunset	6	MidWest		01-02-20	ID-4	Freddie Jere Otten Levison	Sunset	6	MidWest		01-02-20	ID-4	Freddie	Jere	Otten	Levison	Sunset	6	MidWest
25	01-08-20	ID-18_Eduardo Jarrod Dawkins Fogle	Sunset	6	South		01-08-20	ID-18	Eduardo Jarrod Dawkins Fogle	Sunset	6	South		01-08-20	ID-18	Eduardo	Jarrod	Dawkins	Fogle	Sunset	6	South
26	01-02-20	ID-6_Jesus Pasquariello Ryburn	Yanaki	18	Mexico		01-02-20	ID-6	Jesus Pasquariello Ryburn	Yanaki	18	Mexico		01-02-20	ID-6	Jesus	Pasquariello	Ryburn		Yanaki	18	Mexico
27	01-02-20	ID-1_Theodore Kriger Ryburn	Majestic	12	NorthWest		01-02-20	ID-1	Theodore Kriger Ryburn	Majestic	12	NorthWest		01-02-20	ID-1	Theodore	Kriger	Ryburn		Majestic	12	NorthWest
28
29	Very useful when we need to create random array of products that have								other function on minisheet
30	for example, unit prices, because can not be shuffled, they should remain consistent								ARANDOM
31	or, like in the above array, the ID nr associated with names, should remain unique																	Since left array recalculates, we copy it as values, and, adding the headers, we obtain a nice useful random array
32	Date	ID-nr_Name	Prod.,Unt Price	Pack.	Region		Date	ID-nr	Name	Prod.	Unt.Price	Pack.	Region	Unt.Sold	Total
33	sample						=ACLMSPLIT(ACLMSPLIT(ARANDOM(A34:E59,20),2,"_"),4,",")							=RANDARRAY(ROWS(G34#),,2,10,1)				Date	ID-nr	Name	Prod.	Unt.Price	Pack.	Region	Unt.Sold	Total
34	01-01-21	ID-1_Theodore Kriger Ryburn	Quad,20.5	3	West		01-02-20	ID-11	Theodore Grinnell Speth	Yanaki	10.2	12	South	6	734.4			01-02-20	ID-4	Freddie Jere Otten Levison	Quad	20.5	3	MidWest	9	553.5
35	01-09-20	ID-2_Antione Myron Scheidler Fogle	Majestic,17.3	6	South		01-04-21	ID-19	Houston Myron Enderle	Quad	20.5	6	NorthWest	10	1230			01-02-20	ID-19	Houston Myron Enderle	Sunset	14.8	18	West	9	2397.6
36	01-04-20	ID-3_Antione Grinnell	Yanaki,10.2	12	MidWest		01-04-20	ID-19	Houston Myron Enderle	Quad	20.5	6	West	7	861			01-05-20	ID-14	Houston Grinnell Levison	Quad	20.5	12	NorthWest	7	1722
37	01-02-20	ID-4_Freddie Jere Otten Levison	Crested,9.85	18	Canada		01-01-21	ID-6	Jesus Pasquariello Ryburn	Gigi	18.7	6	Canada	10	1122			01-02-20	ID-20	Mel Jamel Valtierra Hoge	Quad	20.5	6	West	3	369
38	01-11-20	ID-5_Mel Jere Scheidler	Sunset,14.8		Mexico		01-05-20	ID-17	Rudolf Bradly Garth	Yanaki	10.2	3	South	4	122.4			01-08-20	ID-1	Theodore Kriger Ryburn	Yanaki	10.2	18	NorthWest	3	550.8
39	01-04-20	ID-6_Jesus Pasquariello Ryburn	Gigi,18.7		NorthWest		01-07-20	ID-2	Antione Myron Scheidler Fogle	Majestic	17.3	12	Canada	7	1453.2			01-11-20	ID-8	Rey Myron Mcglothlin Mattei	Majestic	17.3	12	NorthWest	4	830.4
40	01-01-20	ID-7_Houston Arnoldo Ybarbo			East		01-11-20	ID-6	Jesus Pasquariello Ryburn	Crested	9.85	18	MidWest	7	1241.1			01-02-20	ID-19	Houston Myron Enderle	Sunset	14.8	3	NorthWest	10	444
41	01-01-21	ID-8_Rey Myron Mcglothlin Mattei					01-02-20	ID-10	Mel Bradly Buskirk	Gigi	18.7	6	Mexico	8	897.6			01-01-21	ID-5	Mel Jere Scheidler	Yanaki	10.2	3	East	2	61.2
42	01-11-20	ID-9_Ricky Jamel Buskirk					01-12-20	ID-8	Rey Myron Mcglothlin Mattei	Sunset	14.8	6	Canada	4	355.2			01-04-21	ID-1	Theodore Kriger Ryburn	Crested	9.85	6	Mexico	6	354.6
43	01-09-20	ID-10_Mel Bradly Buskirk					01-01-20	ID-3	Antione Grinnell	Majestic	17.3	12	Canada	8	1660.8			01-04-20	ID-12	Alfred Jamel Poulsen Mattei	Yanaki	10.2	18	MidWest	3	550.8
44	01-12-20	ID-11_Theodore Grinnell Speth					01-01-21	ID-1	Theodore Kriger Ryburn	Sunset	14.8	6	Mexico	2	177.6			01-02-20	ID-14	Houston Grinnell Levison	Gigi	18.7	18	MidWest	7	2356.2
45	01-05-20	ID-12_Alfred Jamel Poulsen Mattei					01-02-20	ID-20	Mel Jamel Valtierra Hoge	Yanaki	10.2	12	MidWest	2	244.8			01-02-20	ID-1	Theodore Kriger Ryburn	Quad	20.5	12	MidWest	10	2460
46	01-07-20	ID-13_Rey Jamel Dawkins Fogle					01-02-20	ID-16	Rey Bradly Shelton Ryburn	Sunset	14.8	3	Mexico	4	177.6			01-04-21	ID-12	Alfred Jamel Poulsen Mattei	Gigi	18.7	6	Canada	6	673.2
47	01-04-21	ID-14_Houston Grinnell Levison					01-02-20	ID-11	Theodore Grinnell Speth	Gigi	18.7	18	Canada	6	2019.6			01-02-20	ID-11	Theodore Grinnell Speth	Sunset	14.8	12	South	3	532.8
48	01-12-20	ID-15_Vance Harry Garth Levison					01-04-21	ID-6	Jesus Pasquariello Ryburn	Crested	9.85	6	Mexico	2	118.2			01-02-20	ID-15	Vance Harry Garth Levison	Majestic	17.3	6	Mexico	3	311.4
49	01-02-20	ID-16_Rey Bradly Shelton Ryburn					01-09-20	ID-14	Houston Grinnell Levison	Sunset	14.8	18	West	7	1864.8			01-01-21	ID-2	Antione Myron Scheidler Fogle	Quad	20.5	18	MidWest	4	1476
50	01-02-20	ID-17_Rudolf Bradly Garth					01-02-20	ID-14	Houston Grinnell Levison	Yanaki	10.2	6	Mexico	6	367.2			01-12-20	ID-4	Freddie Jere Otten Levison	Gigi	18.7	6	MidWest	3	336.6
51	01-12-20	ID-18_Eduardo Jarrod Dawkins Fogle					01-02-20	ID-5	Mel Jere Scheidler	Majestic	17.3	18	NorthWest	9	2802.6			01-02-20	ID-12	Alfred Jamel Poulsen Mattei	Quad	20.5	3	MidWest	8	492
52	01-04-20	ID-19_Houston Myron Enderle					01-04-20	ID-6	Jesus Pasquariello Ryburn	Yanaki	10.2	3	Mexico	8	244.8			01-02-20	ID-9	Ricky Jamel Buskirk	Yanaki	10.2	3	Canada	7	214.2
53	01-01-21	ID-20_Mel Jamel Valtierra Hoge					01-04-20	ID-16	Rey Bradly Shelton Ryburn	Sunset	14.8	6	West	3	266.4			01-04-20	ID-4	Freddie Jere Otten Levison	Quad	20.5	6	MidWest	10	1230
54	01-02-20							↑	↑	↑	↑				=K34:K53*L34:L53*N34:N53
55	01-04-21							ID nr consistent with Name		Prduct consistent with Unit Price
56	01-02-20							(ex:ID-1 always associated with Theodore…)		(ex:Quad price always 20.5)
57	01-08-20
58	01-02-20
59	01-02-20
60
ACLMSPLIT post

Cell Formulas
Range	Formula
G1,N33,G33,N1	G1	=FORMULATEXT(G2)
G2:L27	G2	=ACLMSPLIT(A2:E27,2,"_")
N2:V27	N2	=ACLMSPLIT(G2#,3," ")
G34:M53	G34	=ACLMSPLIT(ACLMSPLIT(ARANDOM(A34:E59,20),2,"_"),4,",")
N34:N53	N34	=RANDARRAY(ROWS(G34#),,2,10,1)
O34:O53	O34	=K34:K53*L34:L53*N34:N53
O54	O54	=FORMULATEXT(O34)
Dynamic array formulas.

 

[Xlambda]

Improved function by adding default functionality for column index argument (cl=0 or ignored will split the last column of the array) and error handling. Calls ATEXTSPLIT , APPEND2H
a: array , cl: integer, column index of column to split, 0 or ignored will split the last column , d: string, delimiter
Other function in minisheet ARANDOM , ACLMJOIN

=LAMBDA(a,cl,d,
    LET(l,COLUMNS(a),s,SEQUENCE(,l),c,IF(cl,cl,l),x,INDEX(a,,c),y,ATEXTSPLIT(x,d,),z,FILTER(a,s<c),v,FILTER(a,s>c),
       IFERROR(IFS(AND(c=1,l=1),y,c=1,APPEND2H(y,v,),OR(c=0,c=l),APPEND2H(z,y,),TRUE,APPEND2H(APPEND2H(z,y,),v,)),"check data")
    )
)

LAMBDA 8.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	added default value for column argument, (0 or ignored, last column will get splitted) and some error handling
2	sample to create random data array										data joining,nested ACLMJOIN formulas		link ACLMJOIN post
3	Product	Brand	Type	Cost/u	S.Price/u	Sale Rep	Shop	Region	Manager		=ACLMJOIN(ACLMJOIN(A4:I18,1,5,,-1,"|"),3,,,,"|")
4	ID-3	Samsung	phone	230	300	Mel Dawkins	Shop C	East	John S		ID-3|Samsung|phone|230|300	Mel Dawkins	Shop C|East|John S
5	ID-6	Samsung	tablet	170	240	Winford Filer	Shop B	West	David M		ID-6|Samsung|tablet|170|240	Winford Filer	Shop B|West|David M
6	ID-4	Apple	phone	350	470	Miguel Valtierra	Shop A	East	John S		ID-4|Apple|phone|350|470	Miguel Valtierra	Shop A|East|John S
7	ID-1	Apple	tablet	280	350	Hollis Shelton	Shop D	West	Mike B		ID-1|Apple|tablet|280|350	Hollis Shelton	Shop D|West|Mike B
8	ID-7	Samsung	laptop	650	780	Davis Pasquariello	Shop E	North	Bill T		ID-7|Samsung|laptop|650|780	Davis Pasquariello	Shop E|North|Bill T
9	ID-8	Apple	laptop	420	510	Houston Grinnell	Shop F	South	Steve J		ID-8|Apple|laptop|420|510	Houston Grinnell	Shop F|South|Steve J
10	ID-2	HP	laptop	520	620	Antione Dechant					ID-2|HP|laptop|520|620	Antione Dechant
11	ID-5	HP	desktop	570	695	Theodore Pozo					ID-5|HP|desktop|570|695	Theodore Pozo
12	ID-9	Apple	desktop	810	999	Vance Mcglothlin					ID-9|Apple|desktop|810|999	Vance Mcglothlin
13						Lindsey Buskirk						Lindsey Buskirk
14						Rudolf Otten						Rudolf Otten
15						Ricky Kitterman						Ricky Kitterman
16						Jesus Garth						Jesus Garth			random array created consistent with initial data
17						Tom Enderle						Tom Enderle			first nested formula, cl argument 1
18						Freddie Poulsen						Freddie Poulsen			second nested formula, cl argument ignored (0) (splits last column)
19											random array with joined values				=ACLMSPLIT(ACLMSPLIT(K21#,1,"|"),,"|")
20											=ARANDOM(K4#,20)				Product	Brand	Type	Cost/u	S.Price/u	Sale Rep	Shop	Region	Manager
21	simple functionality explained										ID-1|Apple|tablet|280|350	Miguel Valtierra	Shop E|North|Bill T		ID-1	Apple	tablet	280	350	Miguel Valtierra	Shop E	North	Bill T
22	sample										ID-4|Apple|phone|350|470	Antione Dechant	Shop E|North|Bill T		ID-4	Apple	phone	350	470	Antione Dechant	Shop E	North	Bill T
23	A,10,x	red	X,Y,Z								ID-6|Samsung|tablet|170|240	Antione Dechant	Shop D|West|Mike B		ID-6	Samsung	tablet	170	240	Antione Dechant	Shop D	West	Mike B
24	B,20,y	blue									ID-1|Apple|tablet|280|350	Vance Mcglothlin	Shop B|West|David M		ID-1	Apple	tablet	280	350	Vance Mcglothlin	Shop B	West	David M
25	C,30,y	green	T,U,V								ID-4|Apple|phone|350|470	Jesus Garth	Shop B|West|David M		ID-4	Apple	phone	350	470	Jesus Garth	Shop B	West	David M
26											ID-1|Apple|tablet|280|350	Theodore Pozo	Shop E|North|Bill T		ID-1	Apple	tablet	280	350	Theodore Pozo	Shop E	North	Bill T
27	=ACLMSPLIT(A23:C25,1,",")										ID-6|Samsung|tablet|170|240	Hollis Shelton	Shop B|West|David M		ID-6	Samsung	tablet	170	240	Hollis Shelton	Shop B	West	David M
28	cl=1										ID-1|Apple|tablet|280|350	Winford Filer	Shop C|East|John S		ID-1	Apple	tablet	280	350	Winford Filer	Shop C	East	John S
29	A	10	x	red	X,Y,Z						ID-9|Apple|desktop|810|999	Freddie Poulsen	Shop D|West|Mike B		ID-9	Apple	desktop	810	999	Freddie Poulsen	Shop D	West	Mike B
30	B	20	y	blue							ID-6|Samsung|tablet|170|240	Ricky Kitterman	Shop B|West|David M		ID-6	Samsung	tablet	170	240	Ricky Kitterman	Shop B	West	David M
31	C	30	y	green	T,U,V						ID-2|HP|laptop|520|620	Hollis Shelton	Shop F|South|Steve J		ID-2	HP	laptop	520	620	Hollis Shelton	Shop F	South	Steve J
32											ID-3|Samsung|phone|230|300	Rudolf Otten	Shop D|West|Mike B		ID-3	Samsung	phone	230	300	Rudolf Otten	Shop D	West	Mike B
33	=ACLMSPLIT(A23:C25,,",")										ID-4|Apple|phone|350|470	Hollis Shelton	Shop F|South|Steve J		ID-4	Apple	phone	350	470	Hollis Shelton	Shop F	South	Steve J
34	cl ignored (or 0)										ID-1|Apple|tablet|280|350	Antione Dechant	Shop B|West|David M		ID-1	Apple	tablet	280	350	Antione Dechant	Shop B	West	David M
35	A,10,x	red	X	Y	Z						ID-1|Apple|tablet|280|350	Lindsey Buskirk	Shop A|East|John S		ID-1	Apple	tablet	280	350	Lindsey Buskirk	Shop A	East	John S
36	B,20,y	blue									ID-2|HP|laptop|520|620	Winford Filer	Shop F|South|Steve J		ID-2	HP	laptop	520	620	Winford Filer	Shop F	South	Steve J
37	C,30,y	green	T	U	V						ID-9|Apple|desktop|810|999	Mel Dawkins	Shop A|East|John S		ID-9	Apple	desktop	810	999	Mel Dawkins	Shop A	East	John S
38											ID-1|Apple|tablet|280|350	Mel Dawkins	Shop A|East|John S		ID-1	Apple	tablet	280	350	Mel Dawkins	Shop A	East	John S
39	=ACLMSPLIT(ACLMSPLIT(A23:C25,1,","),,",")										ID-2|HP|laptop|520|620	Theodore Pozo	Shop D|West|Mike B		ID-2	HP	laptop	520	620	Theodore Pozo	Shop D	West	Mike B
40	nested										ID-6|Samsung|tablet|170|240	Mel Dawkins	Shop A|East|John S		ID-6	Samsung	tablet	170	240	Mel Dawkins	Shop A	East	John S
41	A	10	x	red	X	Y	Z
42	B	20	y	blue
43	C	30	y	green	T	U	V
44
ACLMSPLIT post2

Cell Formulas
Range	Formula
K3,K20	K3	=FORMULATEXT(K4)
K4:M18	K4	=ACLMJOIN(ACLMJOIN(A4:I18,1,5,,-1,"|"),3,,,,"|")
O19,A39,A33,A27	O19	=FORMULATEXT(O21)
K21:M40	K21	=ARANDOM(K4#,20)
O21:W40	O21	=ACLMSPLIT(ACLMSPLIT(K21#,1,"|"),,"|")
A29:E31	A29	=ACLMSPLIT(A23:C25,1,",")
A35:E37	A35	=ACLMSPLIT(A23:C25,,",")
A41:G43	A41	=ACLMSPLIT(ACLMSPLIT(A23:C25,1,","),,",")
Dynamic array formulas.