ATEXTSPLIT

=ATEXTSPLIT(ar,dl,ea)

ar
array strings, 1D vertical
dl
string, one or more characters
ea
0 or 1, 0 or omitted ignores empty ; 1 does not ignore empty

array text split by any delimiter,(one or more chars), fine-tuned for complex scenarios

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
ATEXTSPLIT array text split by any delimiter,(one or more chars), fine-tuned for complex scenarios.
Wrote this function inspired by MrExcel video: Excel TEXTJOIN And The Need For TEXTSPLIT with Ann K. Emery - 2407
The idea was to create a function that is clean and robust, does not call other functions, non-recursive, no FILTERXML, and uses same arguments as TEXTJOIN. What TEXTJOIN joins, ATEXTSPLIT should be able to split it back like it was, with same arguments. When we join, we choose the delimiter, but when we split imported data, things can go quite tricky.
ar: array 1D vertical, dl: delimiter, any string , ea: empty argument , 0 or omitted - ignores empty ; 1 - does not ignore empty
Note: There are 2 special characters in the formula, (h,"º",d,"ª"), first variables after "LET" . These should be changed if any of them is found on the array of strings. Formula advises if this happens.
Excel Formula:
=LAMBDA(ar,dl,ea,
    LET(h,"º",d,"ª",ch,ISNUMBER(SEARCH(CHOOSE({1,2},h,d),ar)),
       a,SUBSTITUTE(ar,dl,d),b,IF(ea,a,SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(a," ",h),d," "))," ",d),h," ")),
       n,LEN(b)-LEN(SUBSTITUTE(b,d,""))+1,c,MAX(n),sc,SEQUENCE(,c),
       x,SEARCH(h,SUBSTITUTE(d&b,d,h,sc)),y,SEARCH(h,SUBSTITUTE(b&d,d,h,sc)),m,IFERROR(MID(b,x,y-x),""),
       IF(OR(ch),"change special chars",IFERROR(--m,m))
    )
)
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI
1TEXTJOINATEXTSPLIT
2empty ignoredempty not ignored2 charsempty ignored
3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=0
4sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check
5a4ca,4,,ca4ca4ca><6.8><ta6.8t131
6d0.5d,,,0.5d0.5d0.5><2.4 w><2.4 w600
7g hi 2k l3 ng h,i 2,k l,3 ng hi 2k l3 ng hi 2k l3 n<Ac4><gh>< ><<Ac4gh 421
8space
9empty ignoredea arg.=0ea arg.=1empty not ignored
10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=1
11a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check
12d,0.5d0.5d0.5a6.8t1310
13g h,i 2,k l,3 ng hi 2k l3 ng hi 2k l3 n2.4 w0600
14<Ac4gh 4210
15Complex scenariospace followed by an empty string
16empty ignoredempty not ignored
17ea arg.=0ea arg.=1error debug.
18sample=ATEXTSPLIT(F19:F22,",",)=ATEXTSPLIT(F19:F22,",",1)=ATEXTSPLIT(X19,",",)
19a,b,c,2.3,,d e,abc2.3d eabc2.3d ea,ºb,cchange special chars
20,,g,,h, ,1.8gh 1.8gh 1.8=ATEXTSPLIT(X21,",",1)
21, k m, ,4.9, ,p k m 4.9 p k m 4.9 pa,ªb,cchange special chars
22, ,,x 4, ,, x 4 x 4
23
24Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument
25 -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument
26 We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)
27
28=LEN(H19#)=LEN(N19#)
29111331113030
30111300010113
31413110413110
32151000105100
33
34- if values themselves embed inner spaces, could be important to keep them (like special codes with fixed length that follow a pattern),TRIM tweaks can not be used
35
36ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)
37fixed with 4 pos.a bc/a b/ bcda bca b bcd444
38/____ /ab d/ d/a ab d da 444
39/xxxx/check:pattern is kept
40
ATEXTSPLIT post
Cell Formulas
RangeFormula
H4,N36,H36,N28,H28,Z18,Z20,N18,H18,F10,X11,N10,H10,F4,X4,N4H4=FORMULATEXT(H5)
H5:K7,H11:K13H5=ATEXTSPLIT(F5:F7,",",)
N5:Q7,N11:Q13N5=ATEXTSPLIT(F5:F7,",",1)
X5:Z7X5=ATEXTSPLIT(T5:T7,"><",)
AC5:AE7,AC12:AF14AC5=LEN(X5#)
F5:F7F5=TEXTJOIN(",",0,A5:D5)
X12:AA14X12=ATEXTSPLIT(T5:T7,"><",1)
F11:F13F11=TEXTJOIN(",",,A5:D5)
H19:L22H19=ATEXTSPLIT(F19:F22,",",)
N19:T22N19=ATEXTSPLIT(F19:F22,",",1)
Z19Z19=ATEXTSPLIT(X19,",",)
Z21Z21=ATEXTSPLIT(X21,",",1)
H29:L32,N29:T32H29=LEN(H19#)
H37:J38H37=ATEXTSPLIT(F37:F38,"/",)
N37:P38N37=LEN(H37#)
Dynamic array formulas.
 
Upvote 0
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNO
1Vertical patterns, we can flatten the horizontal ones and filter the blanks or we use same formulas simply by deleating a ","
2
3=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(4)))
4↓↓↓=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(4)^0+x-1))
5↓↓↓↓↓↓=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(x)))
6↓↓↓↓↓↓↓↓↓=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(x)^0-1+x))
7↓↓↓↓↓↓↓↓↓↓↓↓=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,CHAR(SEQUENCE(x)+64)))
81111A
92112A
103122B
114113A
121223B
132233C
143214A
154224B
161334C
172344D
183315A
194325B
201435C
212445D
223455E
2344
2415
2525
2635
2745
28
ATS 5
Cell Formulas
RangeFormula
B3B3=FORMULATEXT(B8)
D4D4=FORMULATEXT(D8)
F5F5=FORMULATEXT(F8)
H6H6=FORMULATEXT(H8)
J7J7=FORMULATEXT(J8)
B8:B27B8=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(4)))
D8:D27D8=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(4)^0+x-1))
F8:F22F8=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(x)))
H8:H22H8=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(x)^0-1+x))
J8:J22J8=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,CHAR(SEQUENCE(x)+64)))
Dynamic array formulas.
 
