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
837
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
Hey Xlambda,
Care to explain why you excluded FITLERXML?
That was such nice and compact formula, it's my favorite...
 
Hi Geert !! Thanks for the great question!! ?✌
First, "exclude" is a little bit too harsh of a word. I am nobody to exclude anything. ? As you know, better than anyone, I was the first to introduced FILTERXML to the "team" back in 2020, 1st of May, on
one of Mike's YT video Excel Magic Trick 1664 . If you check, only after that date , Mike , Bill , Abiola, and others, did exquisite explanatory videos on it. Anyhow, I did not invent it, and nobody/everybody owns it. (Myself, learn it from a web site, dnt remember which one, long before dynamic arrays) Cutting the long story short, I Love FILTERXML, of course I do, it was a big hit.? Can be made to handle arrays, can append arrays also, but only small data sets. Though, the main reason I choose not to use it is because I've noticed that in real life, when I was trying to implement it in solving other people's tasks, they got a reluctant attitude. Don't know why, looks too cryptic to them, too far of what they believe Excel is. Simple as that.
Now, when it comes to splitting, I do care about accuracy, and I have considered that an equally powerful function, as an opposite of TEXTJOIN , should contain the ignore empty/include empty argument.
And ATEXTSPLIT has it. I know also that SPLITONDELIMITER can be improved and refined, but since that was the only one I've got to compare ATEXTSPLIT with, here are some examples in the minisheet bellow. Check them out and share your opinion with us. In my opinion, not only FILTERXML function has to be acurate when comes to splitting, any other functions, of any shape size or form should be.✌
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1-about ATEXTSPLIT
21. A "TEXTSPLIT"(ATEXTSPLIT) formula should be kind of TEXTJOIN opposite, whatever and however TEXTJOIN joins, ATEXTSPLIT should split back as it was
32. If TEXTJOIN has an "ignore empty/include empty" argument, ATEXTSPLIT should have one also.
43. ATEXTJOIN can handle vertical arrays
5split on both scenariosignore emptyignore empty
6=SPLITONDELIMITER(A7,",")=ATEXTSPLIT(A7,",",)no empty string,only a space)=SPLITONDELIMITER(M7,",")=ATEXTSPLIT(M7,",",)
7a,b,,caabca,b, ,caab c
8(,, null string btwn delimiters)binclude empty(, , space btwn delimiters)binclude empty
9#VALUE!=ATEXTSPLIT(A7,",",1)#VALUE!=ATEXTSPLIT(M7,",",1)
10cabccab c
11empty(null)string11011111
12space=LEN(F10#)=LEN(R10#)
13
14
15ignore emptyignore empty
16=SPLITONDELIMITER(A17,",")=ATEXTSPLIT(A17,",",)array=SPLITONDELIMITER(M17:M18,",")=ATEXTSPLIT(M17:M18,",",)
17a,, ,b, caa b ca,b,,caabc
18this is: a,empty,space,b,space&d#VALUE!1112d,e, , fdde f
19#VALUE!=LEN(F17#)=LEN(R17#)1110
20b1112
21cinclude emptyinclude empty
22(no way to diferentiate between errors,=ATEXTSPLIT(A17,",",1)=ATEXTSPLIT(M17:M18,",",1)
23which one is space, which one is empty)a b cabc
24(c lost it's leading space)10112de f
25LEN(D21)=1=LEN(F23#)=LEN(R23#)1101
26(c has the leading space)1112
27(f has a leading space)
28
FXML
Cell Formulas
RangeFormula
D6,F6,R22,F22,O16,R16,D16,F16,R9,F9,P6,R6D6=FORMULATEXT(D7)
D7:D10,D17:D21,P7:P10D7=SPLITONDELIMITER(A7,",")
F7:H7,F17:I17,R7:U7F7=ATEXTSPLIT(A7,",",)
F10:I10,R10:U10F10=ATEXTSPLIT(A7,",",1)
F11:I11,F24:J24,F18:I18,R11:U11F11=LEN(F10#)
F12,F25,F19,R12F12=FORMULATEXT(F11)
O17:O18O17=SPLITONDELIMITER(M17:M18,",")
R17:U18R17=ATEXTSPLIT(M17:M18,",",)
Q19,Q25Q19=FORMULATEXT(R19)
R19:U20,R25:U26R19=LEN(R17#)
R23:U24R23=ATEXTSPLIT(M17:M18,",",1)
F23:J23F23=ATEXTSPLIT(A17,",",1)
Dynamic array formulas.
 
Speaking of accuracy, noticed that I have set a wrong color in cell H10 ?✌ Rest is fine. Choose to correct it.?
LAMBDA 7.0.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
1-about ATEXTSPLIT
21. A "TEXTSPLIT"(ATEXTSPLIT) formula should be kind of TEXTJOIN opposite, whatever and however TEXTJOIN joins, ATEXTSPLIT should split back as it was
32. If TEXTJOIN has an "ignore empty/include empty" argument, ATEXTSPLIT should have one also.
43. ATEXTJOIN can handle vertical arrays
5split on both scenariosignore emptyignore empty
6=SPLITONDELIMITER(A7,",")=ATEXTSPLIT(A7,",",)no empty string,only a space)=SPLITONDELIMITER(M7,",")=ATEXTSPLIT(M7,",",)
7a,b,,caabca,b, ,caab c
8(,, null string btwn delimiters)binclude empty(, , space btwn delimiters)binclude empty
9#VALUE!=ATEXTSPLIT(A7,",",1)#VALUE!=ATEXTSPLIT(M7,",",1)
10cabccab c
11empty(null)string11011111
12space=LEN(F10#)=LEN(R10#)
13
14
15ignore emptyignore empty
16=SPLITONDELIMITER(A17,",")=ATEXTSPLIT(A17,",",)array=SPLITONDELIMITER(M17:M18,",")=ATEXTSPLIT(M17:M18,",",)
17a,, ,b, caa b ca,b,,caabc
18this is: a,empty,space,b,space&d#VALUE!1112d,e, , fdde f
19#VALUE!=LEN(F17#)=LEN(R17#)1110
20b1112
21cinclude emptyinclude empty
22(no way to diferentiate between errors,=ATEXTSPLIT(A17,",",1)=ATEXTSPLIT(M17:M18,",",1)
23which one is space, which one is empty)a b cabc
24(c lost it's leading space)10112de f
25LEN(D21)=1=LEN(F23#)=LEN(R23#)1101
26(c has the leading space)1112
27(f has a leading space)
28
FXML
Cell Formulas
RangeFormula
D6,F6,R22,F22,O16,R16,D16,F16,R9,F9,P6,R6D6=FORMULATEXT(D7)
D7:D10,D17:D21,P7:P10D7=SPLITONDELIMITER(A7,",")
F7:H7,F17:I17,R7:U7F7=ATEXTSPLIT(A7,",",)
F10:I10,R10:U10F10=ATEXTSPLIT(A7,",",1)
F11:I11,F24:J24,F18:I18,R11:U11F11=LEN(F10#)
F12,F25,F19,R12F12=FORMULATEXT(F11)
O17:O18O17=SPLITONDELIMITER(M17:M18,",")
R17:U18R17=ATEXTSPLIT(M17:M18,",",)
Q19,Q25Q19=FORMULATEXT(R19)
R19:U20,R25:U26R19=LEN(R17#)
R23:U24R23=ATEXTSPLIT(M17:M18,",",1)
F23:J23F23=ATEXTSPLIT(A17,",",1)
Dynamic array formulas.
 
The first time I saw FILTERXLM for split text was from Lori Post on 10 Jan 2017


=TEXTJOIN(",",,FILTERXML("<x><a>"&SUBSTITUTE(A1,",","</a><a>")&"</a></x>","//a[not(. = preceding::a/.)]"))

XPath already include advanced syntax [not(. = preceding::a/.)]
so the first use for split text must be way before 2017 since the FILTERXML launch in 2013

Another ATEXTSPLIT

Excel Formula:
=LAMBDA(ar,dl,ea,LET(l,MAX(LEN(ar)),ds,REPT(" ",l),c,SUBSTITUTE(SUBSTITUTE(ar," ","_"),dl,ds),
df,IF(ea,c,SUBSTITUTE(TRIM(c)," ",ds)),SUBSTITUTE(TRIM(MID(df,SEQUENCE(,MAX(LEN(SUBSTITUTE(df,ds,ds&0))-LEN(df)+1),,l),l)),"_"," "))
)(M17:M18,",",0)
 
HI Bo_Ry. Hello to you too!! What a great honor to have the great master profesional, descended to our poor, humble, amateur playground. ?✌✌.
FILTERXML, back in 2013, wow, in 2013 I did not knew what on/off means. ? . So, you knew it since 2017, but when did you do the first video about it on your YT channel ? You have your own YT channel as far as I know (yes , saw it, is linked in your signature)? I remember vaguely that you advertised the topic, saying that is not in English or something, this was still after 1st of May 2020, or you did it before that date? Does not matter , the idea was that if we check the frequency distribution of FILTERXML videos (as in split/append versatility) is very 0 asymptotical left skewed to may 2020 date, which was fabulous at that time. Abiola did it's first YT about it at end of May, though became viral after MIke and Bill addressed it in their YT channels.
Now, the formula, superb, I like that you followed and understood the touch of my interpretation of the accuracy of what splitting benchmarks should be capable of, ignoring/including empty, space gaps, handles arrays..etc., everything's fine there. But gap method??? I know is ok, used it myself many times, also FXML . Enjoyed even a completely new approach with ASPLIT.
Why I said, in the description, that I was inspired by MrExcel video? Because I have tried to imagine what an excel TEXTSPLIT will be capable of and I realized: "What TEXTJOIN joins, an equally powerful TEXTSPLIT should be able to split, back to what it was". This has resetead all my other former perspectives. So, if TEXTJOIN can join 32767 characters, we have to be able to split them back.
Imagine , a "starting" array, 117rows x 35colummns=4095cells containing "1234567" (7chars) on each cell like in ar=REPT("1234567",SEQUENCE(117,35)^0).
Then, a=TEXTJOIN(",",,a). We get a string of LEN(a)=32759
Now, if we call ts=ATEXTSPLIT(a,",",) followed by ASTACK(ts,35) bam , back to starting array, works fine, mission accomplished!!
By comparison, the gap method, following the same reasoning, let's consider an array only 8 x 8 , same string on each cell "1234567" ar=REPT("1234567",SEQUENCE(8,8)^0)
Next, a=TEXTJOIN(",",,a) We get a string of only LEN(a)=511
If we call your formula, let's name it GAPSPLIT(a,",",) works fine, splits, but, if we add anywhere, on string a, only a single character, anywhere, the formula fails. So, GAPSPLIT limit is 64 words of 7char length each+1 (not considering the single char delimiter). Not too much. That's why, in this context only, of performance, I did not use it. If we reach Excel's limitations, it's MS problem to improve it, with time, if we reach limitations because of our design, it's only by our choice.
Another minor thing, for "robustness", I have addressed error debugging for special unusual characters that we have to use as SUBSTITUTE arguments.
If we call for example GAPSPLIT("a_ _ b","_",) we get erroneous result. Choice again.
Conclusion: Based on choice awareness, everybody is free to choose whatever formula suits them best.?✌
PS. Guys, please pardon my long talk, just come off from the first "night" party in the last 15 months . "Relaxing" times are back!! "Excel" times are over!! ?
 
Thanks, I'm not anywhere close to a master. I'm just a newbie that having fun with EXCEL.
In 2017 I only know VLOOKUP. o_O
I start to interesting in EXCEL in 2018 after watching CTRL+SHIFT+ENTER Mike Excelisfun's playlist.

The real first time I see FILTERXML is from XOR LX on Apr 24, 2020. He is a real great master.

Then I trace back and found that Lori reply this on June 2, 2017

Then trace back to Lori and found the oldest one that I can google out, on Jan 10, 2017

Chando also has a Video on this FILTERXML trick on Sep 12, 2019. He got the idea from his Forum which also traces back to Lori's post.

From this, all the credit should go to Lori.
 
Few more "H·" channel iterations and will get to Bill Gates. ?✌
 
Tweaked functionality of same function with "can be omitted" arguments, and splitting 2D arrays, not only 1D vertical arrays.
Excel Formula:
=LAMBDA(ar,[dl],[ea],
    LET(h,"º",d,"ª",l,IF(dl="",",",dl),r,AFLAT(ar),ch,ISNUMBER(SEARCH(CHOOSE({1,2},h,d),r)),a,SUBSTITUTE(r,l,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 1.1.2.xlsx
ABCDEFGHIJKLMNOPQRST
1arguments new fuctionality (can be omitted)
2[dl]: delimiter, if "" or omitted dl=",", otherwise dl
3[ea]: empty argument, if 0 or omitted ea=0 ignore empty, otherwise ea=1 include empty
4
5blank/null string
6spacedl,ea,omitteddl,omittted,ea,1
7=ATEXTSPLIT(A8:A9)=ATEXTSPLIT(A8:A9,,1)
8a,,b,c, ,dabc dabc d
9,,1, ,2,3,4,1 2341 234
10
11=LEN(D8#)=LEN(J8#)
12check1111110111100
131111100111110
14
15dl,"/",ea,omitteddl,"/",ea,1
16=ATEXTSPLIT(A17:A18,"/")=ATEXTSPLIT(A17:A18,"/",1)
17a//b/C/ /dabC dabC d
18//1/ /2/3/4/1 2341 234
19
20Now we can split 2D arrays not only 1D, and together with ARESIZE we can control the outcome
21
22=ATEXTSPLIT(A23:B24)=ARESIZE(D23#,2,6)
23a,b,c1,2,3abcabc123
24d,e,f4,5,6123def456
25def
26456ARESIZE
27
28Note: We can write a new lambda, if we want, to always keep the outcome of source array
29
30=LAMBDA(ar,[dl],[ea],LET(x,ATEXTSPLIT(ar,dl,ea),ARESIZE(x,ROWS(ar),COLUMNS(x)*COLUMNS(ar))))(A23:B24)
31abc123
32def456
33
new ATEXTSPLIT
Cell Formulas
RangeFormula
D7,B30,D22,I22,J16,D16,J11,D11,J7D7=FORMULATEXT(D8)
D8:H9D8=ATEXTSPLIT(A8:A9)
J8:Q9J8=ATEXTSPLIT(A8:A9,,1)
D12:H13,J12:Q13D12=LEN(D8#)
D17:H18D17=ATEXTSPLIT(A17:A18,"/")
J17:Q18J17=ATEXTSPLIT(A17:A18,"/",1)
D23:F26D23=ATEXTSPLIT(A23:B24)
I23:N24I23=ARESIZE(D23#,2,6)
B31:G32B31=LAMBDA(ar,[dl],[ea],LET(x,ATEXTSPLIT(ar,dl,ea),ARESIZE(x,ROWS(ar),COLUMNS(x)*COLUMNS(ar))))(A23:B24)
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.
 

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

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