ATEXTJOIN

=ATEXTJOIN(ar,a,s,d)
ar
array
a
leading string array, omitted or null string means no leading string
s
start index ,omitted, 0 or 1 means start index 1
d
delimiter string array, omitted or null string means no delimiter, can handle more characters
ATEXTJOIN appends horizontally each row of an array, adds leading string or not, we can set start index, with delimiter or not.
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
ATEXTJOIN !! recursive !! appends horizontally each row of an array, adds leading string or not, we can set start index, with delimiter or not.
Excel Formula: 
=LAMBDA(ar,a,s,d,
    LET(c,COLUMNS(ar),
       i,MIN(c+1,MAX(1,s)),
       ai,INDEX(ar,,i),
       IF(i=c+1,a,ATEXTJOIN(ar,a&IF(ai="","",ai&IF(i=c,"",d)),i+1,d))
    )
)
LAMBDA 5.0.xlsx
ABCDEFGHI
1prod agd23120red10Steveno leading string,start 0,no delim
2prod brf45430green15Johnprod agd23120red10Steve
3prod cet7100white45Davidprod brf45430green15John
4prod cet7100white45David
5
6no leading string,start 0,dble delim
7prod a//gd23//120//red//10//Steve
8prod b//rf45//430//green//15//John
9prod c//et7//100//white//45//David
10
11leading string,start index 3,delim
12tbl 1 - 120|red|10|Steve
13tbl 1 - 430|green|15|John
14tbl 1 - 100|white|45|David
15
16leading string as array
171-prod a/gd23/120/red/10/Steve
182-prod b/rf45/430/green/15/John
193-prod c/et7/100/white/45/David
20
21leading string with formula
22tbl 11 -prod a/gd23/120/red/10/Steve
23tbl 12 -prod b/rf45/430/green/15/John
24tbl 13 -prod c/et7/100/white/45/David
25
ATEXTJOIN post
Cell Formulas
RangeFormula
H2:H4H2=ATEXTJOIN(A1:F3,,,)
H7:H9H7=ATEXTJOIN(A1:F3,,,"//")
H12:H14H12=ATEXTJOIN(A1:F3,"tbl 1 - ",3,"|")
H17:H19H17=ATEXTJOIN(A1:F3,{"1-";"2-";"3-"},,"/")
H22:H24H22=LET(a,A1:F3,d,"-",ai,"tbl 1"&SEQUENCE(ROWS(a))&" -",ATEXTJOIN(a,ai,,"/"))
Dynamic array formulas.
 
Last edited by a moderator:
Upvote 0
Brand new function, same name. Another example of how easy, new functions (BYROW), can replace a recursive function functionality.
ATEXTJOIN(a,[dl],[ea])=LAMBDA(a,[dl],[ea],LET(d,IF(ISOMITTED(dl),",",dl),e,IF(ea,0,1),BYROW(a,LAMBDA(a,TEXTJOIN(d,e,a)))))
Excel Formula: 
=LAMBDA(a,[dl],[ea],LET(d,IF(ISOMITTED(dl),",",dl),e,IF(ea,0,1),BYROW(a,LAMBDA(a,TEXTJOIN(d,e,a)))))
]
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1blank/null stringarguments:
2space[dl]: delimiter; if omitted dl="," ,if dl="" no delimiter (similar CONCAT)
3[ea]: empty argument; if 0 or omitted ea=0=not include empty (ignore empty),ea<>0 include empty
4
5
6dl,ea,omitteddl,omitted(dl=","),ea,1dl,"",ea,omitteddl,"",ea,1
7sample=ATEXTJOIN(A8:E9)=ATEXTJOIN(A8:E9,,1)=ATEXTJOIN(A8:E9,"")=ATEXTJOIN(A8:E9,"",1)
8ac da,c, ,da,,c, ,dac dsameac d
912 41,2, ,41,2, ,,412 4result12 4
10
11dl,"/",ea,omitteddl,"/",ea,1
12=LEN(A8:E9)=ATEXTJOIN(A8:E9,"/")=ATEXTJOIN(A8:E9,"/",1)
1310111a/c/ /da//c/ /d
14111011/2/ /41/2/ //4
15check
16
new ATJ 1
Cell Formulas
RangeFormula
G7,G12,L12,A12,R7,W7,L7G7=FORMULATEXT(G8)
G8:G9G8=ATEXTJOIN(A8:E9)
L8:L9L8=ATEXTJOIN(A8:E9,,1)
R8:R9R8=ATEXTJOIN(A8:E9,"")
W8:W9W8=ATEXTJOIN(A8:E9,"",1)
A13:E14A13=LEN(A8:E9)
G13:G14G13=ATEXTJOIN(A8:E9,"/")
L13:L14L13=ATEXTJOIN(A8:E9,"/",1)
Dynamic array formulas.
 