Challenge. Quite a common task in real life. Create a random array with no dups by row.
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1Task. Single cell formula to create an array 10 x 15 of random numbers between 1 and 100 with no duplicates by row
2
3Using only RANDARRAY can produce duplicates by rowchecking dups by row, apart from CF
4=RANDARRAY(10,15,1,100,1)=BYROW(B5#,LAMBDA(x,COUNT(UNIQUE(x,1))<15))
579231641664173341873083217942TRUE
6952817444674152446463737837TRUE
7386650512688922726719353585059TRUE
822157441355474718348665559FALSE
97994931923243518843996933822TRUE
106963755897217060769856947642TRUE
1115737478862275445942529529727TRUE
12208040545854685355144699345214TRUE
131834100571883032977143333331TRUE
14863476214282206766186366804254TRUE
15
16single cell formula
17=ATEXTSPILL(SEQUENCE(10),LAMBDA(x,TAKE(SORTBY(SEQUENCE(,100),RANDARRAY(,100)),,15)))=BYROW(B18#,LAMBDA(x,COUNT(UNIQUE(x,1))<15))
18755129668335193756801543522373FALSE
1962279646856347810317698921130FALSE
2033436626774462693474783558839FALSE
21666720971716782752155791471842FALSE
22244991923143463659739758878FALSE
23563862573586483599872994187FALSE
249482568193917342950469529833FALSE
25672782372160203717445313510FALSE
2625743241423982834054508877286FALSE
2733448218142403417963966463562FALSE
28
ATS 6
Cell Formulas
RangeFormula
B4,R17,B17,R4B4=FORMULATEXT(B5)
B5:P14B5=RANDARRAY(10,15,1,100,1)
R5:R14,R18:R27R5=BYROW(B5#,LAMBDA(x,COUNT(UNIQUE(x,1))<15))
B18:P27B18=ATEXTSPILL(SEQUENCE(10),LAMBDA(x,TAKE(SORTBY(SEQUENCE(,100),RANDARRAY(,100)),,15)))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
R18Expression=R18textNO
B18:P27Expression=COUNTIFS($B18:$P18,B18)>1textNO
R5:R14Expression=R5textNO
B5:P14Expression=COUNTIFS($B5:$P5,B5)>1textNO
 
Needless to say, all ATEXTSPILL formulas can be replaced with the "master" version SPILLBYROWS that deals with any arrays and any functions, not only column vectors.
Full study of SPILLBYROWS and much more, still has to be done whenever I will have enough spare time for it.
ATEXTSPILL could be slightly faster, and for text operations does the job.
Anyhow, there is a way to tweak ATEXTSPILL to do what SPILLBYROWS does. See the example below.
ATEXTSPILL.xlsx
ABCDEFGHIJK
1
2=SPILLBYROWS(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},"-",1)))
3Do. Or do not. There is no try. -AnonymousDoOrdonotThereisnotry
4There are two options: Adapt or die. Anonymous
5One cannot step twice in the same river. -HeraclitusTherearetwooptionsAdaptordie
6Whatever you are, be a good one. -Abraham LincolnOnecannotsteptwiceinthesameriver
7Heraclitus
8Whateveryouarebeagoodone
9AbrahamLincoln
10
11A tweak to trick ATEXTSPILL to handle 2D arrays is to "feed" entire rows into lambda helper argument.
12Task. Extracting unique by rows in ascending order
13
14=RANDARRAY(5,8,1,20,1)
1514121220115148
16518417717158
171118128143162
18159417166714
191613612176
20
21=SPILLBYROWS(C15#,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))
225811121420
234578151718
242381112141618
25467914151617
261361217
27
28=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,LET(y,INDEX(C15#,x,),SORT(UNIQUE(y,1),,,1))))
295811121420
304578151718
312381112141618
32467914151617
331361217
34
Sheet12
Cell Formulas
RangeFormula
C2,C28,C21,C14C2=FORMULATEXT(C3)
C3:J9C3=SPILLBYROWS(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},"-",1)))
C15:J19C15=RANDARRAY(5,8,1,20,1)
C22:J26C22=SPILLBYROWS(C15#,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))
C29:J33C29=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,LET(y,INDEX(C15#,x,),SORT(UNIQUE(y,1),,,1))))
Dynamic array formulas.
 
We have seen how to create random rows without repetitions, this will be about how to randomly generate a sequence of arrays without repetitions.
Task. Out of 12 people, we have to assign random teams of 4 members each for a period of 15 days.
For every 3 consecutive days, each member should have only 1 assignment, and every other 3 days the team distributions have to be changed randomly.
On other words, we have to create 5 random arrays of 3x4 members with no repetitions.
Random names list from a random list generator site: Male Name Generator — A random list of boys' names .

ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOPQRS
1Copying data from site => single cell list with line feed separator CHAR(10)
2
3Richard Cruz Calvin Sheldon Yadiel Wade Milo Mark Quinn Bobby Justus Karter
4
5Spliting as column vectorIf we not "inject" the random functionrandom function inside function argument
6=TEXTSPLIT(B3,,CHAR(10))Shuffling the arrayinside function argument => same array sequencerecalculates with every iteration
7↓↓↓=RANDARRAY(12)=SORTBY(B8#,C8#)=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,WRAPCOLS(E8#,3)))=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,WRAPCOLS(SORTBY(B8#,RANDARRAY(12)),3)))
8Richard0.559265CalvinCalvinYadielRichardCruzCruzYadielSheldonBobby
9Cruz0.930086MiloMiloBobbyJustusSheldonMarkJustusQuinnRichard
10Calvin0.050665QuinnQuinnWadeKarterMarkWadeCalvinKarterMilo
11Sheldon0.931163YadielCalvinYadielRichardCruzYadielMiloBobbyKarter
12Yadiel0.283132BobbyMiloBobbyJustusSheldonQuinnWadeSheldonJustus
13Wade0.505379WadeQuinnWadeKarterMarkCruzRichardMarkCalvin
14Milo0.10434RichardCalvinYadielRichardCruzBobbyKarterWadeMilo
15Mark0.945032JustusMiloBobbyJustusSheldonRichardSheldonJustusYadiel
16Quinn0.151372KarterQuinnWadeKarterMarkMarkQuinnCalvinCruz
17Bobby0.43663CruzCalvinYadielRichardCruzKarterSheldonBobbyJustus
18Justus0.649646SheldonMiloBobbyJustusSheldonWadeMarkCalvinQuinn
19Karter0.665065MarkQuinnWadeKarterMarkYadielMiloRichardCruz
20CalvinYadielRichardCruzQuinnJustusYadielMark
21MiloBobbyJustusSheldonBobbyRichardWadeCruz
22QuinnWadeKarterMarkKarterMiloSheldonCalvin
23
ATS 8
Cell Formulas
RangeFormula
B6B6=FORMULATEXT(B8)
C7,E7,G7,L7C7=FORMULATEXT(C8)
B8:B19B8=TEXTSPLIT(B3,,CHAR(10))
C8:C19C8=RANDARRAY(12)
E8:E19E8=SORTBY(B8#,C8#)
G8:J22G8=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,WRAPCOLS(E8#,3)))
L8:O22L8=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,WRAPCOLS(SORTBY(B8#,RANDARRAY(12)),3)))
Dynamic array formulas.
 
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOPQRST
1Reversed "engineering"
2Extract unique values every other 3x4 array, ascending ordernr arrays
3=ROWS(B4:E36)/3
4SheldonBobbyCalvinQuinnembedded lambda ar(i) useful for iteration11
5KarterCruzJustusBobbyar(2)
6CruzMiloYadielRichard=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(2)
7JustusRichardYadielSheldonJustusRichardYadielSheldon
8CalvinKarterRichardCruzCalvinKarterRichardCruz
9CruzBobbyJustusBobbyCruzBobbyJustusBobby
10JustusMiloKarterWade
11BobbyQuinnCalvinCruzar(4)
12SheldonYadielMarkRichard=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(4)
13YadielCalvinCruzRichardYadielCalvinCruzRichard
14JustusSheldonJustusKarterJustusSheldonJustusKarter
15MarkCalvinYadielMarkMarkCalvinYadielMark
16RichardJustusQuinnCruz
17KarterMiloCalvinMarksolution with embedded lambda ar(i)
18WadeYadielSheldonBobby=LET(ra,B4:E36,ar,LAMBDA(i,INDEX(ra,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4))),ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(ar(x)),1),,,1))))
19CalvinBobbySheldonJustusBobbyCalvinCruzJustusKarterMiloQuinnRichardSheldonYadiel
20SheldonCalvinMiloMarkBobbyCalvinCruzJustusKarterRichardSheldonYadiel
21WadeCruzWadeQuinnBobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel
22MarkSheldonJustusJustusCalvinCruzJustusKarterMarkRichardSheldonYadiel
23QuinnMarkMiloMiloBobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel
24QuinnSheldonCalvinBobbyBobbyCalvinCruzJustusMarkMiloQuinnSheldonWade
25MarkKarterYadielSheldonBobbyCalvinJustusMarkMiloQuinnSheldon
26CruzYadielKarterYadielBobbyCalvinCruzJustusKarterMarkSheldonWadeYadiel
27BobbyJustusWadeCalvinCalvinCruzJustusKarterMarkMiloQuinnWadeYadiel
28JustusQuinnMarkCalvinBobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel
29CruzMarkQuinnYadielCalvinKarterMarkMiloQuinnRichardSheldonWadeYadiel
30MarkKarterMiloWadeor
31WadeMarkCruzQuinnlambda formula
32KarterBobbySheldonRichard=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(INDEX(B4:E36,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1)))
33JustusMiloCalvinYadielBobbyCalvinCruzJustusKarterMiloQuinnRichardSheldonYadiel
34KarterWadeCalvinSheldonBobbyCalvinCruzJustusKarterRichardSheldonYadiel
35SheldonKarterQuinnMiloBobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel
36RichardMarkYadielYadielCalvinCruzJustusKarterMarkRichardSheldonYadiel
37BobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel
38BobbyCalvinCruzJustusMarkMiloQuinnSheldonWade
39BobbyCalvinJustusMarkMiloQuinnSheldon
40BobbyCalvinCruzJustusKarterMarkSheldonWadeYadiel
41CalvinCruzJustusKarterMarkMiloQuinnWadeYadiel
42BobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel
43CalvinKarterMarkMiloQuinnRichardSheldonWadeYadiel
44
ATS 9
Cell Formulas
RangeFormula
M3,G32,G18,G12,G6M3=FORMULATEXT(M4)
M4M4=ROWS(B4:E36)/3
G7:J9G7=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(2)
G13:J15G13=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(4)
G19:R29G19=LET(ra,B4:E36,ar,LAMBDA(i,INDEX(ra,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4))),ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(ar(x)),1),,,1))))
G33:R43G33=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(INDEX(B4:E36,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1)))
Dynamic array formulas.
 
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOPQR
1Same results keeping initial format of source array plus adding index array number
2lambda formula
3G5:=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,HSTACK("ar "&x,WRAPROWS(SORT(UNIQUE(TOROW(INDEX(B5:E37,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),4))))
4
5SheldonBobbyCalvinQuinnar 1BobbyCalvinCruzJustus
6KarterCruzJustusBobbyKarterMiloQuinnRichard
7CruzMiloYadielRichardSheldonYadiel
8JustusRichardYadielSheldonar 2BobbyCalvinCruzJustus
9CalvinKarterRichardCruzKarterRichardSheldonYadiel
10CruzBobbyJustusBobbyar 3BobbyCalvinCruzJustus
11JustusMiloKarterWadeKarterMarkMiloQuinn
12BobbyQuinnCalvinCruzRichardSheldonWadeYadiel
13SheldonYadielMarkRichardar 4CalvinCruzJustusKarter
14YadielCalvinCruzRichardMarkRichardSheldonYadiel
15JustusSheldonJustusKarterar 5BobbyCalvinCruzJustus
16MarkCalvinYadielMarkKarterMarkMiloQuinn
17RichardJustusQuinnCruzRichardSheldonWadeYadiel
18KarterMiloCalvinMarkar 6BobbyCalvinCruzJustus
19WadeYadielSheldonBobbyMarkMiloQuinnSheldon
20CalvinBobbySheldonJustusWade
21SheldonCalvinMiloMarkar 7BobbyCalvinJustusMark
22WadeCruzWadeQuinnMiloQuinnSheldon
23MarkSheldonJustusJustusar 8BobbyCalvinCruzJustus
24QuinnMarkMiloMiloKarterMarkSheldonWade
25QuinnSheldonCalvinBobbyYadiel
26MarkKarterYadielSheldonar 9CalvinCruzJustusKarter
27CruzYadielKarterYadielMarkMiloQuinnWade
28BobbyJustusWadeCalvinYadiel
29JustusQuinnMarkCalvinar 10BobbyCalvinCruzJustus
30CruzMarkQuinnYadielKarterMarkMiloQuinn
31MarkKarterMiloWadeRichardSheldonWadeYadiel
32WadeMarkCruzQuinnar 11CalvinKarterMarkMilo
33KarterBobbySheldonRichardQuinnRichardSheldonWade
34JustusMiloCalvinYadielYadiel
35KarterWadeCalvinSheldon
36SheldonKarterQuinnMilo
37RichardMarkYadielYadiel
38
ATS 10
Cell Formulas
RangeFormula
B3B3=FORMULATEXT(G5)
G5:K34G5=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,HSTACK("ar "&x,WRAPROWS(SORT(UNIQUE(TOROW(INDEX(B5:E37,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),4))))
Dynamic array formulas.
 
I know that the last examples were kind of off topic to the text functions but, was fun to show what level of complexity can be accomplished with today's Excel functions environment, and versatility of custom-made lambda helper functions, as product of this fine, and more powerful than never, environment.
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOPQRS
1I thought that also adding info about nr. of unique members/each array could be useful
2
3G5:=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(u,SORT(UNIQUE(TOROW(INDEX(B5:E37,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),HSTACK("ar "&x,WRAPROWS(u,4),"cnt "&COLUMNS(u)))))
4
5SheldonBobbyCalvinQuinnar 1BobbyCalvinCruzJustuscnt 10
6KarterCruzJustusBobbyKarterMiloQuinnRichard
7CruzMiloYadielRichardSheldonYadiel
8JustusRichardYadielSheldonar 2BobbyCalvinCruzJustuscnt 8
9CalvinKarterRichardCruzKarterRichardSheldonYadiel
10CruzBobbyJustusBobbyar 3BobbyCalvinCruzJustuscnt 12
11JustusMiloKarterWadeKarterMarkMiloQuinn
12BobbyQuinnCalvinCruzRichardSheldonWadeYadiel
13SheldonYadielMarkRichardar 4CalvinCruzJustusKartercnt 8
14YadielCalvinCruzRichardMarkRichardSheldonYadiel
15JustusSheldonJustusKarterar 5BobbyCalvinCruzJustuscnt 12
16MarkCalvinYadielMarkKarterMarkMiloQuinn
17RichardJustusQuinnCruzRichardSheldonWadeYadiel
18KarterMiloCalvinMarkar 6BobbyCalvinCruzJustuscnt 9
19WadeYadielSheldonBobbyMarkMiloQuinnSheldon
20CalvinBobbySheldonJustusWade
21SheldonCalvinMiloMarkar 7BobbyCalvinJustusMarkcnt 7
22WadeCruzWadeQuinnMiloQuinnSheldon
23MarkSheldonJustusJustusar 8BobbyCalvinCruzJustuscnt 9
24QuinnMarkMiloMiloKarterMarkSheldonWade
25QuinnSheldonCalvinBobbyYadiel
26MarkKarterYadielSheldonar 9CalvinCruzJustusKartercnt 9
27CruzYadielKarterYadielMarkMiloQuinnWade
28BobbyJustusWadeCalvinYadiel
29JustusQuinnMarkCalvinar 10BobbyCalvinCruzJustuscnt 12
30CruzMarkQuinnYadielKarterMarkMiloQuinn
31MarkKarterMiloWadeRichardSheldonWadeYadiel
32WadeMarkCruzQuinnar 11CalvinKarterMarkMilocnt 9
33KarterBobbySheldonRichardQuinnRichardSheldonWade
34JustusMiloCalvinYadielYadiel
35KarterWadeCalvinSheldon
36SheldonKarterQuinnMilo
37RichardMarkYadielYadiel
38
ATS 11
Cell Formulas
RangeFormula
B3B3=FORMULATEXT(G5)
G5:L34G5=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(u,SORT(UNIQUE(TOROW(INDEX(B5:E37,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),HSTACK("ar "&x,WRAPROWS(u,4),"cnt "&COLUMNS(u)))))
Dynamic array formulas.
 
Even more complex, adding individual unique count for each array extraction.
Nested lambda helper MAP inside custom-made lambda helper ATEXTSPILL
ATEXTSPILL.xlsx
BCDEFGHIJKLMNOPQRSTUVWX
1Adding info about array index nr., nr. of total unique members plus individual each member count for each array extraction
2=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(u,SORT(UNIQUE(TOROW(INDEX(B7:E39,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),HSTACK("ar "&x,WRAPROWS(u,4),"cnt "&COLUMNS(u)))))
3=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(a,INDEX(B7:E39,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4)),u,SORT(UNIQUE(TOROW(a),1),,,1),m,MAP(u,LAMBDA(x,SUM(--(x=a)))),HSTACK("ar "&x,WRAPROWS(u&","&m,4),"cnt "&COLUMNS(u)))))
4
5array #, extract unique, total unique countarray #, extract unique, individual count, total unique count
6
7SheldonBobbyCalvinQuinnar 1BobbyCalvinCruzJustuscnt 10ar 1Bobby,2Calvin,1Cruz,2Justus,1cnt 10
8KarterCruzJustusBobbyKarterMiloQuinnRichardKarter,1Milo,1Quinn,1Richard,1
9CruzMiloYadielRichardSheldonYadielSheldon,1Yadiel,1
10JustusRichardYadielSheldonar 2BobbyCalvinCruzJustuscnt 8ar 2Bobby,2Calvin,1Cruz,2Justus,2cnt 8
11CalvinKarterRichardCruzKarterRichardSheldonYadielKarter,1Richard,2Sheldon,1Yadiel,1
12CruzBobbyJustusBobbyar 3BobbyCalvinCruzJustuscnt 12ar 3Bobby,1Calvin,1Cruz,1Justus,1cnt 12
13JustusMiloKarterWadeKarterMarkMiloQuinnKarter,1Mark,1Milo,1Quinn,1
14BobbyQuinnCalvinCruzRichardSheldonWadeYadielRichard,1Sheldon,1Wade,1Yadiel,1
15SheldonYadielMarkRichardar 4CalvinCruzJustusKartercnt 8ar 4Calvin,2Cruz,1Justus,2Karter,1cnt 8
16YadielCalvinCruzRichardMarkRichardSheldonYadielMark,2Richard,1Sheldon,1Yadiel,2
17JustusSheldonJustusKarterar 5BobbyCalvinCruzJustuscnt 12ar 5Bobby,1Calvin,1Cruz,1Justus,1cnt 12
18MarkCalvinYadielMarkKarterMarkMiloQuinnKarter,1Mark,1Milo,1Quinn,1
19RichardJustusQuinnCruzRichardSheldonWadeYadielRichard,1Sheldon,1Wade,1Yadiel,1
20KarterMiloCalvinMarkar 6BobbyCalvinCruzJustuscnt 9ar 6Bobby,1Calvin,2Cruz,1Justus,1cnt 9
21WadeYadielSheldonBobbyMarkMiloQuinnSheldonMark,1Milo,1Quinn,1Sheldon,2
22CalvinBobbySheldonJustusWadeWade,2
23SheldonCalvinMiloMarkar 7BobbyCalvinJustusMarkcnt 7ar 7Bobby,1Calvin,1Justus,2Mark,2cnt 7
24WadeCruzWadeQuinnMiloQuinnSheldonMilo,2Quinn,2Sheldon,2
25MarkSheldonJustusJustusar 8BobbyCalvinCruzJustuscnt 9ar 8Bobby,1Calvin,1Cruz,1Justus,1cnt 9
26QuinnMarkMiloMiloKarterMarkSheldonWadeKarter,2Mark,1Sheldon,1Wade,1
27QuinnSheldonCalvinBobbyYadielYadiel,3
28MarkKarterYadielSheldonar 9CalvinCruzJustusKartercnt 9ar 9Calvin,1Cruz,1Justus,1Karter,1cnt 9
29CruzYadielKarterYadielMarkMiloQuinnWadeMark,3Milo,1Quinn,2Wade,1
30BobbyJustusWadeCalvinYadielYadiel,1
31JustusQuinnMarkCalvinar 10BobbyCalvinCruzJustuscnt 12ar 10Bobby,1Calvin,1Cruz,1Justus,1cnt 12
32CruzMarkQuinnYadielKarterMarkMiloQuinnKarter,1Mark,1Milo,1Quinn,1
33MarkKarterMiloWadeRichardSheldonWadeYadielRichard,1Sheldon,1Wade,1Yadiel,1
34WadeMarkCruzQuinnar 11CalvinKarterMarkMilocnt 9ar 11Calvin,1Karter,2Mark,1Milo,1cnt 9
35KarterBobbySheldonRichardQuinnRichardSheldonWadeQuinn,1Richard,1Sheldon,2Wade,1
36JustusMiloCalvinYadielYadielYadiel,2
37KarterWadeCalvinSheldon
38SheldonKarterQuinnMilo
39RichardMarkYadielYadiel
40
ATS 11
Cell Formulas
RangeFormula
B2B2=FORMULATEXT(G7)
B3B3=FORMULATEXT(N7)
G7:L36G7=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(u,SORT(UNIQUE(TOROW(INDEX(B7:E39,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),HSTACK("ar "&x,WRAPROWS(u,4),"cnt "&COLUMNS(u)))))
N7:S36N7=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(a,INDEX(B7:E39,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4)),u,SORT(UNIQUE(TOROW(a),1),,,1),m,MAP(u,LAMBDA(x,SUM(--(x=a)))),HSTACK("ar "&x,WRAPROWS(u&","&m,4),"cnt "&COLUMNS(u)))))
Dynamic array formulas.
 
