ATTSPLIT

[Xlambda]

ATTSPLIT Array To Text Split , splits back to arrays an array of data obtained by ARRAYTOTEXT function, format argument 1 .
Inspired by ExcelIsFun YT video Join text items in a cell using TEXTJOIN or ARRAYTOTEXT functions. Excel Magic Trick 1741
Other functions on minisheet: AREMOVE , ATEXTSPLIT , AKEEP , AFLAT ASTACK

=LAMBDA(a,
    LET(k,OR(ISERROR(SEARCH(";",a))),
       IF(k,"not proper data format",ATEXTSPLIT(AFLAT(ATEXTSPLIT(AREMOVE(a,"{}""""",),";",1)),",",1))
    )
)

LAMBDA 10.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	AB	AC	AD	AE	AF	AG	AH
1	sample array		b	2	y
2		g			h
3		4	3
4	Post 1
5	Functionality of ARRAYTOTEXT						Ways to get to initial array
6	format argument 0 or omitted						step 1				step 2													step 3
7	=ARRAYTOTEXT(B1:E3)						remove spaces				split with ATEXTSPLIT",",include empty=1													we need an unknow extra argument
8	, b, 2, y, g, , , h, 4, 3, ,						=AREMOVE(A8," ",)				=ATEXTSPLIT(G9,",",1)													for array width,(ASTACK argument)
9	The function adds a space btwn values						,b,2,y,g,,,h,4,3,,					b	2	y	g			h	4	3				=ASTACK(K9#,4)
10	and "," delimiter																								b	2	y
11	The blanks will become spaces																							g			h
12																								4	3
13	format argument 1						step 1									step 2								step 3
14	=ARRAYTOTEXT(B1:E3,1)						remove {}" chars									split with ATEXTSPLIT for ";" del.								split with ATEXTSPLIT "," del.
15	{,"b",2,"y";"g",,,"h";4,3,,}						=AREMOVE(A15,"{}""""",)									and stack the results with AFLAT								include empty argument=1
16	No spaces added btwn						,b,2,y;g,,,h;4,3,,									=AFLAT(ATEXTSPLIT(G16,";",))								=ATEXTSPLIT(P17#,",",1)
17	delimiter and values						Note:									,b,2,y									b	2	y
18	We get a";" at the end						To include " as char to be removed									g,,,h								g			h
19	of each row						we have to use 4 x" , as in """"									4,3,,								4	3
20	Text values btwn quotes
21							Other approach is to find out nr of columns of the array
22	othe functions on minisheet						that will be nr of "," up to first ";", using AKEEP only ",;" chars and SEARCH position of ";"
23	AREMOVE						=AKEEP(G16,",;",)					=SEARCH(";",G24)
24	ATEXTSPLIT						,,,;,,,;,,,					4
25	AKEEP						substitute ";" with ",", ATEXTSPLIT for ","
26	AFLAT						and ASTACK for nr of columns, like first scenario
27	ASTACK						=SUBSTITUTE(G16,";",",")					=ATEXTSPLIT(G28,",",1)													=ASTACK(L28#,L24)
28							,b,2,y,g,,,h,4,3,,						b	2	y	g			h	4	3					b	2	y
29																									g			h
30																									4	3
31
ATTSPLIT post 1

Cell Formulas
Range	Formula
A7,Y27,L27,G27,L23,G23,X16,P16,G15,A14,X9,K8,G8	A7	=FORMULATEXT(A8)
A8	A8	=ARRAYTOTEXT(B1:E3)
G9	G9	=AREMOVE(A8," ",)
K9:V9	K9	=ATEXTSPLIT(G9,",",1)
X10:AA12	X10	=ASTACK(K9#,4)
A15	A15	=ARRAYTOTEXT(B1:E3,1)
G16	G16	=AREMOVE(A15,"{}""""",)
P17:P19	P17	=AFLAT(ATEXTSPLIT(G16,";",))
X17:AA19	X17	=ATEXTSPLIT(P17#,",",1)
G24	G24	=AKEEP(G16,",;",)
L24	L24	=SEARCH(";",G24)
G28	G28	=SUBSTITUTE(G16,";",",")
L28:W28	L28	=ATEXTSPLIT(G28,",",1)
Y28:AB30	Y28	=ASTACK(L28#,L24)
Dynamic array formulas.

 

[Xlambda]

Post 2. Other functions on minisheet AREMOVE , ATEXTSPLIT , AFLAT

LAMBDA 10.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	Post 2: Handling an array of arraytotext data
2	If we get data obtained with arraytotext format argument=0, there is no way to determine the initial arrays dimensions, so this scenario
3	can be covered combining other functions AREMOVE ATEXTSPLIT ASTACK, like in previous post.
4	For that reason ATTSPLIT function will cover only data in the format obtained by arraytotext format argument=1( has row separators";").
5	This ensures that the result will be consistent with the initial arrays.
6
7	arraytotext data, format arg.=1, coming from 3 arrays								array 1					array 2				array 3
8	=ARRAYTOTEXT(I8:L10,1)					Initial arrays				b	2	y		AB		CD			abc		123
9	{,"b",2,"y";"g",,,"h";4,3,,}					different dimensions			g			h			23			def		456		ghi
10	{"AB",,"CD";,23,;,"EF",;"GH",45,}								4	3					EF
11	{,"abc",,123,;"def",,456,,"ghi"}													GH	45
12																			all steps in a function
13	step 1: remove {}" chars		step 2: ATEXTSPLIT for ";" (row delim.)					step 3: AFLAT		step 4: ATEXTSPLIT for "," (clms delim.)									ATTSPLIT function
14	=AREMOVE(A9:A11," {}""""",)		=ATEXTSPLIT(A15#,";",1)					=AFLAT(C15#)		=ATEXTSPLIT(H15#,",",1)									=ATTSPLIT(A9:A11)
15	,b,2,y;g,,,h;4,3,,		,b,2,y	g,,,h	4,3,,			,b,2,y			b	2	y							b	2	y
16	AB,,CD;,23,;,EF,;GH,45,		AB,,CD	,23,	,EF,	GH,45,		g,,,h		g			h						g			h
17	,abc,,123,;def,,456,,ghi		,abc,,123,	def,,456,,ghi				4,3,,		4	3								4	3
18								AB,,CD		AB		CD							AB		CD
19								,23,			23									23
20	othe functions on minisheet							,EF,			EF									EF
21	AREMOVE							GH,45,		GH	45								GH	45
22	ATEXTSPLIT							,abc,,123,			abc		123							abc		123
23	AFLAT							def,,456,,ghi		def		456		ghi					def		456		ghi
24
25	Alternative formula to reproduce ARRAYTOTEXT functionality (with row separators)
26	=LAMBDA(a,LET(c,COLUMNS(a),s,SEQUENCE(,c),x,SWITCH(s,c,a&";",a&","),y,TEXTJOIN(,,x),LEFT(y,LEN(y)-1)))(I8:L10)
27	↓
28	↓		=ATTSPLIT(A29)
29	,b,2,y;g,,,h;4,3,,			b	2	y
30			g			h
31	Error debug.		4	3
32	If not proper data set
33	(no rows separators ";")
34	,b,2,y,g,,,h,4,3,,
35
36	=ATTSPLIT(A34)
37	not proper data format
38
ATTSPLIT post 2

Cell Formulas
Range	Formula
A8,A36,C28,A14,S14,H14,J14,C14	A8	=FORMULATEXT(A9)
A9	A9	=ARRAYTOTEXT(I8:L10,1)
A10	A10	=ARRAYTOTEXT(N8:P11,1)
A11	A11	=ARRAYTOTEXT(R8:V9,1)
A15:A17	A15	=AREMOVE(A9:A11," {}""""",)
C15:F17	C15	=ATEXTSPLIT(A15#,";",1)
H15:H23	H15	=AFLAT(C15#)
J15:N23	J15	=ATEXTSPLIT(H15#,",",1)
S15:W23	S15	=ATTSPLIT(A9:A11)
A26	A26	=FORMULATEXT(A29)
A29	A29	=LAMBDA(a,LET(c,COLUMNS(a),s,SEQUENCE(,c),x,SWITCH(s,c,a&";",a&","),y,TEXTJOIN(,,x),LEFT(y,LEN(y)-1)))(I8:L10)
C29:F31	C29	=ATTSPLIT(A29)
A37	A37	=ATTSPLIT(A34)
Dynamic array formulas.