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
2nd solution using latest ATEXTSPLIT (capable of splitting for an array of delimiters, post #28). Single cell formula at M4:
Excel Formula:
=LET(a,expG[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"},
         b,ATEXTSPLIT(a,{":",","}),
         n,INDEX(b,,1),u,UNIQUE(n),
         t,BYROW(b,LAMBDA(x,SUM(x))),
        m,MAP(u,LAMBDA(x,SUM(IF(x=n,t)))),
        q,SUM(m)/4+SEQUENCE(ROWS(u))^0-1,
        VSTACK(h,HSTACK(u,m,q,m-q))
)
split-expenses.xlsx
ABCDEFGHIJKLMNOPQ
1sample table: "exp"ATEXTSPLIT returns numbers as numbers even if the array
2has also text, so we can sum by row already at this stage=BYROW(D4#,LAMBDA(x,SUM(x)))
3Expense Details=ATEXTSPLIT(exp[Expense Details],{":",","})↓↓↓
4Phil: 255.12,121.29,160.23Phil255.12121.29160.23536.64PersonSpentShareTo Receive/Pay
5Alan: 43.17,226.76,343.66,358.01Alan43.17226.76343.66358.01971.6Phil4928.882982.4351946.445
6Phil: 159.95,177.56,278.37Phil159.95177.56278.37615.88Alan2723.082982.435-259.355
7Stu: 206.51,188.09,244.68,33.21,152.98Stu206.51188.09244.6833.21152.98825.47Stu1961.672982.435-1020.765
8Phil: 285.91,306.4,140.68Phil285.91306.4140.68732.99Doug2316.112982.435-666.325
9Doug: 226.76,226.76,169.6,208.67Doug226.76226.76169.6208.67831.79
10Phil: 326.4Phil326.4326.4
11Phil: 308.43,224.95,102.33Phil308.43224.95102.33635.71
12Phil: 168.64Phil168.64168.64
13Stu: 259.93Stu259.93259.93
14Stu: 121.29Stu121.29121.29
15Stu: 177Stu177177
16Alan: 172.23Alan172.23172.23
17Alan: 214.04,162.58,205.66,188.16,128.41Alan214.04162.58205.66188.16128.41898.85
18Alan: 62.75,160.51,173.34,283.8Alan62.75160.51173.34283.8680.4
19Phil: 356.3,361.34,41.35,255.94,67.42Phil356.3361.3441.35255.9467.421082.35
20Stu: 168.25,161.28,248.45Stu168.25161.28248.45577.98
21Doug: 163.93,308.43,218.58Doug163.93308.43218.58690.94
22Phil: 226.04,265.97,140.81,197.45Phil226.04265.97140.81197.45830.27
23Doug: 216.79,180.39,246.65,149.55Doug216.79180.39246.65149.55793.38
24
new ATEXTSPLIT 13
Cell Formulas
RangeFormula
K2K2=FORMULATEXT(K4)
D3D3=FORMULATEXT(D4)
D4:I23D4=ATEXTSPLIT(exp[Expense Details],{":",","})
K4:K23K4=BYROW(D4#,LAMBDA(x,SUM(x)))
M4:P8M4=LET(a,expG[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"}, b,ATEXTSPLIT(a,{":",","}), n,INDEX(b,,1),u,UNIQUE(n), t,BYROW(b,LAMBDA(x,SUM(x))), m,MAP(u,LAMBDA(x,SUM(IF(x=n,t)))), q,SUM(m)/4+SEQUENCE(ROWS(u))^0-1, VSTACK(h,HSTACK(u,m,q,m-q)) )
Dynamic array formulas.
 
1st solution new alternative (no ATEXTSPLIT, uses BYROW(initial array), no need of BYROW(array construction))
Single cell formula at F6:
Excel Formula:
=LET(a,exp[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"},
        p,TEXTBEFORE(a,":"),u,UNIQUE(p),
        sm,BYROW(a,LAMBDA(x,LET(y,TEXTSPLIT(x,{":",","}),SUM(IFERROR(--y,y))))),
        sp,MAP(u,LAMBDA(x,SUM(IF(x=p,sm)))),
        sh,SUM(sp)/4+SEQUENCE(ROWS(u))^0-1,
        VSTACK(h,HSTACK(u,sp,sh,sp-sh))
)
split-expenses.xlsx
ABCDEFGHIJKL
1sample table: "exp"1st solution.New concept, no array construction before BYROW, BYROW directly with initial array
2
3Expense Details=BYROW(exp[Expense Details],LAMBDA(x,LET(y,TEXTSPLIT(x,{":",","}),SUM(IFERROR(--y,y)))))
4Phil: 255.12,121.29,160.23536.64
5Alan: 43.17,226.76,343.66,358.01971.6
6Phil: 159.95,177.56,278.37615.88PersonSpentShareTo Receive/Pay
7Stu: 206.51,188.09,244.68,33.21,152.98825.47Phil4928.882982.4351946.445
8Phil: 285.91,306.4,140.68732.99Alan2723.082982.435-259.355
9Doug: 226.76,226.76,169.6,208.67831.79Stu1961.672982.435-1020.765
10Phil: 326.4326.4Doug2316.112982.435-666.325
11Phil: 308.43,224.95,102.33635.71
12Phil: 168.64168.64
13Stu: 259.93259.93
14Stu: 121.29121.29
15Stu: 177177
16Alan: 172.23172.23
17Alan: 214.04,162.58,205.66,188.16,128.41898.85
18Alan: 62.75,160.51,173.34,283.8680.4
19Phil: 356.3,361.34,41.35,255.94,67.421082.35
20Stu: 168.25,161.28,248.45577.98
21Doug: 163.93,308.43,218.58690.94
22Phil: 226.04,265.97,140.81,197.45830.27
23Doug: 216.79,180.39,246.65,149.55793.38
24
new ATEXTSPLIT 14
Cell Formulas
RangeFormula
D3D3=FORMULATEXT(D4)
D4:D23D4=BYROW(exp[Expense Details],LAMBDA(x,LET(y,TEXTSPLIT(x,{":",","}),SUM(IFERROR(--y,y)))))
F6:I10F6=LET(a,exp[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"}, p,TEXTBEFORE(a,":"), u,UNIQUE(p), sm,BYROW(a,LAMBDA(x,LET(y,TEXTSPLIT(x,{":",","}),SUM(IFERROR(--y,y))))), sp,MAP(u,LAMBDA(x,SUM(IF(x=p,sm)))), sh,SUM(sp)/4+SEQUENCE(ROWS(u))^0-1, VSTACK(h,HSTACK(u,sp,sh,sp-sh)) )
Dynamic array formulas.
 
Great news from MrExcel, today's YT (27Jun2022) announcing latest Excel improvements to TEXT….functions.
Excel Improvements To TEXTBEFORE
As much as I like ATEXTSPLIT in its "analogic" form (does not use any of the new lambda helper functions and is lighting fast for large arrays) , if we want to unleash full functionality of TEXTSPLIT/TEXTBEFORE/TEXTAFTER functions, the easiest solution is to go "digital".
Here it is, ATEXTSPILL, a lambda helper function.
If a function can expand a cell into a row of spilled results, ATEXTSPILL can expand a column vector into rows.
Actually, it is a limited, shorter version of SPILLBYROWS, since the input array is only a column and not a 2D array. SPILLBYROWS can deal with any array.
And as function argument we can use any of the TEXT..functions with all its native functionality and arguments.
Examples inspired from Microsoft's support page: link
ATEXTSPILL(cl,fn,[nf])
cl: column vector
fn: lambda helper function argument: LAMBDA(x,TEXTSPLIT(x,...)) or TEXTBEFORE(x,...
[nf]: not found argument: if omitted => empty string ""
Excel Formula:
=LAMBDA(cl, fn, [nf],
    LET(
        e, IF(ISOMITTED(nf), "", nf),
        r, REDUCE(
            0,
            SEQUENCE(ROWS(cl)),
            LAMBDA(v, i, VSTACK(v, IFERROR(fn(INDEX(cl, i, 1)), e)))
        ),
        t, IFNA(DROP(r, 1), ""),
        a, IF(AND(t = ""), e, t),
        IFERROR(--a, a)
    )
)
Book1
ABCDEFGHIJKLM
1Split a name and a sentence by using a common delimiter.
2
3=ATEXTSPILL(B4:B5,LAMBDA(x,TEXTSPLIT(x," ")))
4Dakota Lennon SanchezDakotaLennonSanchez
5To be or not to beTobeornottobe
6
7Split array constants into 2x3 array
8=ATEXTSPILL(B9:B11,LAMBDA(x,TEXTSPLIT(x,",",";")))
91,2,3;4,5,6123
107,8,9;10,11,12456
1113,14,15;16,17,18789
12101112
13131415
14161718
15
16As we see ATEXTSPILL is capable of staking the results arrays of each row.
17
18or from a single cell:
19=ATEXTSPILL(TEXTJOIN(";",,B9:B11),LAMBDA(x,TEXTSPLIT(x,",",";")))
20123
21456
22789
23101112
24131415
25161718
26
27=ATEXTSPILL(B28:B29,LAMBDA(x,TEXTSPLIT(x,",",";")))
28a,b,c,d;e,f,g,habcd
291,2,3;4,5,6,7;8,9efgh
30123
314567
3289
33
34Note: The function excludes NA() errors by default even if pad argument in TEXTSPLIT is omitted
35For pad functionality we can give a value to "nf" argument
36
37=ATEXTSPILL(B28:B29,LAMBDA(x,TEXTSPLIT(x,",",";")),"empty")
38abcd
39efghWherever possible text is converted to numbers
40123empty=ISNUMBER(D38#)
414567FALSEFALSEFALSEFALSE
4289emptyemptyFALSEFALSEFALSEFALSE
43TRUETRUETRUEFALSE
44TRUETRUETRUETRUE
45TRUETRUEFALSEFALSE
46
Sheet1
Cell Formulas
RangeFormula
D3,I40,D37,D27,D19,D8D3=FORMULATEXT(D4)
D4:I5D4=ATEXTSPILL(B4:B5,LAMBDA(x,TEXTSPLIT(x," ")))
D9:F14D9=ATEXTSPILL(B9:B11,LAMBDA(x,TEXTSPLIT(x,",",";")))
D20:F25D20=ATEXTSPILL(TEXTJOIN(";",,B9:B11),LAMBDA(x,TEXTSPLIT(x,",",";")))
D28:G32D28=ATEXTSPILL(B28:B29,LAMBDA(x,TEXTSPLIT(x,",",";")))
D38:G42D38=ATEXTSPILL(B28:B29,LAMBDA(x,TEXTSPLIT(x,",",";")),"empty")
I41:L45I41=ISNUMBER(D38#)
Dynamic array formulas.
 
Book1
ABCDEFGHIJKLMNO
1
2including empty
3=ATEXTSPILL(B4:B5,LAMBDA(x, TEXTSPLIT(x,",",";")))
4a,,,,d;e,f,g,had
51,2,3;4,5,6,,7;8,9efgh
6123
74567
889
9
10ignoring empty
11=ATEXTSPILL(B4:B5,LAMBDA(x, TEXTSPLIT(x,",",";",1)))
12ad
13efgh
14123
154567
1689
17
18Cool example, "equalizing" different dimensions arrays into a
19fixed nr. of columns array, let's say 3 columns, to see what we get.
20
21a1abcdeAB
22fghijCD
23EF
24a21234GH
255678a3
269101112
27
28single cell formula
29=ATEXTSPILL(TEXTJOIN({",",",",";"},,D21:H22,D24:G26,J21:K24),LAMBDA(x,TEXTSPLIT(x,",",";")))
30abc
31defThis was possible because of TEXTJOIN versatility
32ghito work with an array of delimiters
33j12
34345=TEXTJOIN({",",",","|"},,J21:K24)
35678A,B,C|D,E,F|G,H
3691011
3712AB
38CDE
39FGH
40
Sheet2
Cell Formulas
RangeFormula
D3,J34,D29,D11D3=FORMULATEXT(D4)
D4:H8D4=ATEXTSPILL(B4:B5,LAMBDA(x, TEXTSPLIT(x,",",";")))
D12:G16D12=ATEXTSPILL(B4:B5,LAMBDA(x, TEXTSPLIT(x,",",";",1)))
D30:F39D30=ATEXTSPILL(TEXTJOIN({",",",",";"},,D21:H22,D24:G26,J21:K24),LAMBDA(x,TEXTSPLIT(x,",",";")))
J35J35=TEXTJOIN({",",",","|"},,J21:K24)
Dynamic array formulas.
 
The following examples will cover all arguments functionality:
- arrays of delimiters
- multiple chars delimiters
- multiple chars delimiters arrays
- include/ignore empty
- column/row delimiters
- case sensitive/case insensitive
- match end 0/1
ATEXTSPILL and nested ATEXTSPILL functions using also TEXTBEFORE/TEXTAFTER
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOP
1split only for spaces
2cl=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x," ")))emty strings
3Do. Or do not. There is no try. -AnonymousDo.Ordonot.Thereisnotry.-Anonymous
4There are two options: Adapt or die. Therearetwooptions:Adaptordie.
5One cannot step twice in the same river. -HeraclitusOnecannotsteptwiceinthesameriver.-Heraclitus
6Whatever you are, be a good one. -Abraham LincolnWhateveryouare,beagoodone.-AbrahamLincoln
7
8separators are: space , . : -split for all separators, single chars, (include empty by default)
9=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",",",".",":","-"})))
10dbl chars separators ". " , ", " ,": "DoOrdonotThereisnotryAnonymous
11( . , and : followed by space)TherearetwooptionsAdaptordie
12OnecannotsteptwiceinthesameriverHeraclitus
13WhateveryouarebeagoodoneAbrahamLincoln
14
15split for all separators, some double chars sep, (include empty by default)
16=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": ","-"})))
17DoOrdonotThereisnotryAnonymous
18TherearetwooptionsAdaptordie
19OnecannotsteptwiceinthesameriverHeraclitus
20WhateveryouarebeagoodoneAbrahamLincoln
21
22wanted solution: all possible separators, dbl chars sep, ignore empty
23=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": ","-"},,1)))
24DoOrdonotThereisnotryAnonymous
25TherearetwooptionsAdaptordie
26OnecannotsteptwiceinthesameriverHeraclitus
27WhateveryouarebeagoodoneAbrahamLincoln
28
29extract author in a separate row
30=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},"-",1)))
31DoOrdonotThereisnotry
32Anonymous
33TherearetwooptionsAdaptordie
34Onecannotsteptwiceinthesameriver
35Heraclitus
36Whateveryouarebeagoodone
37AbrahamLincoln
38
ATS 1
Cell Formulas
RangeFormula
C2,C30,C23,C16,C9C2=FORMULATEXT(C3)
C3:K6C3=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x," ")))
C10:O13C10=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",",",".",":","-"})))
C17:L20C17=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": ","-"})))
C24:K27C24=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": ","-"},,1)))
C31:J37C31=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},"-",1)))
Dynamic array formulas.
 
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOP
1ATEXTFILL with TEXTBEFORE/TEXTAFTER functions
2cl=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTBEFORE(x,"-")))
3Do. Or do not. There is no try. -AnonymousDo. Or do not. There is no try.
4There are two options: Adapt or die.
5One cannot step twice in the same river. -HeraclitusOne cannot step twice in the same river.
6Whatever you are, be a good one. -Abraham LincolnWhatever you are, be a good one.
7
8extract quotes
9match to end argument,1extract quotes words single cell formula, nested ATEXTFILL with 2 different TEXT…functions
10=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTBEFORE(x,"-",,,1)))=ATEXTSPILL(ATEXTSPILL(A3:A6,LAMBDA(x,TEXTBEFORE(x,"-",,,1))),LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},,1)))
11Do. Or do not. There is no try. DoOrdonotThereisnotry
12There are two options: Adapt or die. TherearetwooptionsAdaptordie
13One cannot step twice in the same river. Onecannotsteptwiceinthesameriver
14Whatever you are, be a good one. Whateveryouarebeagoodone
15
16extracting authors
17=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")))
18Anonymous
19
20Heraclitus
21Abraham Lincoln
22
23using "nf" argument to fill the blankextracting full name, single cell formula, nested ATEXTFILL
24=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")),"Anonymous")=ATEXTSPILL(ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")),"Anonymous"),LAMBDA(x,TEXTSPLIT(x," ")))
25AnonymousAnonymous
26AnonymousAnonymous
27HeraclitusHeraclitus
28Abraham LincolnAbrahamLincoln
29
ATS 2
Cell Formulas
RangeFormula
C2,F24,C17,E10C2=FORMULATEXT(C3)
C3:C6C3=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTBEFORE(x,"-")))
B10,B24B10=FORMULATEXT(C11)
C11:C14C11=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTBEFORE(x,"-",,,1)))
E11:L14E11=ATEXTSPILL(ATEXTSPILL(A3:A6,LAMBDA(x,TEXTBEFORE(x,"-",,,1))),LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},,1)))
C18:C21C18=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")))
C25:C28C25=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")),"Anonymous")
F25:G28F25=ATEXTSPILL(ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")),"Anonymous"),LAMBDA(x,TEXTSPLIT(x," ")))
Dynamic array formulas.
 
ATEXTSPILL.xlsx
ABCDEFGHI
1by default delimiters are case sensitive
2cl=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x")))
315.21x12.4x21.5X13.18x78.65x48.215.2112.421.5X13.1878.6548.2
418.3x53.84X72.83x18.47x58.412x36.3718.353.84X72.8318.4758.41236.37
5
6=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,{"x","X"})))
715.2112.421.513.1878.6548.2
818.353.8472.8318.4758.41236.37
9
10or
11using case insensitive match argument => 1
12=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x",,,1)))
1315.2112.421.513.1878.6548.2
1418.353.8472.8318.4758.41236.37
15
16=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x","X")))
1715.2112.421.5
1813.1878.6548.2
1918.353.84
2072.8318.4758.41236.37
21
ATS 3
Cell Formulas
RangeFormula
C2,C16,C12,C6C2=FORMULATEXT(C3)
C3:G4C3=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x")))
C7:H8C7=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,{"x","X"})))
C13:H14C13=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x",,,1)))
C17:F20C17=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x","X")))
Dynamic array formulas.
 
