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
832
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
1st solution. Step by step calculations.
split-expenses.xlsx
ABCDEFGHIJKLMNOPQ
1sample table: "exp"step 1
2=TEXTBEFORE(exp[Expense Details],":")
3Expense Details↓↓↓step 2step 3: TEXTSPLIT array (using trick concept)step 4
4Phil: 255.12,121.29,160.23↓↓↓=TEXTAFTER(exp[Expense Details],":")=IFNA(DROP(--TEXTSPLIT(TEXTJOIN("",,F5#&":"),",",":"),-1),"")=BYROW(H5#,LAMBDA(x,SUM(x)))
5Alan: 43.17,226.76,343.66,358.01Phil 255.12,121.29,160.23255.12121.29160.23536.64
6Phil: 159.95,177.56,278.37Alan 43.17,226.76,343.66,358.0143.17226.76343.66358.01971.6
7Stu: 206.51,188.09,244.68,33.21,152.98Phil 159.95,177.56,278.37159.95177.56278.37615.88
8Phil: 285.91,306.4,140.68Stu 206.51,188.09,244.68,33.21,152.98206.51188.09244.6833.21152.98825.47
9Doug: 226.76,226.76,169.6,208.67Phil 285.91,306.4,140.68285.91306.4140.68732.99
10Phil: 326.4Doug 226.76,226.76,169.6,208.67226.76226.76169.6208.67831.79
11Phil: 308.43,224.95,102.33Phil326.4326.4326.4
12Phil: 168.64Phil 308.43,224.95,102.33308.43224.95102.33635.71
13Stu: 259.93Phil168.64168.64168.64
14Stu: 121.29Stu259.93259.93259.93
15Stu: 177Stu121.29121.29121.29
16Alan: 172.23Stu177177177
17Alan: 214.04,162.58,205.66,188.16,128.41Alan172.23172.23172.23
18Alan: 62.75,160.51,173.34,283.8Alan 214.04,162.58,205.66,188.16,128.41214.04162.58205.66188.16128.41898.85
19Phil: 356.3,361.34,41.35,255.94,67.42Alan 62.75,160.51,173.34,283.862.75160.51173.34283.8680.4
20Stu: 168.25,161.28,248.45Phil 356.3,361.34,41.35,255.94,67.42356.3361.3441.35255.9467.421082.35
21Doug: 163.93,308.43,218.58Stu 168.25,161.28,248.45168.25161.28248.45577.98
22Phil: 226.04,265.97,140.81,197.45Doug 163.93,308.43,218.58163.93308.43218.58690.94
23Doug: 216.79,180.39,246.65,149.55Phil 226.04,265.97,140.81,197.45226.04265.97140.81197.45830.27
24Doug 216.79,180.39,246.65,149.55216.79180.39246.65149.55793.38
25
26step 5: unq namesstep 6: sum expenses clmstep 7: share of expenses clmstep 8: To Receive/Pay clm
27=UNIQUE(D5#)=MAP(D28#,LAMBDA(x,SUM((x=D5#)*N5#)))=SUM(F28#)/4+SEQUENCE(ROWS(D28#))^0-1=F28#-H28#
28Phil4928.882982.4351946.445
29Alan2723.082982.435-259.355
30Stu1961.672982.435-1020.77
31Doug2316.112982.435-666.325
32
new ATEXTSPLIT 3
Cell Formulas
RangeFormula
D2D2=FORMULATEXT(D5)
H4,N4,D27,F27,H27,M27,F4H4=FORMULATEXT(H5)
D5:D24D5=TEXTBEFORE(exp[Expense Details],":")
F5:F24F5=TEXTAFTER(exp[Expense Details],":")
H5:L24H5=IFNA(DROP(--TEXTSPLIT(TEXTJOIN("",,F5#&":"),",",":"),-1),"")
N5:N24N5=BYROW(H5#,LAMBDA(x,SUM(x)))
D28:D31D28=UNIQUE(D5#)
F28:F31F28=MAP(D28#,LAMBDA(x,SUM((x=D5#)*N5#)))
H28:H31H28=SUM(F28#)/4+SEQUENCE(ROWS(D28#))^0-1
M28:M31M28=F28#-H28#
Dynamic array formulas.
 
1st solution. Single cell formula set at D3:
Excel Formula:
=LET(a,exp[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"},b,",",c,":",
           n,TEXTBEFORE(a,c),s,TEXTAFTER(a,c),u,UNIQUE(n),
           t,BYROW(DROP(IFNA(--TEXTSPLIT(TEXTJOIN("",,s&c),b,c),0),-1),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
ABCDEFGH
1sample table: "exp"Single cell formula
2
3Expense DetailsPersonSpentShareTo Receive/Pay
4Phil: 255.12,121.29,160.23Phil4928.882982.4351946.445
5Alan: 43.17,226.76,343.66,358.01Alan2723.082982.435-259.355
6Phil: 159.95,177.56,278.37Stu1961.672982.435-1020.765
7Stu: 206.51,188.09,244.68,33.21,152.98Doug2316.112982.435-666.325
8Phil: 285.91,306.4,140.68
9Doug: 226.76,226.76,169.6,208.67
10Phil: 326.4
11Phil: 308.43,224.95,102.33
12Phil: 168.64
13Stu: 259.93
14Stu: 121.29
15Stu: 177
16Alan: 172.23
17Alan: 214.04,162.58,205.66,188.16,128.41
18Alan: 62.75,160.51,173.34,283.8
19Phil: 356.3,361.34,41.35,255.94,67.42
20Stu: 168.25,161.28,248.45
21Doug: 163.93,308.43,218.58
22Phil: 226.04,265.97,140.81,197.45
23Doug: 216.79,180.39,246.65,149.55
24
new ATEXTSPLIT 4
Cell Formulas
RangeFormula
D3:G7D3=LET(a,exp[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"},b,",",c,":", n,TEXTBEFORE(a,c),s,TEXTAFTER(a,c),u,UNIQUE(n), t,BYROW(DROP(IFNA(--TEXTSPLIT(TEXTJOIN("",,s&c),b,c),0),-1),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.
 
Before jumping to 2nd solution of Chandoo challenge I was just in the mood of rewriting ATEXTSPLIT as simple as possible, so I have removed from the previous version some quirks and features:
- replaced "special" chars "º" and "ª",with CHAR(1) and CHAR(2)
No more risk of this new invisible chars to be hidden in the initial array because, anyhow, I have used as first operation in the formula a CLEAN(ar) that will remove them, just in case.
- new function is free of calling other custom-made functions or any of the new Excel functions, removed AFLAT, so now, initial array "ar" should be only 1D vertical array.
- removed "," as default delimiter if "dl" is omitted.
The rest of the designed concept is identical with the previous function.
No limitations. Being an "analogic" function is lighting fast for very large arrays, can split arrays that have 32767 chars on each cell.
Before I have said: Whatever TEXTJOIN joins ATEXTSPLIT splits.
Now we can say: Whatever TEXTSPLIT can split cell by cell, ATEXTSPLIT can split an entire array.
ATEXTSPLIT(ar,dl,[ea])
ar: 1D vertical array (if 2D we can use TOCOL(ar))
dl: delimiter
[ea]: empty argument, if omitted ignores empty, if 1 or <>0 includes empty
Excel Formula:
=LAMBDA(ar,[dl],[ea],
    LET(d, CHAR(1),s, CHAR(2),f, CLEAN(ar),t, dl,
        a, SUBSTITUTE(f, t, d),
        b, IF(ea,a,SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(a, " ", s), d, " ")), " ", d),s," ")),
        n, LEN(b) - LEN(SUBSTITUTE(b, d, "")) + 1,
        m, MAX(n),
        c, SEQUENCE(, m),
        x, SEARCH(s, SUBSTITUTE(d & b, d, s, c)),
        y, SEARCH(s, SUBSTITUTE(b & d, d, s, c)),
        z, IFERROR(MID(b, x, y - x), ""),
        IFERROR(--z, z)
    )
)
split-expenses.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
2Crazy array example to check TEXTSPLIT and ATEXTSPLIT neck to neck
3
41. ignore empty2. include empty
5=TEXTSPLIT(B6,",",,1)=TEXTSPLIT(B6,",")
6a , ,, , b,, ,c, ,,a b c a b c
7, ,, x ,, ,,,y,z, , x yz   x yz
8=TEXTSPLIT(B7,",",,1)=TEXTSPLIT(B7,",")
9
10checking LENchecking LEN
11=LEN(D6:J7)=LEN(M6:X7)
122112111210120111000
131311110010301001110
14
15=ATEXTSPLIT(B6:B7,",")=ATEXTSPLIT(B6:B7,",",1)
16a b c a b c
17 x yz x yz
18
19checking LENchecking LEN
20=LEN(D16:J17)=LEN(M16#)
212112111210120111000
221311110010301001110
23
24=SUM(AND(D12#-D21#))=SUM(M12#-M21#)
250<= same results =>0
26
new ATEXTSPLIT 5
Cell Formulas
RangeFormula
D5,M24,D24,M20,D20,M15,D15,M11,D11,M5D5=FORMULATEXT(D6)
D6:J6,D7:I7D6=TEXTSPLIT(B6,",",,1)
M6:W6,M7:X7M6=TEXTSPLIT(B6,",")
D8,M8D8=FORMULATEXT(D7)
D12:J13D12=LEN(D6:J7)
M12:X13M12=LEN(M6:X7)
D16:J17D16=ATEXTSPLIT(B6:B7,",")
M16:X17M16=ATEXTSPLIT(B6:B7,",",1)
D21:J22D21=LEN(D16:J17)
M21:X22M21=LEN(M16#)
D25D25=SUM(AND(D12#-D21#))
M25M25=SUM(M12#-M21#)
Dynamic array formulas.
 
2nd solution. Using ATEXTSPLIT. Step by step
split-expenses.xlsx
ABCDEFGHIJKLM
1sample table: "exp"step 1rest of steps identical to 1st solution
2=TEXTBEFORE(exp[Expense Details],":")
3Expense Details↓↓↓step 2step 3: using ATEXTSPLIT
4Phil: 255.12,121.29,160.23↓↓↓=TEXTAFTER(exp[Expense Details],":")=ATEXTSPLIT(F5#,",")
5Alan: 43.17,226.76,343.66,358.01Phil 255.12,121.29,160.23255.12121.29160.23
6Phil: 159.95,177.56,278.37Alan 43.17,226.76,343.66,358.0143.17226.76343.66358.01
7Stu: 206.51,188.09,244.68,33.21,152.98Phil 159.95,177.56,278.37159.95177.56278.37
8Phil: 285.91,306.4,140.68Stu 206.51,188.09,244.68,33.21,152.98206.51188.09244.6833.21152.98
9Doug: 226.76,226.76,169.6,208.67Phil 285.91,306.4,140.68285.91306.4140.68
10Phil: 326.4Doug 226.76,226.76,169.6,208.67226.76226.76169.6208.67
11Phil: 308.43,224.95,102.33Phil326.4326.4
12Phil: 168.64Phil 308.43,224.95,102.33308.43224.95102.33
13Stu: 259.93Phil168.64168.64
14Stu: 121.29Stu259.93259.93
15Stu: 177Stu121.29121.29
16Alan: 172.23Stu177177
17Alan: 214.04,162.58,205.66,188.16,128.41Alan172.23172.23
18Alan: 62.75,160.51,173.34,283.8Alan 214.04,162.58,205.66,188.16,128.41214.04162.58205.66188.16128.41
19Phil: 356.3,361.34,41.35,255.94,67.42Alan 62.75,160.51,173.34,283.862.75160.51173.34283.8
20Stu: 168.25,161.28,248.45Phil 356.3,361.34,41.35,255.94,67.42356.3361.3441.35255.9467.42
21Doug: 163.93,308.43,218.58Stu 168.25,161.28,248.45168.25161.28248.45
22Phil: 226.04,265.97,140.81,197.45Doug 163.93,308.43,218.58163.93308.43218.58
23Doug: 216.79,180.39,246.65,149.55Phil 226.04,265.97,140.81,197.45226.04265.97140.81197.45
24Doug 216.79,180.39,246.65,149.55216.79180.39246.65149.55
25
new ATEXTSPLIT 6
Cell Formulas
RangeFormula
D2D2=FORMULATEXT(D5)
H4,F4H4=FORMULATEXT(H5)
D5:D24D5=TEXTBEFORE(exp[Expense Details],":")
F5:F24F5=TEXTAFTER(exp[Expense Details],":")
H5:L24H5=ATEXTSPLIT(F5#,",")
Dynamic array formulas.
 
2nd solution. Using ATEXTSPLIT. Single cell formula at D3
Excel Formula:
=LET(a,exp[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"},b,",",c,":",
           n,TEXTBEFORE(a,c),s,TEXTAFTER(a,c),u,UNIQUE(n),
           t,ATEXTSPLIT(s,","),
           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
ABCDEFGH
1sample table: "exp"single cell formula. Calls ATEXTSPLIT
2
3Expense DetailsPersonSpentShareTo Receive/Pay
4Phil: 255.12,121.29,160.23Phil4928.92982.4351946.445
5Alan: 43.17,226.76,343.66,358.01Alan2723.12982.435-259.355
6Phil: 159.95,177.56,278.37Stu1961.72982.435-1020.765
7Stu: 206.51,188.09,244.68,33.21,152.98Doug2316.12982.435-666.325
8Phil: 285.91,306.4,140.68
9Doug: 226.76,226.76,169.6,208.67
10Phil: 326.4
11Phil: 308.43,224.95,102.33
12Phil: 168.64
13Stu: 259.93
14Stu: 121.29
15Stu: 177
16Alan: 172.23
17Alan: 214.04,162.58,205.66,188.16,128.41
18Alan: 62.75,160.51,173.34,283.8
19Phil: 356.3,361.34,41.35,255.94,67.42
20Stu: 168.25,161.28,248.45
21Doug: 163.93,308.43,218.58
22Phil: 226.04,265.97,140.81,197.45
23Doug: 216.79,180.39,246.65,149.55
24
25
new ATEXTSPLIT 7
Cell Formulas
RangeFormula
D3:G7D3=LET(a,exp[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"},b,",",c,":", n,TEXTBEFORE(a,c),s,TEXTAFTER(a,c),u,UNIQUE(n), t,ATEXTSPLIT(s,","), 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 alternative, step by step
split-expenses.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1sample table: "exp"1st solution, alternative, step by step
2step 1step 2step 3step 4
3Expense Details=IFNA(TEXTSPLIT(TEXTJOIN("-",,expE[Expense Details]),{":",","},"-"),0)=TAKE(D4#,,1)=--DROP(D4#,,1)=BYROW(M4#,LAMBDA(x,SUM(x)))
4Phil: 255.12,121.29,160.23Phil255.12121.29160.2300Phil255.12121.29160.2300536.64
5Alan: 43.17,226.76,343.66,358.01Alan43.17226.76343.66358.010Alan43.17226.76343.66358.010971.6
6Phil: 159.95,177.56,278.37Phil159.95177.56278.3700Phil159.95177.56278.3700615.88
7Stu: 206.51,188.09,244.68,33.21,152.98Stu206.51188.09244.6833.21152.98Stu206.51188.09244.6833.21152.98825.47
8Phil: 285.91,306.4,140.68Phil285.91306.4140.6800Phil285.91306.4140.6800732.99
9Doug: 226.76,226.76,169.6,208.67Doug226.76226.76169.6208.670Doug226.76226.76169.6208.670831.79
10Phil: 326.4Phil326.40000Phil326.40000326.4
11Phil: 308.43,224.95,102.33Phil308.43224.95102.3300Phil308.43224.95102.3300635.71
12Phil: 168.64Phil168.640000Phil168.640000168.64
13Stu: 259.93Stu259.930000Stu259.930000259.93
14Stu: 121.29Stu121.290000Stu121.290000121.29
15Stu: 177Stu1770000Stu1770000177
16Alan: 172.23Alan172.230000Alan172.230000172.23
17Alan: 214.04,162.58,205.66,188.16,128.41Alan214.04162.58205.66188.16128.41Alan214.04162.58205.66188.16128.41898.85
18Alan: 62.75,160.51,173.34,283.8Alan62.75160.51173.34283.80Alan62.75160.51173.34283.80680.4
19Phil: 356.3,361.34,41.35,255.94,67.42Phil356.3361.3441.35255.9467.42Phil356.3361.3441.35255.9467.421082.35
20Stu: 168.25,161.28,248.45Stu168.25161.28248.4500Stu168.25161.28248.4500577.98
21Doug: 163.93,308.43,218.58Doug163.93308.43218.5800Doug163.93308.43218.5800690.94
22Phil: 226.04,265.97,140.81,197.45Phil226.04265.97140.81197.450Phil226.04265.97140.81197.450830.27
23Doug: 216.79,180.39,246.65,149.55Doug216.79180.39246.65149.550Doug216.79180.39246.65149.550793.38
24
25step 5step 6step 7step 8
26=UNIQUE(K4#)=MAP(D27#,LAMBDA(x,SUM(IF(x=K4#,S4#))))=SUM(F27#)/4+SEQUENCE(ROWS(D27#))^0-1=F27#-K27#
27Phil4928.882982.41946.4
28Alan2723.082982.4-259.36
29Stu1961.672982.4-1020.8
30Doug2316.112982.4-666.33
31
32step 9
33=HSTACK(D27#,F27#,K27#,R27#)
34Phil4928.882982.4351946.445
35Alan2723.082982.435-259.355
36Stu1961.672982.435-1020.765
37Doug2316.112982.435-666.325
38
new ATEXTSPLIT 8
Cell Formulas
RangeFormula
D3,K3,M3,D33,R26,K26,D26,F26,S3D3=FORMULATEXT(D4)
D4:I23D4=IFNA(TEXTSPLIT(TEXTJOIN("-",,expE[Expense Details]),{":",","},"-"),0)
K4:K23K4=TAKE(D4#,,1)
M4:Q23M4=--DROP(D4#,,1)
S4:S23S4=BYROW(M4#,LAMBDA(x,SUM(x)))
D27:D30D27=UNIQUE(K4#)
F27:F30F27=MAP(D27#,LAMBDA(x,SUM(IF(x=K4#,S4#))))
K27:K30K27=SUM(F27#)/4+SEQUENCE(ROWS(D27#))^0-1
R27:R30R27=F27#-K27#
D34:G37D34=HSTACK(D27#,F27#,K27#,R27#)
Dynamic array formulas.
 
1st solution alternative, single cell formula at D3.
Does not use anymore TEXTBEFORE or TEXTAFTER, uses versatility of TEXTSPLIT to split for an array of delimiters {":",","}
Excel Formula:
=LET(a,exp[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"},
   b,IFNA(TEXTSPLIT(TEXTJOIN("-",,a),{":",","},"-"),0),n,TAKE(b,,1),s,--DROP(b,,1),
   t,BYROW(s,LAMBDA(x,SUM(x))),u,UNIQUE(n),
   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
ABCDEFGH
1sample table: "exp"
2
3Expense DetailsPersonSpentShareTo Receive/Pay
4Phil: 255.12,121.29,160.23Phil4928.882982.4351946.445
5Alan: 43.17,226.76,343.66,358.01Alan2723.082982.435-259.355
6Phil: 159.95,177.56,278.37Stu1961.672982.435-1020.765
7Stu: 206.51,188.09,244.68,33.21,152.98Doug2316.112982.435-666.325
8Phil: 285.91,306.4,140.68
9Doug: 226.76,226.76,169.6,208.67
10Phil: 326.4
11Phil: 308.43,224.95,102.33
12Phil: 168.64
13Stu: 259.93
14Stu: 121.29
15Stu: 177
16Alan: 172.23
17Alan: 214.04,162.58,205.66,188.16,128.41
18Alan: 62.75,160.51,173.34,283.8
19Phil: 356.3,361.34,41.35,255.94,67.42
20Stu: 168.25,161.28,248.45
21Doug: 163.93,308.43,218.58
22Phil: 226.04,265.97,140.81,197.45
23Doug: 216.79,180.39,246.65,149.55
24
new ATEXTSPLIT 9
Cell Formulas
RangeFormula
D3:G7D3=LET(a,exp[Expense Details],h,{"Person","Spent","Share","To Receive/Pay"},b,IFNA(TEXTSPLIT(TEXTJOIN("-",,a),{":",","},"-"),0),n,TAKE(b,,1),s,--DROP(b,,1),t,BYROW(s,LAMBDA(x,SUM(x))),u,UNIQUE(n),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.
 
Playing with TEXTSPLIT for this challenge I realized is a super cool function. I took the challenge to import its versatility also to ATEXTSPLIT.
New ATEXTSPLIT now can work with array of delimiters (solved this with a REDUCE function), has a new argument "pad" like TEXTSPLIT's argument "pad_with".
Also, empty arguments of the functions were reversed, now they share the same name and have the same functionality.
ATEXTSPLIT(ar,[dl],[ie],[pad])
ar: vertical 1D array
dl: delimiter or array of delimiters
[ie]: ignore empty (same functionality as in TEXTSPLIT)
-if omitted or 0 or FALSE, includes empty cells
-if 1 or TRUE or <>0, ignores empty cells
[pad]: pad_with argument, if omitted ="" (in TEXTSPLIT, if omitted, =NA() )
Excel Formula:
=LAMBDA(ar,dl,[ie],[pad],
    LET(d, CHAR(1),s, CHAR(2),p, " ",f, CLEAN(ar),
        a, REDUCE(f, SEQUENCE(COUNTA(dl)), LAMBDA(v,i, SUBSTITUTE(v, INDEX(dl, i), d))),
        b, IF(ie,SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(a, p, s), d, p)), p, d),s,p),a),
        n, LEN(b) - LEN(SUBSTITUTE(b, d, "")) + 1, m, MAX(n), c, SEQUENCE(, m),
        x, SEARCH(s, SUBSTITUTE(d & b, d, s, c)),
        y, SEARCH(s, SUBSTITUTE(b & d, d, s, c)),
        z, IFERROR(MID(b, x, y - x), IF(ISOMITTED(pad), "", pad)),
        IFERROR(--z, z)
    )
)
split-expenses.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Versatility TEXTSPLIT/ATEXTSPLIT
2
31. can split for an array of delimiters.
4Splitting will take place wherever any of the delimiters present in the array of delimiters is found.
5
6Complex array to check TEXTSPLIT and ATEXTSPLIT functionality neck to neck.
7The array has 3 different delimiters ( , : ; )
8Any sequence of any of the delimiters next to each other will be consider empty strings.
9Array has spaces, those spaces should be kept as spaces after splitting.
10
11a. ignore empty argument of both function omitted => empty cells included
12
13=TEXTSPLIT(A14,{":",";",","})=LEN(C14:M15)
14a, b;,c ,:; d: e ,fa bc d e f12020023100
15, : ; x;y,;z:v, :;  xyzv 01121011100both function return same rusults
16=TEXTSPLIT(A15,{":",";",","})=SUM(O14#)-SUM(O19#)
170
18=ATEXTSPLIT(A14:A15,{":",",",";"})=LEN(C19#)
19a bc d e f12020023100
20 xyzv 01121011100
21
22b. ignore empty argument of both function=1 => empty cells ignored
23
24=TEXTSPLIT(A14,{":",";",","},,1)=LEN(C25:I26)
25a bc d e f1222310
26 xyzv 1121111both function return same rusults
27=TEXTSPLIT(A15,{":",";",","},,1)=SUM(M25#)-SUM(M30#)
280
29=ATEXTSPLIT(A14:A15,{",",";",":"},1)=LEN(C30#)
30a bc d e f1222310
31 xyzv 1121111
32
new ATEXTSPLIT 10
Cell Formulas
RangeFormula
C13,C29,M29,U27,M24,C24,O18,C18,AA16,O13C13=FORMULATEXT(C14)
O14:Y15O14=LEN(C14:M15)
C14:K14,C15:M15C14=TEXTSPLIT(A14,{":",";",","})
C16,C27C16=FORMULATEXT(C15)
AA17AA17=SUM(O14#)-SUM(O19#)
C19:M20C19=ATEXTSPLIT(A14:A15,{":",",",";"})
O19:Y20O19=LEN(C19#)
M25:S26M25=LEN(C25:I26)
C25:H25,C26:I26C25=TEXTSPLIT(A14,{":",";",","},,1)
U28U28=SUM(M25#)-SUM(M30#)
C30:I31C30=ATEXTSPLIT(A14:A15,{",",";",":"},1)
M30:S31M30=LEN(C30#)
Dynamic array formulas.
 
split-expenses.xlsx
ABCDEFGHIJKLM
1Versatility TEXTSPLIT/ATEXTSPLIT
2
32. TEXTSPLIT has 2 arguments for delimiters, col delimiters and row delimiters.
4Since TEXTSPLIT splits single cells only, makes sense to be able to choose the orientation.
5Using both of them allows also TEXTSPLIT to split a single cell into an array, in 2 steps calculations.
6ATEXTSPLIT does array splitting natively, no need for 2nd delimiter.
7
83. "pad_with" argument
9This is useful only when TEXTSPLIT uses both delimiter arguments, to "level" missing array elements.
10If TEXTSPLIT omits "pad" argument, NA()'s will be returned
11If ATEXTSPLIT omits "pad" argument, "" will be returned
12
13
14Splitting array using TEXTSPLIT, (2 steps) "pad"="missing"
15=TEXTJOIN("|",,B16:B18)
16Stu: 168.25,161.28,248.45Stu: 168.25,161.28,248.45|Doug: 163.93,308.43,218.58|Phil: 226.04,265.97,140.81,197.45
17Doug: 163.93,308.43,218.58
18Phil: 226.04,265.97,140.81,197.45=TEXTSPLIT(D16,{":",","},"|",,"missing")after splitting everything
19Stu168.25161.28248.45missingwill stay as text
20Doug163.93308.43218.58missing=SUM(D19#)
21Phil226.04265.97140.81197.450
22
23Splitting array using ATEXTSPLIT, "pad"="missing"
24=ATEXTSPLIT(B16:B18,{":",","},,"missing")ATEXTSPLIT checks for possible
25Stu168.25161.28248.45missingnumeric values and wherever finds them
26Doug163.93308.43218.58missingconverts them to numbers
27Phil226.04265.97140.81197.45=SUM(D25#)
282099.19
29
new ATEXTSPLIT 11
Cell Formulas
RangeFormula
D15,J27,D24,J20,D18D15=FORMULATEXT(D16)
D16D16=TEXTJOIN("|",,B16:B18)
D19:H21D19=TEXTSPLIT(D16,{":",","},"|",,"missing")
J21J21=SUM(D19#)
D25:H27D25=ATEXTSPLIT(B16:B18,{":",","},,"missing")
J28J28=SUM(D25#)
Dynamic array formulas.
 
split-expenses.xlsx
ABCDEFGHIJKLMNO
1Versatility TEXTSPLIT/ATEXTSPLIT
2sample:
34. using multiple chars delimiters.numbers (with thousands separators)
4sampleseparated by space,comma,space
5a/b//c///d////e/////f//////g///////h123,345.24 , 4,567.23 , 0.237 , 38,235,104.32
6
7ie,omitted=TEXTSPLIT(K5," , ")
8=TEXTSPLIT(B5,"///")123,345.244,567.230.23738,235,104.32
9a/b//cd/e//fg/h
10check for numbers TEXTSPLIT
11=ATEXTSPLIT(B5,"///")=ISNUMBER(K8#)
12a/b//cd/e//fg/hFALSEFALSEFALSEFALSE
13
14ie,1=ATEXTSPLIT(K5," , ")
15=TEXTSPLIT(B5,"///",,1)123345.244567.230.23738235104.32
16a/b//cd/e//fg/h
17check for numbers ATEXTSPLIT
18=ATEXTSPLIT(B5,"///",1)=ISNUMBER(K15#)
19a/b//cd/e//fg/hTRUETRUETRUETRUE
20
new ATEXTSPLIT 12
Cell Formulas
RangeFormula
K7,K18,B18,B15,K14,K11,B11,B8K7=FORMULATEXT(K8)
K8:N8K8=TEXTSPLIT(K5," , ")
B9:I9B9=TEXTSPLIT(B5,"///")
B12:I12B12=ATEXTSPLIT(B5,"///")
K12:N12,K19:N19K12=ISNUMBER(K8#)
K15:N15K15=ATEXTSPLIT(K5," , ")
B16:G16B16=TEXTSPLIT(B5,"///",,1)
B19:G19B19=ATEXTSPLIT(B5,"///",1)
Dynamic array formulas.
 

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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