ATEXTSPLIT

[schardt679]

ATEXTSPLIT splits delimited values from a one dimensional array, or returns the value count, delimiter positions, value positions, or value lengths.

Thanks to XLambda's ATTSPLIT & ATEXTSPLIT functions, GeertD's GETONDELIMITER & SPLITONDELIMITER functions, and ExcelIsFun's/Mr. Excels' Dueling Excel #192 for the inspiration.
Similar to other Excel text functions, values are returned as text.
Invalid arguments return error messages. Blanks/ errors are returned for Split.
If the delimiter isn't found, Value Count= 1, Delimiter Positions= 0, Value Positions= 1, and Lengths= string length.
Optional arguments require comma to work. Calls AONEDIM, ATRIM, & CELLCOUNT.
Other functions on minisheet: AFORMULATEXT.

Screen Tip/ Comment: =ATEXTSPLIT(Array☛ range, [Delimiter]☛ separator char; =" ", [Info_Type]☛ 0/=Split ; 1=Count; 2=Delimiter?; 3=?; 4=Length, [nRows]☛ row index; =0, [nCols]☛ column index; =0) ⁂[]=optional: use ","; =default; =omit; ?=position; =value

=LAMBDA(Array,Delimiter,Info_Type,nRows,nCols,
      LET(Arr, Array&"",          Info, N(IFERROR(Info_Type,)),          nRow, N(IFERROR(nRows,)),          nCol, N(IFERROR(nCols,)),
             A1D?, AONEDIM(Arr),          AScan, AND(A1D?<>{"H","V"}),          H?, A1D?="H",
             InfoScan, OR(Info<0, Info>4),          InfoMSG, "Info=0-4",
             NumScan, OR(MIN(nRow)<0, MIN(nCol)<0),          NumMSG, "nRows/nCols>=0",
             ERRORS, IFS(AScan, A1D?,  InfoScan, InfoMSG,  NumScan, NumMSG,  1, 0),
             Del, Delimiter,          D, CHAR(2),          D_, CHAR(3),          AD, ATRIM(Arr, Del),          DSub, SUBSTITUTE(AD, IF(Del="", " ", Del), D),
             A, IFERROR(DSub,),          ALen, LEN(A),          NoDLen, LEN(SUBSTITUTE(A, D, "")),          VCt, ALen - NoDLen + (A<>""),
             MaxCt, MAX(IF(VCt=0, 1, VCt)),          CellCt, CELLCOUNT(A,),          Seq, SEQUENCE(IF(H?, MaxCt, 1), IF(H?, 1, MaxCt)),
             RowSel, IF(H?, MaxCt, CellCt),          RowF, FILTER(nRow, nRow<=RowSel, 1),          AllRow?, IFNA(XMATCH(0, RowF),),
             ColSel, IF(H?, CellCt, MaxCt),          ColF, FILTER(nCol, nCol<=ColSel, 1),          AllCol?, IFNA(XMATCH(0, ColF),),
             RowSeq, IF(AllRow?, SEQUENCE(RowSel),  RowF),          ColSeq, IF(AllCol?, SEQUENCE(, ColSel),  ColF),
             DFind, FIND(D_, SUBSTITUTE(A&D, D, D_, Seq)),          VFind, FIND(D_, SUBSTITUTE(D&A, D, D_, Seq)),
             DPos, IF(((Info=2) * (Seq=VCt)) + (VCt<1),, DFind),          VPos, IF(VCt<1,, VFind),          VLen, ABS(DPos - VPos),
             Split, IF(Seq>VCt, "", MID(DSub, VPos, VLen)),          VC, INDEX(VCt, IF(H?, ColSeq, RowSeq)),
             PreRes, IFERROR(SWITCH(Info,  2, DPos,  3, VPos, 4, VLen,  Split), IF(Info=0, Split,)),
             Result, IF(Info=1, VC,  INDEX(PreRes, RowSeq, ColSeq)),          Return, IF(ERRORS<>0, ERRORS, Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		ATEXTSPLIT
2
3		Sample Data		Delimiter:			Result
4		My name is Patrick.		Info_Type:			My	name	is	Patrick.
5		I went to CalU of PA.		nRows:			I	went	to	CalU
6		I have 3 degrees.		nCols:	1		I	have	3	degrees.
7		I am 27.			2		I	am	27.
8					3
9		#N/A			4		#N/A
10
11		Data is a vertical array --> result spills horizontally
12		Delimiter is " "☛ Char 32
13		Values are split by delimiter
14		All rows are returned
15		Resultant columns 1-4 are returned
16		Formula in cell G4☛ =ATEXTSPLIT(B4:B9, E3, E4, E5, TRANSPOSE(E6:E9))
17
18
19
20		Sample Data		Delimiter:	|		Result
21		Group|Project|Start By|End By		Info_Type:	1		4
22		1|A|3/22/21|3/23/21		nRows:	2		1
23		#VALUE!			3		0
24					4
25		2|B2|3/22/21|3/25/21		nCols:
26		3|C|3/23/21|3/25/21
27		4|D|3/23/21|3/23/21
28		5|E1|3/23/21|3/26/21
29		5|E2|3/24/21|3/29/21
30		5|E3|3/25/21|3/30/21
31
32		Data is a vertical array --> result spills horizontally
33		Delimiter is "|"☛ Char 124
34		Delimited values are counted
35		Resultant rows 2-4 are returned
36		All columns are returned
37		Formula in cell G21☛ =ATEXTSPLIT(B21:B30, E20, E21, E22:E24, E25)
38
39
40
41		Sample Data
42		A|BC|DEF	GHIJ|JKLM	1+2+3+4	5+6+7+8+9+10
43
44		Delmiter:	|
45			|
46			+
47			+
48		Info_Type:	2
49		nRows:
50		nCols:
51
52		Result
53		2	5	2	2
54		5	0	4	4
55		0	0	6	6
56		0	0	0	8
57		0	0	0	10
58		0	0	0	0
59
60		Data is a horizontal array --> result spills vertically
61		Delimiter 1 is "|"☛ Char 124; Del 2 is "+"☛ Char 43
62		Delimiter positions are returned
63		All Rows are returned
64		All columns are returned
65		Formula in cell B53☛ =ATEXTSPLIT(B42:E42, TRANSPOSE(C44:C47), C48, C49, C50)
66
67
68
69		Sample Data		Delmiter:	|		Result
70		A|BC|DEF			|		1	3	6	0	0	0
71		GHIJ|JKLM			+		1	6	0	0	0	0
72		1+2+3+4			+		1	3	5	7	0	0
73		5+6+7+8+9+10		Info_Type:	3		1	3	5	7	9	11
74				nRows:
75				nCols:
76
77
78		Data is a vertical array --> result spills horizontally
79		Delimiter 1 is "|"☛ Char 124; Del 2 is "+"☛ Char 43
80		Value positions are returned
81		All Rows are returned
82		All columns are returned
83		Formula in cell G70☛ =ATEXTSPLIT(B70:B73, E69:E72, E73, E74, E75)
84
85
86
87		Sample Data		Delmiter:	|		Result
88		A|BC|DEF			|		1	2	3	0	0	0
89		GHIJ|JKLM			+		4	4	0	0	0	0
90		1+2+3+4			+		1	1	1	1	0	0
91		5+6+7+8+9+10		Info_Type:	4		1	1	1	1	1	2
92				nRows:
93				nCols:
94
95
96		Data is a vertical array --> result spills horizontally
97		Delimiter 1 is "|"☛ Char 124; Del 2 is "+"☛ Char 43
98		Value lengths are returned
99		All Rows are returned
100		All columns are returned
101		Formula in cell G88☛ =ATEXTSPLIT(B88:B91, E87:E90, E91, E92, E93)
102
ATEXTSPLIT

Cell Formulas
Range	Formula
G4:J9	G4	=ATEXTSPLIT(B4:B9, E3, E4, E5, TRANSPOSE(E6:E9))
B16	B16	=AFORMULATEXT(G4)
G21:G23	G21	=ATEXTSPLIT(B21:B30, E20, E21, E22:E24, E25)
B37	B37	=AFORMULATEXT(G21)
B53:E58	B53	=ATEXTSPLIT(B42:E42, TRANSPOSE(C44:C47), C48, C49, C50)
B65	B65	=AFORMULATEXT(B53)
G70:L73,G88:L91	G70	=ATEXTSPLIT(B70:B73, E69:E72, E73, E74, E75)
B83,B101	B83	=AFORMULATEXT(G70)
Dynamic array formulas.