ATEXTSPILL with other functions, no TEXT... related.
Limitless possibilities to design patterns.
ATEXTSPILL.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1Versatility of ATEXTSPILL to expand, with any function capable of that.
2Creating patterns, limitless possibilities
3gradual random arrays
4=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,4)))=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,RANDARRAY(,7,10*x,10*x+9,1)))
5123416121313171715
6123426222220272228every row creates random nr. between
712343032313731393710 and 19
812344340464541404220 and 29
912345754575955535030 and 39
10123460666762656963·········
11123470717570727272
12
13=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)))=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,RANDARRAY(,x,10*x,10*x+9,1)))
14115
15122323
16123363239
17123443464444
18123455653515059
19123456616563626867
20123456772797671777678
21
22steps pattern (horizontal)
23=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,REPT("A",SEQUENCE(,x)^0)))=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,2*x-1)))
24A1
25AA123
26AAA12345
27AAAA1234567
28AAAAA123456789
29AAAAAA1234567891011
30AAAAAAA12345678910111213
31
32=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)^0-1+x))=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)+64)))
331A
3422AB
35333ABC
364444ABCD
3755555ABCDE
38666666ABCDEF
397777777ABCDEFG
40
41cl=ATEXTSPILL(B42:B48,LAMBDA(x,SEQUENCE(,x)),"---")=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)^0+63+x)))
4271234567A
433123BB
44512345CCC
45---DDDD
46512345EEEEE
473123FFFFFF
4871234567GGGGGGG
49
50=ATEXTSPILL(8-SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)))=ATEXTSPILL(8-SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)^0+63+x)))
511234567GGGGGGG
52123456FFFFFF
5312345EEEEE
541234DDDD
55123CCC
5612BB
571A
58
59=ATEXTSPILL(ABS(SEQUENCE(9)-5),LAMBDA(x,SEQUENCE(,x+1)))=ATEXTSPILL(MOD(SEQUENCE(8),3)+1,LAMBDA(x,SEQUENCE(,x)))
601234512
611234123
621231
631212
641123
65121
6612312
671234123
6812345
69steps pattern (horiz and vert)
70=ATEXTSPILL(ABS(6-ABS(SEQUENCE(9,,9,-1)-5)),LAMBDA(x,SEQUENCE(,x+1)))=ATEXTSPILL(QUOTIENT(SEQUENCE(10),3)+1,LAMBDA(x,SEQUENCE(,2*x)))
7112312
72123412
73123451234
741234561234
7512345671234
76123456123456
7712345123456
781234123456
7912312345678
8012345678
81
ATS 4
Cell Formulas
RangeFormula
B4,B59,B32,B23,B13B4=FORMULATEXT(D5)
O4,O70,O59,D50,O50,D41,O41,O32,O23,O13O4=FORMULATEXT(O5)
D5:G11D5=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,4)))
O5:U11O5=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,RANDARRAY(,7,10*x,10*x+9,1)))
D14:J20D14=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)))
O14:U20O14=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,RANDARRAY(,x,10*x,10*x+9,1)))
D24:J30D24=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,REPT("A",SEQUENCE(,x)^0)))
O24:AA30O24=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,2*x-1)))
D33:J39D33=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)^0-1+x))
O33:U39O33=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)+64)))
D42:J48D42=ATEXTSPILL(B42:B48,LAMBDA(x,SEQUENCE(,x)),"---")
O42:U48O42=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)^0+63+x)))
D51:J57D51=ATEXTSPILL(8-SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)))
O51:U57O51=ATEXTSPILL(8-SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)^0+63+x)))
D60:H68D60=ATEXTSPILL(ABS(SEQUENCE(9)-5),LAMBDA(x,SEQUENCE(,x+1)))
O60:Q67O60=ATEXTSPILL(MOD(SEQUENCE(8),3)+1,LAMBDA(x,SEQUENCE(,x)))
A70A70=FORMULATEXT(D71)
D71:J79D71=ATEXTSPILL(ABS(6-ABS(SEQUENCE(9,,9,-1)-5)),LAMBDA(x,SEQUENCE(,x+1)))
O71:V80O71=ATEXTSPILL(QUOTIENT(SEQUENCE(10),3)+1,LAMBDA(x,SEQUENCE(,2*x)))
Dynamic array formulas.
 

Forum statistics

Threads
1,223,880
Messages
6,175,157
Members
452,615
Latest member
bogeys2birdies

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