Functionality of combo ATEXTJOIN/ATEXTSPLIT with constant array as delimiter ({",","-"})
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNO
1ATEXTJOIN/ATEXTSPLIT combo, with delimiter as constant array
2
3dl,{",","-"}, ea,omitteddl,"-",ea,omitted
4sample=ATEXTJOIN(A5:G6,{",","-"})=ATEXTSPLIT(I5#,"-")
5ab12cda,b-1,2-c,da,b1,2c,d
6ef34ghe,f-3,4-g,he,f3,4g,h
7
new ATEXTSPLIT post 2
Cell Formulas
RangeFormula
I4,K4I4=FORMULATEXT(I5)
I5:I6I5=ATEXTJOIN(A5:G6,{",","-"})
K5:M6K5=ATEXTSPLIT(I5#,"-")
Dynamic array formulas.
 
My opinion was always, whatever TEXTJOIN joints, ATEXTSPLIT has to split.
Now, with the new combo ATEXTJOIN/ATEXTSPLIT, whatever ATEXTJOIN joins, ATEXTSPLIT has to split.
If the combo is consistent in how they handle the tricky null strings, spaces, the combo it's reliable.
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT
1TEXTJOIN/new ATEXTJOIN/new ATEXTSPLIT functionality - include empty blank/null string
2space
3a&" "" "&cdl,omitted,ea,1dl,omitted (dl=",") ; ea,1 (include empty)dl,ea,omitted (ignore empty)
4sample⬇⬇=TEXTJOIN(",",0,A5:K5)=ATEXTJOIN(A5:K6,,1)=ATEXTSPLIT(V5#,,1)=ATEXTSPLIT(V5#)
5  a cd ,, ,a ,, ,, c,d, ,,, ,a ,, ,, c,d, ,  a cd a cd
6 1 23 ,,,1, ,,,2,3,, ,,,1, ,,,2,3,, 1 23 1 23
7=TEXTJOIN(",",0,A6:K6)
8Check
9=LEN(A5:K6)=LEN(N5:N6)=LEN(V5#)=LEN(Z5#)=LEN(AL5#)
1000120102110181800120102110121211
1110011001101161610011001101111111
12
13TEXTJOIN/new ATEXTJOIN/new ATEXTSPLIT functionality - ignore emptyblank/null string
14space
15a&" "" "&cdl,ea,omitteddl,ea,omitted (ignore empty)dl,omitted (dl=",") ; ea,1 (include empty)
16sample⬇⬇=TEXTJOIN(",",,A17:K17)=ATEXTJOIN(A17:K18)=ATEXTSPLIT(V17#)=ATEXTSPLIT(V17#,,1)
17  a cd ,a , , c,d, ,a , , c,d, a cd same a cd
18 1 23 ,1, ,2,3, ,1, ,2,3, 1 23 results 1 23
19=TEXTJOIN(",",,A18:K18)
20Check
21=LEN(A17:K18)=LEN(N17:N18)=LEN(V17#)=LEN(Z17#)=LEN(AH17#)
22001201021101313121211121211
23100110011011111111111111111
24
25
26functions
27new ATEXTJOIN
28new ATEXTSPLIT
29
new ATJ 2
Cell Formulas
RangeFormula
N4,V21,AH21,Z21,N21,A21,AH16,V16,Z16,N16,V9,AL9,Z9,N9,A9,AL4,V4,Z4N4=FORMULATEXT(N5)
B5,B17B5=""
V5:V6V5=ATEXTJOIN(A5:K6,,1)
Z5:AJ6Z5=ATEXTSPLIT(V5#,,1)
AL5:AQ6AL5=ATEXTSPLIT(V5#)
N5:N6N5=TEXTJOIN(",",0,A5:K5)
N7,N19N7=FORMULATEXT(N6)
A10:K11,A22:K23A10=LEN(A5:K6)
N10:N11,N22:N23N10=LEN(N5:N6)
V10:V11,AH22:AM23,Z22:AE23,V22:V23,AL10:AQ11,Z10:AJ11V10=LEN(V5#)
V17:V18V17=ATEXTJOIN(A17:K18)
Z17:AE18Z17=ATEXTSPLIT(V17#)
AH17:AM18AH17=ATEXTSPLIT(V17#,,1)
N17:N18N17=TEXTJOIN(",",,A17:K17)
Dynamic array formulas.
 
Playing with the versatility of various functions that we have so far. Transforming data in a proper data set.
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOP
1sampleChallenge: Single cell formula to transform sample into this:
2
3Prod 1Prod 2Prod 3Prod 4Report2020/2021
420202021202020212020202120202021MonthProd 1Prod 2Prod 3Prod 4
5Jan23.416.218.321.131.714.519.817.9Jan23.4/16.218.3/21.131.7/14.519.8/17.9
6Feb18.713.523.211.914.615.318.325.6Feb18.7/13.523.2/11.914.6/15.318.3/25.6
7Mar30.224.733.324.835.129.514.819.1Mar30.2/24.733.3/24.835.1/29.514.8/19.1
8
new ATJ 3
 
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQR
1sample
2Prod 1Prod 2Prod 3Prod 4step1step 2
320202021202020212020202120202021=ATEXTJOIN(B4:I6,{"/","-"})=ATEXTSPLIT(K4#,"-")
4Jan23.416.218.321.131.714.519.817.923.4/16.2-18.3/21.1-31.7/14.5-19.8/17.923.4/16.218.3/21.131.7/14.519.8/17.9
5Feb18.713.523.211.914.615.318.325.618.7/13.5-23.2/11.9-14.6/15.3-18.3/25.618.7/13.523.2/11.914.6/15.318.3/25.6
6Mar30.224.733.324.835.129.514.819.130.2/24.7-33.3/24.8-35.1/29.5-14.8/19.130.2/24.733.3/24.835.1/29.514.8/19.1
7step 3
8=AREPORT(N4#,A4:A6,AVCLEAN(B2:H2),,,"Month")
9MonthProd 1Prod 2Prod 3Prod 4
10Jan23.4/16.218.3/21.131.7/14.519.8/17.9
11functions on minisheetFeb18.7/13.523.2/11.914.6/15.318.3/25.6
12new ATEXTJOINMar30.2/24.733.3/24.835.1/29.514.8/19.1
13new ATEXTSPLITstep 4step 5
14AREPORT=ATEXTJOIN(B3:I3,{"/",","})=ATEXTSPLIT(K15)
15AVCLEAN (3rd post)2020/2021,2020/2021,2020/2021,2020/20212020/20212020/20212020/20212020/2021
16AFILLstep 6step 7
17AOVERLAP=ARESIZE(N15,1,1)=AFILL({"Report",""},N18)
18ARESIZE2020/2021Report2020/2021
19
20step 8
21=AOVERLAP(M9#,P18#,-1,1)
22 Report2020/2021
23MonthProd 1Prod 2Prod 3Prod 4
24Jan23.4/16.218.3/21.131.7/14.519.8/17.9
25Feb18.7/13.523.2/11.914.6/15.318.3/25.6
26all 8 steps in a single cell dynamic formula :Mar30.2/24.733.3/24.835.1/29.514.8/19.1
27
28=AOVERLAP(AREPORT(ATEXTSPLIT(ATEXTJOIN(B4:I6,{"/","-"}),"-"),A4:A6,AVCLEAN(B2:H2),,,"Month"),AFILL({"Report",""},ARESIZE(ATEXTSPLIT(ATEXTJOIN(B3:I3,{"/",","})),1,1)),-1,1)
29
30 Report2020/2021
31MonthProd 1Prod 2Prod 3Prod 4
32Jan23.4/16.218.3/21.131.7/14.519.8/17.9
33Feb18.7/13.523.2/11.914.6/15.318.3/25.6
34Mar30.2/24.733.3/24.835.1/29.514.8/19.1
35
new ATJ 4
Cell Formulas
RangeFormula
N3,M21,N17,P17,K14,N14,M8,K3N3=FORMULATEXT(N4)
K4:K6K4=ATEXTJOIN(B4:I6,{"/","-"})
N4:Q6N4=ATEXTSPLIT(K4#,"-")
M9:Q12M9=AREPORT(N4#,A4:A6,AVCLEAN(B2:H2),,,"Month")
K15K15=ATEXTJOIN(B3:I3,{"/",","})
N15:Q15N15=ATEXTSPLIT(K15)
N18N18=ARESIZE(N15,1,1)
P18:Q18P18=AFILL({"Report",""},N18)
M22:Q26M22=AOVERLAP(M9#,P18#,-1,1)
A28A28=FORMULATEXT(M30)
M30:Q34M30=AOVERLAP(AREPORT(ATEXTSPLIT(ATEXTJOIN(B4:I6,{"/","-"}),"-"),A4:A6,AVCLEAN(B2:H2),,,"Month"),AFILL({"Report",""},ARESIZE(ATEXTSPLIT(ATEXTJOIN(B3:I3,{"/",","})),1,1)),-1,1)
Dynamic array formulas.
 
Backwards challenge, transform previous result into initial form design using a single cell full dynamic formula.
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQR
1sampleBackward challenge: Single cell formula to transform sample into this:
2
3Report2020/2021Prod 1Prod 2Prod 3Prod 4
4MonthProd 1Prod 2Prod 3Prod 420202021202020212020202120202021
5Jan23.4/16.218.3/21.131.7/14.519.8/17.9Jan23.416.218.321.131.714.519.817.9
6Feb18.7/13.523.2/11.914.6/15.318.3/25.6Feb18.713.523.211.914.615.318.325.6
7Mar30.2/24.733.3/24.835.1/29.514.8/19.1Mar30.224.733.324.835.129.514.819.1
8
9step 1step 2
10=ATEXTSPLIT(B5:E7,"/")=ARESIZE(D11#,ROWS(B5:E7),COLUMNS(D11#)*COLUMNS(B5:E7))
1123.416.223.416.218.321.131.714.519.817.9
1218.321.118.713.523.211.914.615.318.325.6
1331.714.530.224.733.324.835.129.514.819.1
1419.817.9step 3step 3'
1518.713.5=ATEXTSPLIT(C3,"/")=COLUMNS(G16#)
1623.211.9202020212
1714.615.3
1818.325.6step 4
1930.224.7=ATEXTSPLIT(ATEXTJOIN(B4:E4,REPT(",.",K16-1)&","))
2033.324.8Prod 1.Prod 2.Prod 3.Prod 4
2135.129.5
2214.819.1step 5
23=INDEX(G16#,MOD(SEQUENCE(,COLUMNS(G11#))-1,2)+1)
24functions minisheet20202021202020212020202120202021
25new ATEXTSPLIT
26new ATEXTJOINstep 6
27AREPORT=AREPORT(G11#,G5:G7,G24#)
28ARESIZE 20202021202020212020202120202021
29APP2VJan23.416.218.321.131.714.519.817.9
30Feb18.713.523.211.914.615.318.325.6
31Mar30.224.733.324.835.129.514.819.1
32
33step 7
34=APP2V(G20#,G28#,,-1)
35 Prod 1.Prod 2.Prod 3.Prod 4
3620202021202020212020202120202021
37Jan23.416.218.321.131.714.519.817.9
38Feb18.713.523.211.914.615.318.325.6
39Mar30.224.733.324.835.129.514.819.1
40single cell dynamic LET
41 =LET(ma,B5:E7,p,B4:E4,y,C3,m,A5:A7,ts,ATEXTSPLIT(ma,"/"),nm,ARESIZE(ts,ROWS(ma),COLUMNS(ts)*COLUMNS(ma)),ys,ATEXTSPLIT(y,"/"),cy,COLUMNS(ys), pa,ATEXTSPLIT(ATEXTJOIN(p,REPT(",.",cy-1)&",")),ya,INDEX(ys,MOD(SEQUENCE(,COLUMNS(nm))-1,cy)+1),r,AREPORT(nm,m,ya),APP2V(pa,r,,-1))
42only 4 variables,initial array elements
43 Prod 1.Prod 2.Prod 3.Prod 4
4420202021202020212020202120202021
45Jan23.416.218.321.131.714.519.817.9
46Feb18.713.523.211.914.615.318.325.6
47Mar30.224.733.324.835.129.514.819.1
48
new ATJ 5
Cell Formulas
RangeFormula
D10,G10,G34,G27,G23,G19,K15,G15D10=FORMULATEXT(D11)
D11:E22D11=ATEXTSPLIT(B5:E7,"/")
G11:N13G11=ARESIZE(D11#,ROWS(B5:E7),COLUMNS(D11#)*COLUMNS(B5:E7))
G16:H16G16=ATEXTSPLIT(C3,"/")
K16K16=COLUMNS(G16#)
G20:M20G20=ATEXTSPLIT(ATEXTJOIN(B4:E4,REPT(",.",K16-1)&","))
G24:N24G24=INDEX(G16#,MOD(SEQUENCE(,COLUMNS(G11#))-1,2)+1)
G28:O31G28=AREPORT(G11#,G5:G7,G24#)
G35:O39G35=APP2V(G20#,G28#,,-1)
G43:O47G43=LET(ma,B5:E7,p,B4:E4,y,C3,m,A5:A7,ts,ATEXTSPLIT(ma,"/"),nm,ARESIZE(ts,ROWS(ma),COLUMNS(ts)*COLUMNS(ma)),ys,ATEXTSPLIT(y,"/"),cy,COLUMNS(ys),pa,ATEXTSPLIT(ATEXTJOIN(p,REPT(",.",cy-1)&",")),ya,INDEX(ys,MOD(SEQUENCE(,COLUMNS(nm))-1,cy)+1),r,AREPORT(nm,m,ya),APP2V(pa,r,,-1))
Dynamic array formulas.
 
Proof of how same formula updates dynamically, (no refresh ? ) for an expanded data set in all directions, one more year, one more product, one more month.
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1expanded data setsame single cell formula
2
3Report2019/2020/2021 Prod 1..Prod 2..Prod 3..Prod 4..Prod 5
4MonthProd 1Prod 2Prod 3Prod 4Prod 5201920202021201920202021201920202021201920202021201920202021
5Jan23.4/16.2/21.318.3/21.1/42.631.7/14.5/25.719.8/17.9/29.136.8/29.8/12.3Jan23.416.221.318.321.142.631.714.525.719.817.929.136.829.812.3
6Feb18.7/13.5/19.823.2/11.9/34.714.6/15.3/33.318.3/25.6/30.617.2/19.4/32.8Feb18.713.519.823.211.934.714.615.333.318.325.630.617.219.432.8
7Mar30.2/24.7/31.533.3/24.8/16.735.1/29.5/44.414.8/19.1/42.227.3/11.8/39.1Mar30.224.731.533.324.816.735.129.544.414.819.142.227.311.839.1
8Apr15.8/22.3/48.729.6/18.3/38.722.6/41.3/30.826.4/12.8/31.427.6/32.1/19.7Apr15.822.348.729.618.338.722.641.330.826.412.831.427.632.119.7
9
new ATJ 6
Cell Formulas
RangeFormula
H3:W8H3=LET(ma,B5:F8,p,B4:F4,y,C3,m,A5:A8,ts,ATEXTSPLIT(ma,"/"),nm,ARESIZE(ts,ROWS(ma),COLUMNS(ts)*COLUMNS(ma)),ys,ATEXTSPLIT(y,"/"),cy,COLUMNS(ys),pa,ATEXTSPLIT(ATEXTJOIN(p,REPT(",.",cy-1)&",")),ya,INDEX(ys,MOD(SEQUENCE(,COLUMNS(nm))-1,cy)+1),r,AREPORT(nm,m,ya),APP2V(pa,r,,-1))
Dynamic array formulas.
 
LAMBDA 1.1.2.xlsx
ABCDEFGHIJKLMNO
1Inserting a cell "gap" distribution of null strings to an array 1D
2Was needed in previous post formula to get from:
3Prod 1Prod 2Prod 3Prod 4Prod 5
4to
5Prod 1Prod 2Prod 3Prod 4Prod 5
6(2 cell gap was calculated as nr. of years-1 ,3-1=2)
7
8I have used :
9=ATEXTSPLIT(ATEXTJOIN(A3:E3,REPT(",.",2)&","))
10Prod 1..Prod 2..Prod 3..Prod 4..Prod 5
11with the small compromise of tiny dots that can be eliminated with a extra SUBSTITUTE (did not use it)
12
13Found a cleaner way, using include empty functionality with empty argument value ea=1,(dl omitted means dl=",")
14=ATEXTSPLIT(ATEXTJOIN(A3:E3,REPT(",",2)&","),,1)
15Prod 1Prod 2Prod 3Prod 4Prod 5
16
17For fun let's do it for a column, dbl transpose
18
19=TRANSPOSE(ATEXTSPLIT(ATEXTJOIN(TRANSPOSE(B20:B25),REPT(",",3)&","),,1))
20Prod 1Prod 1
21Prod 2or=ASTACK(ATEXTSPLIT(ATEXTJOIN(AUNSTACK(B20:B25),REPT(",",3)&","),,1))
22Prod 3Prod 1
23Prod 4back to initial
24Prod 5Prod 2=AHCLEAN(F22#)
25Prod 6Prod 1
26Prod 2Prod 2
27Prod 3
28Prod 3Prod 4
29Prod 5
30Prod 3Prod 6
31
32Prod 4
33
34Prod 4
35functions
36Prod 5ASTACK
37AUNSTACK
38Prod 5AHCLEAN
39
40Prod 6
41
42Prod 6
43
new ATJ ATS 7
Cell Formulas
RangeFormula
A9,H24,F21,D19,A14A9=FORMULATEXT(A10)
A10:M10A10=ATEXTSPLIT(ATEXTJOIN(A3:E3,REPT(",.",2)&","))
A15:M15A15=ATEXTSPLIT(ATEXTJOIN(A3:E3,REPT(",",2)&","),,1)
D20:D40D20=TRANSPOSE(ATEXTSPLIT(ATEXTJOIN(TRANSPOSE(B20:B25),REPT(",",3)&","),,1))
F22:F42F22=ASTACK(ATEXTSPLIT(ATEXTJOIN(AUNSTACK(B20:B25),REPT(",",3)&","),,1))
H25:H30H25=AHCLEAN(F22#)
Dynamic array formulas.
 
You must log in or register to reply here.

Forum statistics

Threads
1,224,812
Messages
6,181,089
Members
453,021
Latest member
Justyna P

Share this page

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