It's Sunday, let's have some fun.
Deal a shuffled deck of cards in less than 1 min.
Note: Covered this before AXMAS thread post #11. At that time, I used custom-made lambdas like AFLAT, ARESIZE, now we have TOROW/TOCOL, WRAPCOLS/WRAPROWS.
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE
1unicodessuits
29824Deal a shuffled deck of cards in less than 1 minCF formula entire spreadsheet:
39827 =OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)
49829=SEQUENCE(,13,2)+{0;0;0;0}
59830234567891011121314
6234567891011121314
7234567891011121314
8234567891011121314
9
10=SWITCH(E5#,11,"A",12,"J",13,"Q",14,"K",E5#)
112345678910AJQK
122345678910AJQK
132345678910AJQK
142345678910AJQK
15
16=E11#&C2#
172♠3♠4♠5♠6♠7♠8♠9♠10♠A♠J♠Q♠K♠
182♣3♣4♣5♣6♣7♣8♣9♣10♣A♣J♣Q♣K♣
192♥3♥4♥5♥6♥7♥8♥9♥10♥A♥J♥Q♥K♥
202♦3♦4♦5♦6♦7♦8♦9♦10♦A♦J♦Q♦K♦
21
22deck in order
23=TOROW(E17#)
242♠3♠4♠5♠6♠7♠8♠9♠10♠A♠J♠Q♠K♠2♣3♣4♣5♣6♣7♣8♣9♣10♣A♣J♣Q♣K♣2♥3♥4♥5♥6♥7♥8♥9♥10♥A♥J♥Q♥K♥2♦3♦4♦5♦6♦7♦8♦9♦10♦A♦J♦Q♦K♦
25
26shuffling (no duplicates)
27=SORTBY(E24#,RANDARRAY(,52))
285♣A♥7♠Q♦6♣K♣6♦5♦8♦4♣4♥10♥9♦5♥J♣A♦6♥2♦3♠8♣J♥3♥7♣A♠K♠3♦8♥6♠8♠K♦J♠9♠A♣Q♠2♠J♦10♦Q♣3♣9♥4♦9♣5♠Q♥K♥10♣2♥10♠4♠7♥2♣7♦
29
30deal the whole deck 5 cards/round(hand)7 cards13 cardschecking no dups
31=IFNA(WRAPROWS(E28#,5),"")=IFNA(WRAPROWS(E28#,7),"")=IFNA(WRAPROWS(E28#,13),"")=COUNTIF(X32#,X32#)
325♣A♥7♠Q♦6♣5♣A♥7♠Q♦6♣K♣6♦5♣A♥7♠Q♦6♣K♣6♦5♦8♦4♣4♥10♥9♦1111111111111
33K♣6♦5♦8♦4♣5♦8♦4♣4♥10♥9♦5♥5♥J♣A♦6♥2♦3♠8♣J♥3♥7♣A♠K♠3♦1111111111111
344♥10♥9♦5♥J♣J♣A♦6♥2♦3♠8♣J♥8♥6♠8♠K♦J♠9♠A♣Q♠2♠J♦10♦Q♣3♣1111111111111
35A♦6♥2♦3♠8♣3♥7♣A♠K♠3♦8♥6♠9♥4♦9♣5♠Q♥K♥10♣2♥10♠4♠7♥2♣7♦1111111111111
36J♥3♥7♣A♠K♠8♠K♦J♠9♠A♣Q♠2♠
373♦8♥6♠8♠K♦J♦10♦Q♣3♣9♥4♦9♣
38J♠9♠A♣Q♠2♠5♠Q♥K♥10♣2♥10♠4♠
39J♦10♦Q♣3♣9♥7♥2♣7♦
404♦9♣5♠Q♥K♥
4110♣2♥10♠4♠7♥
422♣7♦
43
DECK 1
Cell Formulas
RangeFormula
C2:C5C2=UNICHAR(B2:B5)
E4,AL31,X31,O31,E31,E27,E23,E16,E10E4=FORMULATEXT(E5)
E5:Q8E5=SEQUENCE(,13,2)+{0;0;0;0}
E11:Q14E11=SWITCH(E5#,11,"A",12,"J",13,"Q",14,"K",E5#)
E17:Q20E17=E11#&C2#
E24:BD24E24=TOROW(E17#)
E28:BD28E28=SORTBY(E24#,RANDARRAY(,52))
E32:I42E32=IFNA(WRAPROWS(E28#,5),"")
O32:U39O32=IFNA(WRAPROWS(E28#,7),"")
X32:AJ35X32=IFNA(WRAPROWS(E28#,13),"")
AL32:AX35AL32=COUNTIF(X32#,X32#)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
1:1048576Expression=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)textNO
 
Shuffling n decks of cards with ATEXTSPILL
DECK()
argument free lambda, returns a deck of cards in order (row orientation)
Excel Formula:
=LAMBDA(
    LET(
        s, SEQUENCE(, 13, 2) + {0; 0; 0; 0},
        TOROW(SWITCH(s, 11, "A", 12, "J", 13, "Q", 14, "K", s) & {"♠"; "♣"; "♥"; "♦"})
    )
)
NDECK([n]) shuffles n decks of cards, using ATEXTSPILL as shuffling "engine", n rows, each row a shuffled deck. Also calls DECK()
[n]: nr. of decks, integer
- if n omitted or 0 => single deck in order
- if n<0 => n=1 => single deck shuffled
- if n>0 => n => n decks shuffled, each row a shuffled deck, (no dups/row)
Excel Formula:
=LAMBDA([n],
    IF(
        n,
        ATEXTSPILL(SEQUENCE(MAX(1, n)), LAMBDA(x, SORTBY(DECK(), RANDARRAY(, 52)))),
        DECK()
    )
)
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABB
1
2=DECK()
32♠3♠4♠5♠6♠7♠8♠9♠10♠A♠J♠Q♠K♠2♣3♣4♣5♣6♣7♣8♣9♣10♣A♣J♣Q♣K♣2♥3♥4♥5♥6♥7♥8♥9♥10♥A♥J♥Q♥K♥2♦3♦4♦5♦6♦7♦8♦9♦10♦A♦J♦Q♦K♦
4
5n,omitted => single deck in order (row)
6=NDECK()
72♠3♠4♠5♠6♠7♠8♠9♠10♠A♠J♠Q♠K♠2♣3♣4♣5♣6♣7♣8♣9♣10♣A♣J♣Q♣K♣2♥3♥4♥5♥6♥7♥8♥9♥10♥A♥J♥Q♥K♥2♦3♦4♦5♦6♦7♦8♦9♦10♦A♦J♦Q♦K♦
8
9n <0 => n=1
10=NDECK(-3)
11Q♠K♠8♠9♠6♠A♠10♠3♠10♦5♠6♥6♣4♠J♠8♦4♥2♣10♣9♥5♥A♣4♣J♥Q♥10♥3♥K♣4♦A♦5♣8♣2♠7♣2♦A♥8♥J♦7♦6♦9♣9♦Q♣K♥3♣7♠2♥5♦J♣7♥3♦K♦Q♦
12
13n,1, => 1 deck shuffled
14=NDECK(1)
158♥8♦5♠9♥4♣5♣2♠3♥5♦5♥9♠K♥3♠J♠6♦Q♥6♥7♥8♠J♦A♦J♣Q♣9♦8♣A♠4♥7♦A♣10♥3♣4♠2♣J♥6♣6♠K♠9♣Q♠7♣10♣Q♦2♥2♦4♦K♦3♦A♥10♦10♠K♣7♠
16
17n,>0 => n decks shuffled, each row represent a separate shuffled deck => no dups /row
18=NDECK(3)
199♠8♦2♦2♠6♦3♥8♥K♣5♥K♦10♠7♦3♠4♠9♦Q♣3♣J♣4♦6♣4♥6♠Q♦2♣K♥2♥A♦5♠J♠4♣Q♥10♣9♥5♣K♠6♥9♣A♥A♣5♦A♠8♣8♠Q♠J♥7♥3♦10♥J♦7♣10♦7♠
2010♥6♥2♦Q♥A♥8♣6♦2♠3♣J♥7♠10♣3♦2♣8♦Q♣A♣J♠4♣7♦K♣5♦Q♠A♦9♥5♠3♥6♣9♦5♥10♦K♥3♠7♣4♦K♠J♣8♥4♠9♠J♦K♦8♠A♠6♠7♥9♣4♥5♣2♥Q♦10♠
216♠Q♠3♠Q♥9♠2♣J♠5♦4♠K♦10♥2♥10♦K♠4♥7♠J♦5♥6♣8♦6♥A♣4♣7♣Q♣8♣9♦J♥A♦3♦9♥K♥10♣Q♦J♣A♥K♣7♥2♠6♦7♦5♠3♣10♠4♦3♥8♥8♠A♠9♣5♣2♦
22
23checking dups/row. If all 1 => no dups/row
24B21:B23=COUNTIF(B19:BA19,DECK())
251111111111111111111111111111111111111111111111111111
261111111111111111111111111111111111111111111111111111
271111111111111111111111111111111111111111111111111111
28
DECK 2
Cell Formulas
RangeFormula
B2,B18,B14,B10,B6B2=FORMULATEXT(B3)
B3:BA3B3=DECK()
B7:BA7B7=NDECK()
B11:BA11B11=NDECK(-3)
B15:BA15B15=NDECK(1)
B19:BA21B19=NDECK(3)
D24D24=FORMULATEXT(B25)
B25:BA27B25=COUNTIF(B19:BA19,DECK())
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
1:1048576Expression=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)textNO
 

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top