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



## Xlambda (May 30, 2021)

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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1TEXTJOINATEXTSPLIT2empty ignoredempty not ignored2 charsempty ignored3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=04sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check5a4ca,4,,ca4ca4ca><6.8><ta6.8t1316d0.5d,,,0.5d0.5d0.5><2.4  w><2.4  w6007g hi 2k  l3 ng h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n<Ac4><gh>< ><<Ac4gh 4218space9empty ignoredea arg.=0ea arg.=1empty not ignored10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=111a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check12d,0.5d0.5d0.5a6.8t131013g h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n2.4  w060014<Ac4gh 421015Complex scenariospace followed by an empty string16empty ignoredempty not ignored17ea 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 chars20,,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 chars22, ,,x   4, ,, x   4  x   4 2324Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument25                            -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument26                       We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)2728=LEN(H19#)=LEN(N19#)291113311130303011130001011331413110413110321510001051003334- 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 used3536ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)37fixed with 4 pos.a bc/a  b/ bcda bca  b bcd44438/____ /ab d/   d/a   ab d   da   44439/xxxx/check:pattern is kept40ATEXTSPLIT postCell FormulasRangeFormulaH4,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.


----------



## GeertD (Jun 1, 2021)

Hey Xlambda,
Care to explain why you excluded FITLERXML?
That was such nice and compact formula, it's my favorite...


----------



## Xlambda (Jun 1, 2021)

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.xlsxABCDEFGHIJKLMNOPQRSTUVW1-about ATEXTSPLIT21. A "TEXTSPLIT"(ATEXTSPLIT) formula should be kind of TEXTJOIN opposite, whatever and however TEXTJOIN joins, ATEXTSPLIT should split back as it was32. If TEXTJOIN has an "ignore empty/include empty" argument, ATEXTSPLIT should have one also.43. ATEXTJOIN can handle vertical arrays5split on both scenariosignore emptyignore empty6=SPLITONDELIMITER(A7,",")=ATEXTSPLIT(A7,",",)no empty string,only a space)=SPLITONDELIMITER(M7,",")=ATEXTSPLIT(M7,",",)7a,b,,caabca,b, ,caab c8(,, null string btwn delimiters)binclude empty(, , space btwn delimiters)binclude empty9#VALUE!=ATEXTSPLIT(A7,",",1)#VALUE!=ATEXTSPLIT(M7,",",1)10cabccab c11empty(null)string1101111112space=LEN(F10#)=LEN(R10#)131415ignore emptyignore empty16=SPLITONDELIMITER(A17,",")=ATEXTSPLIT(A17,",",)array=SPLITONDELIMITER(M17:M18,",")=ATEXTSPLIT(M17:M18,",",)17a,, ,b, caa b ca,b,,caabc18this is: a,empty,space,b,space&d#VALUE!1112d,e, , fdde  f19#VALUE!=LEN(F17#)=LEN(R17#)111020b111221cinclude emptyinclude empty22(no way to diferentiate between errors,=ATEXTSPLIT(A17,",",1)=ATEXTSPLIT(M17:M18,",",1)23which one is space, which one is empty)a b cabc24(c lost it's leading space)10112de  f25LEN(D21)=1=LEN(F23#)=LEN(R23#)110126(c has the leading space)111227(f has a leading space)28FXMLCell FormulasRangeFormulaD6,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.


----------



## Xlambda (Jun 1, 2021)

Speaking of accuracy, noticed that I have set a wrong color in cell H10 ?✌ Rest is fine. Choose to correct it.?
LAMBDA 7.0.xlsxABCDEFGHIJKLMNOPQRSTUVW1-about ATEXTSPLIT21. A "TEXTSPLIT"(ATEXTSPLIT) formula should be kind of TEXTJOIN opposite, whatever and however TEXTJOIN joins, ATEXTSPLIT should split back as it was32. If TEXTJOIN has an "ignore empty/include empty" argument, ATEXTSPLIT should have one also.43. ATEXTJOIN can handle vertical arrays5split on both scenariosignore emptyignore empty6=SPLITONDELIMITER(A7,",")=ATEXTSPLIT(A7,",",)no empty string,only a space)=SPLITONDELIMITER(M7,",")=ATEXTSPLIT(M7,",",)7a,b,,caabca,b, ,caab c8(,, null string btwn delimiters)binclude empty(, , space btwn delimiters)binclude empty9#VALUE!=ATEXTSPLIT(A7,",",1)#VALUE!=ATEXTSPLIT(M7,",",1)10cabccab c11empty(null)string1101111112space=LEN(F10#)=LEN(R10#)131415ignore emptyignore empty16=SPLITONDELIMITER(A17,",")=ATEXTSPLIT(A17,",",)array=SPLITONDELIMITER(M17:M18,",")=ATEXTSPLIT(M17:M18,",",)17a,, ,b, caa b ca,b,,caabc18this is: a,empty,space,b,space&d#VALUE!1112d,e, , fdde  f19#VALUE!=LEN(F17#)=LEN(R17#)111020b111221cinclude emptyinclude empty22(no way to diferentiate between errors,=ATEXTSPLIT(A17,",",1)=ATEXTSPLIT(M17:M18,",",1)23which one is space, which one is empty)a b cabc24(c lost it's leading space)10112de  f25LEN(D21)=1=LEN(F23#)=LEN(R23#)110126(c has the leading space)111227(f has a leading space)28FXMLCell FormulasRangeFormulaD6,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.


----------



## Bo_Ry (Jun 4, 2021)

The first time I saw FILTERXLM for split text was from Lori Post on 10 Jan 2017






						CreatE A Unique Delimited String From a Delimited String – Excel Formula Method (by David Hager)
					

See:  https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/  Convert a delimited string like, for example a,b,c,c,d,a,e,r,h,h,t,o,x,a,b,c  (in A1)  to  a,b,c,d,e,r,h,t,o,x



					chandoo.org
				




=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


```
=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)
```


----------



## Xlambda (Jun 4, 2021)

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!! ?


----------



## Bo_Ry (Jun 5, 2021)

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. 
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.





						Extracting unique text from a row of data
					

Hi Experts    I have a row of data in say 10 columns, most of which contains duplicate values.  I want to be able to find the unique words in that particular row and return them in a single or multiple cells.  I attach an example of what I'm trying to do.  I'm not sure if Excel can help but that...



					www.excelforum.com
				




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





						Excelxor.com
					






					excelxor.com
				




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





						CreatE A Unique Delimited String From a Delimited String – Excel Formula Method (by David Hager)
					

See:  https://dhexcel1.wordpress.com/2017/01/03/creating-a-unique-delimited-string-from-a-delimited-string-excel-formula-method-by-david-hager/  Convert a delimited string like, for example a,b,c,c,d,a,e,r,h,h,t,o,x,a,b,c  (in A1)  to  a,b,c,d,e,r,h,t,o,x



					chandoo.org
				




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.








						#awesome trick - Extract word by position using FILTERXML() » Chandoo.org - Learn Excel, Power BI & Charting Online
					

This is CRAZY!!!. I stumbled on a weird use for FILTERXML() while reading a forum post earlier today. So I couldn't wait to test it. I am happy to share the results. Say you have some text (sentence / phrase / keyword etc.) in a cell and you want to extract the nth word. Unfortunately Excel...




					chandoo.org
				




From this, all the credit should go to Lori.


----------



## Xlambda (Jun 5, 2021)

Few more "H·" channel iterations and will get to Bill Gates. ?✌


----------



## Xlambda (Aug 27, 2021)

*Tweaked functionality of same function with "can be omitted" arguments, and splitting 2D arrays, not only 1D vertical arrays.*

```
=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.xlsxABCDEFGHIJKLMNOPQRST1arguments new fuctionality (can be omitted)2[dl]: delimiter, if "" or omitted dl=",", otherwise dl3[ea]: empty argument, if 0 or omitted ea=0 ignore empty, otherwise ea=1 include empty45blank/null string6spacedl,ea,omitteddl,omittted,ea,17=ATEXTSPLIT(A8:A9)=ATEXTSPLIT(A8:A9,,1)8a,,b,c, ,dabc dabc d9,,1, ,2,3,4,1 2341 2341011=LEN(D8#)=LEN(J8#)12check11111101111001311111001111101415dl,"/",ea,omitteddl,"/",ea,116=ATEXTSPLIT(A17:A18,"/")=ATEXTSPLIT(A17:A18,"/",1)17a//b/C/ /dabC dabC d18//1/ /2/3/4/1 2341 2341920Now we can split 2D arrays not only 1D, and together with ARESIZE we can control the outcome2122=ATEXTSPLIT(A23:B24)=ARESIZE(D23#,2,6)23a,b,c1,2,3abcabc12324d,e,f4,5,6123def45625def26456ARESIZE2728Note: We can write a new lambda, if we want, to always keep the outcome of source array2930=LAMBDA(ar,[dl],[ea],LET(x,ATEXTSPLIT(ar,dl,ea),ARESIZE(x,ROWS(ar),COLUMNS(x)*COLUMNS(ar))))(A23:B24)31abc12332def45633new ATEXTSPLITCell FormulasRangeFormulaD7,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.


----------



## Xlambda (Aug 27, 2021)

*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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT1TEXTJOIN/new ATEXTJOIN/new ATEXTSPLIT functionality - include empty blank/null string2space3a&" "" "&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)8Check9=LEN(A5:K6)=LEN(N5:N6)=LEN(V5#)=LEN(Z5#)=LEN(AL5#)100012010211018180012010211012121111100110011011616100110011011111111213TEXTJOIN/new ATEXTJOIN/new ATEXTSPLIT functionality - ignore emptyblank/null string14space15a&" "" "&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)20Check21=LEN(A17:K18)=LEN(N17:N18)=LEN(V17#)=LEN(Z17#)=LEN(AH17#)2200120102110131312121112121123100110011011111111111111111242526functions27new ATEXTJOIN28new ATEXTSPLIT29new ATJ 2Cell FormulasRangeFormulaN4,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.


----------



## Xlambda (May 30, 2021)

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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1TEXTJOINATEXTSPLIT2empty ignoredempty not ignored2 charsempty ignored3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=04sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check5a4ca,4,,ca4ca4ca><6.8><ta6.8t1316d0.5d,,,0.5d0.5d0.5><2.4  w><2.4  w6007g hi 2k  l3 ng h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n<Ac4><gh>< ><<Ac4gh 4218space9empty ignoredea arg.=0ea arg.=1empty not ignored10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=111a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check12d,0.5d0.5d0.5a6.8t131013g h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n2.4  w060014<Ac4gh 421015Complex scenariospace followed by an empty string16empty ignoredempty not ignored17ea 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 chars20,,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 chars22, ,,x   4, ,, x   4  x   4 2324Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument25                            -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument26                       We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)2728=LEN(H19#)=LEN(N19#)291113311130303011130001011331413110413110321510001051003334- 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 used3536ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)37fixed with 4 pos.a bc/a  b/ bcda bca  b bcd44438/____ /ab d/   d/a   ab d   da   44439/xxxx/check:pattern is kept40ATEXTSPLIT postCell FormulasRangeFormulaH4,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.


----------



## Xlambda (Aug 27, 2021)

*Playing with the versatility of various functions that we have so far. Transforming data in a proper data set.*
LAMBDA 1.1.2.xlsxABCDEFGHIJKLMNOP1sampleChallenge: Single cell formula to transform sample into this:23Prod 1Prod 2Prod 3Prod 4Report2020/2021420202021202020212020202120202021MonthProd 1Prod 2Prod 3Prod 45Jan23.416.218.321.131.714.519.817.9Jan23.4/16.218.3/21.131.7/14.519.8/17.96Feb18.713.523.211.914.615.318.325.6Feb18.7/13.523.2/11.914.6/15.318.3/25.67Mar30.224.733.324.835.129.514.819.1Mar30.2/24.733.3/24.835.1/29.514.8/19.18new ATJ 3


----------



## Xlambda (Aug 27, 2021)

LAMBDA 1.1.2.xlsxABCDEFGHIJKLMNOPQR1sample2Prod 1Prod 2Prod 3Prod 4step1step 2320202021202020212020202120202021=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.95Feb18.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.66Mar30.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.17step 38=AREPORT(N4#,A4:A6,AVCLEAN(B2:H2),,,"Month")9MonthProd 1Prod 2Prod 3Prod 410Jan23.4/16.218.3/21.131.7/14.519.8/17.911functions on minisheetFeb18.7/13.523.2/11.914.6/15.318.3/25.612new ATEXTJOINMar30.2/24.733.3/24.835.1/29.514.8/19.113new ATEXTSPLITstep 4step 514AREPORT=ATEXTJOIN(B3:I3,{"/",","})=ATEXTSPLIT(K15)15AVCLEAN (3rd post)2020/2021,2020/2021,2020/2021,2020/20212020/20212020/20212020/20212020/202116AFILLstep 6step 717AOVERLAP=ARESIZE(N15,1,1)=AFILL({"Report",""},N18)18ARESIZE2020/2021Report2020/20211920step 821=AOVERLAP(M9#,P18#,-1,1)22 Report2020/202123MonthProd 1Prod 2Prod 3Prod 424Jan23.4/16.218.3/21.131.7/14.519.8/17.925Feb18.7/13.523.2/11.914.6/15.318.3/25.626all 8 steps in a single cell dynamic formula :Mar30.2/24.733.3/24.835.1/29.514.8/19.12728=AOVERLAP(AREPORT(ATEXTSPLIT(ATEXTJOIN(B4:I6,{"/","-"}),"-"),A4:A6,AVCLEAN(B2:H2),,,"Month"),AFILL({"Report",""},ARESIZE(ATEXTSPLIT(ATEXTJOIN(B3:I3,{"/",","})),1,1)),-1,1)2930 Report2020/202131MonthProd 1Prod 2Prod 3Prod 432Jan23.4/16.218.3/21.131.7/14.519.8/17.933Feb18.7/13.523.2/11.914.6/15.318.3/25.634Mar30.2/24.733.3/24.835.1/29.514.8/19.135new ATJ 4Cell FormulasRangeFormulaN3,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.


----------



## Xlambda (Aug 28, 2021)

*Backwards challenge, transform previous result into initial form design using a single cell full dynamic formula.*
LAMBDA 1.1.2.xlsxABCDEFGHIJKLMNOPQR1sampleBackward challenge: Single cell formula to transform sample into this:23Report2020/2021Prod 1Prod 2Prod 3Prod 44MonthProd 1Prod 2Prod 3Prod 4202020212020202120202021202020215Jan23.4/16.218.3/21.131.7/14.519.8/17.9Jan23.416.218.321.131.714.519.817.96Feb18.7/13.523.2/11.914.6/15.318.3/25.6Feb18.713.523.211.914.615.318.325.67Mar30.2/24.733.3/24.835.1/29.514.8/19.1Mar30.224.733.324.835.129.514.819.189step 1step 210=ATEXTSPLIT(B5:E7,"/")=ARESIZE(D11#,ROWS(B5:E7),COLUMNS(D11#)*COLUMNS(B5:E7))1123.416.223.416.218.321.131.714.519.817.91218.321.118.713.523.211.914.615.318.325.61331.714.530.224.733.324.835.129.514.819.11419.817.9step 3step 3'1518.713.5=ATEXTSPLIT(C3,"/")=COLUMNS(G16#)1623.211.92020202121714.615.31818.325.6step 41930.224.7=ATEXTSPLIT(ATEXTJOIN(B4:E4,REPT(",.",K16-1)&","))2033.324.8Prod 1.Prod 2.Prod 3.Prod 42135.129.52214.819.1step 523=INDEX(G16#,MOD(SEQUENCE(,COLUMNS(G11#))-1,2)+1)24functions minisheet2020202120202021202020212020202125new ATEXTSPLIT26new ATEXTJOINstep 627AREPORT=AREPORT(G11#,G5:G7,G24#)28ARESIZE 2020202120202021202020212020202129APP2VJan23.416.218.321.131.714.519.817.930Feb18.713.523.211.914.615.318.325.631Mar30.224.733.324.835.129.514.819.13233step 734=APP2V(G20#,G28#,,-1)35 Prod 1.Prod 2.Prod 3.Prod 4362020202120202021202020212020202137Jan23.416.218.321.131.714.519.817.938Feb18.713.523.211.914.615.318.325.639Mar30.224.733.324.835.129.514.819.140single cell dynamic LET41 =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 elements43 Prod 1.Prod 2.Prod 3.Prod 4442020202120202021202020212020202145Jan23.416.218.321.131.714.519.817.946Feb18.713.523.211.914.615.318.325.647Mar30.224.733.324.835.129.514.819.148new ATJ 5Cell FormulasRangeFormulaD10,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.


----------



## Xlambda (Aug 28, 2021)

*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.xlsxABCDEFGHIJKLMNOPQRSTUVWX1expanded data setsame single cell formula23Report2019/2020/2021 Prod 1..Prod 2..Prod 3..Prod 4..Prod 54MonthProd 1Prod 2Prod 3Prod 4Prod 52019202020212019202020212019202020212019202020212019202020215Jan23.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.36Feb18.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.87Mar30.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.18Apr15.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.79new ATJ 6Cell FormulasRangeFormulaH3: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.


----------



## Xlambda (Nov 11, 2021)

New function by Dermot, SPLITTER, using recursion. I salute his initiative for using recursion!! ✌?
Deals only with single values, no 1D or 2D arrays
For single values this is my take, no recursion *SP(a,d)*

```
=LAMBDA(a,d,LET(s,SEQUENCE(,LEN(a)+1),x,FILTER(s,(MID(d&a,s,1)=d)*s),y,FILTER(s,(MID(a&d,s,1)=d)*s),MID(a,x,y-x)))
```
Book1ABCDEFGH1Dermot's function SPLITTER 23=SPLITTER(B4:B5,",")=ATEXTSPLIT(B4:B5,",")4a,b#NUM!ab5c,dcd67Seems function is designed only for single values89=SPLITTER(B10,",")10a,bab✌1112Variant for single values, no recursion SP(a,d)13=SP(B14,",")14a,bab15Sheet1Cell FormulasRangeFormulaD3,F3,D13,D9D3=FORMULATEXT(D4)D4D4=SPLITTER(B4:B5,",")F4:G5F4=ATEXTSPLIT(B4:B5,",")D10:E10D10=SPLITTER(B10,",")D14:E14D14=SP(B14,",")Dynamic array formulas.


----------



## Xlambda (Nov 11, 2021)

*Task: Remove text between delimiters using combo ATEXTSPLIT/ATEXTJOIN*
Inspired by latest YT (11-Nov-21) of MyOnlineTrainingHub. Power Query - Remove Text Between Delimiters Reusable Custom Function
*ARBD(a,ld,rd,[kd])* *A*rray *R*emove *B*etween *D*elimiters. Calls ATEXTSPLIT , ATEXTJOIN
a: array 1D vertical
ld: left delimiter
rd: right delimiter
[kd]: keep delimiter: 0 or omitted, doesn't keep,1 or <>0, keeps delimiters

```
=LAMBDA(a,ld,rd,[kd],LET(d,"|",x,SUBSTITUTE(SUBSTITUTE(a,ld,IF(kd,ld&d,d)),rd,IF(kd,d&rd,d)),
    t,ATEXTSPLIT(x,d),c,INT(COLUMNS(t)/2)+1,y,INDEX(t,SEQUENCE(ROWS(a)),SEQUENCE(,c,,2)),
    ATEXTJOIN(y,"",1)
    )
)
```
LAMBDA 1.2.1.xlsxABCDEFG1ld,"(",rd,")",kd,omittedld,"(",rd,")",kd,12=ARBD(A3:A4,"(",")")=ARBD(A3:A4,"(",")",1)312(a),23(bc),456(cde)12,23,45612(),23(),456()4ab(123),cde(12),f,gh(1234),(89),ijkab,cde,f,gh,,ijkab(),cde(),f,gh(),(),ijk56ld,"(",rd,")",kd,omittedld,"(",rd,")",kd,17=ARBD(A8:A10,"(",")")=ARBD(A8:A10,"(",")",1)8a(1),b(2),(3),(4),(5),c,(1234),da,b,,,,c,,da(),b(),(),(),(),c,(),d91 (ab),2 (abc)1,21 (),2 ()101234,56(uyuyluylyuttdtydiyt)781234,56781234,56()7811ATEXTSPLIT btwn delimCell FormulasRangeFormulaC2,E2,C7,E7C2=FORMULATEXT(C3)C3:C4C3=ARBD(A3:A4,"(",")")E3:E4E3=ARBD(A3:A4,"(",")",1)C8:C10C8=ARBD(A8:A10,"(",")")E8:E10E8=ARBD(A8:A10,"(",")",1)Dynamic array formulas.


----------



## Xlambda (Apr 21, 2022)

Inspired by latest Excel Campus-Jon's latest YT (21-Apr-2022) : What is the Best Way to Split Text in Excel?
Using new functions TEXTSPLIT, TEXTBEFORE, TEXTAFTER
TEXTSPLIT-vs-TEXTBEFORE-AFTER.xlsxABCDEFGH1Jon's formulas using TEXTBEFORE, TEXTAFTER2Split Three or More Names with TEXTBEFORE & TEXTAFTER34Full NameFirstMiddleLastMiddle Part 1Middle Part 2Middle Part 35Ardisj FlindallArdisj FlindallFlindall#VALUE! 6Nixie FairbrotherNixie FairbrotherFairbrother#VALUE! 7Ciro Acres BarabischCiroAcresBarabischAcres BarabischAcresAcres8Malinda Ann Howton O'KerinMalindaAnn HowtonO'KerinAnn Howton O'KerinAnn HowtonAnn Howton9Melisse GasnollMelisse GasnollGasnoll#VALUE! 10Booth McMylorBooth McMylorMcMylor#VALUE! 11Rafi Walesa PriddisRafiWalesaPriddisWalesa PriddisWalesaWalesa12Anita Mulvey SerrelsAnitaMulveySerrelsMulvey SerrelsMulveyMulvey13Gillian FordGillian FordFord#VALUE! 14Aldo HandscombeAldo HandscombeHandscombe#VALUE! 1516Alternative formulas:1718Full NameFirstMiddleLast19Ardisj FlindallArdisj Flindall20Nixie FairbrotherNixie Fairbrother21Ciro Acres BarabischCiro Acres Barabisch22Malinda Ann Howton O'KerinMalinda Ann Howton O'Kerin23Melisse GasnollMelisse Gasnoll24Booth McMylorBooth McMylor25Rafi Walesa PriddisRafi Walesa Priddis26Anita Mulvey SerrelsAnita Mulvey Serrels27Gillian FordGillian Ford28Aldo HandscombeAldo Handscombe2930First:=TAKE(TEXTSPLIT([@[Full Name]]," "),,1)31Middle:=SUBSTITUTE(SUBSTITUTE([@[Full Name]],[@First],""),[@Last],"")32Last:=TAKE(TEXTSPLIT([@[Full Name]]," "),,-1)3334Full NameFirstMiddleLast35Ardisj FlindallArdisj Flindall36Nixie FairbrotherNixie Fairbrother37Ciro Acres BarabischCiroAcresBarabisch38Malinda Ann Howton O'KerinMalindaAnn HowtonO'Kerin39Melisse GasnollMelisse Gasnoll40Booth McMylorBooth McMylor41Rafi Walesa PriddisRafiWalesaPriddis42Anita Mulvey SerrelsAnitaMulveySerrels43Gillian FordGillian Ford44Aldo HandscombeAldo Handscombe4546alternative47Middle:=IFERROR(TEXTJOIN(" ",,DROP(DROP(TEXTSPLIT([@[Full Name]]," "),,1),,-1)),"")48Split 3 Names TableCell FormulasRangeFormulaB5:B14B5=TEXTBEFORE([@[Full Name]]," ")C5:C14C5=IFERROR(TEXTBEFORE(TEXTAFTER([@[Full Name]]," ")," ",-1),"")D5:D14D5=TEXTAFTER([@[Full Name]]," ",-1)E5:E14E5=TEXTAFTER([@[Full Name]]," ")F5:F14F5=TEXTBEFORE([@[Middle Part 1]]," ",-1)G5:G14G5=IFERROR([@[Middle Part 2]],"")B19:B28,B35:B44B19=TAKE(TEXTSPLIT([@[Full Name]]," "),,1)C19:C28C19=SUBSTITUTE(SUBSTITUTE([@[Full Name]],[@First],""),[@Last],"")D19:D28,D35:D44D19=TAKE(TEXTSPLIT([@[Full Name]]," "),,-1)B30B30=FORMULATEXT(B19)B31,B47B31=FORMULATEXT(C19)B32B32=FORMULATEXT(D19)C35:C44C35=IFERROR(TEXTJOIN(" ",,DROP(DROP(TEXTSPLIT([@[Full Name]]," "),,1),,-1)),"")


----------



## Xlambda (Apr 21, 2022)

A quick function to *split* a vertical *array* *"ar"* into *3 parts* by a *delimiter* *"dl"* (1st part-before delimiter,2nd part-btw delimiters, 3rd part-after delimiter)
*ATEXTBTW(ar,dl)*

```
=LAMBDA(ar, dl,
    LET(
        s, " ",
        a, TRIM(SUBSTITUTE(ar, dl, s)),
        MAKEARRAY(
            ROWS(a),
            3,
            LAMBDA(r, c,
                LET(
                    i, INDEX(a, r),
                    x, IFERROR(TEXTBEFORE(i, s, 1), i),
                    y, IFERROR(TEXTAFTER(i, s, -1), ""),
                    SWITCH(c, 1, x, 3, y, TRIM(SUBSTITUTE(SUBSTITUTE(i, x, ""), y, "")))
                )
            )
        )
    )
)
```
TEXTSPLIT-vs-TEXTBEFORE-AFTER.xlsxABCDEFGHIJ1Array Splitting in 3.ATEXTBTW functiondl," "checking LEN after splitting2=ATEXTBTW(A3:A12," ")=LEN(C3#)3Ardisj FlindallArdisjFlindall6084Nixie FairbrotherNixieFairbrother50115Ciro Acres BarabischCiroAcresBarabisch4596Malinda Ann Howton O'KerinMalindaAnn HowtonO'Kerin71077Melisse GasnollMelisseGasnoll7078Booth McMylorBoothMcMylor5079Rafi Walesa PriddisRafiWalesaPriddis46710Anita Mulvey SerrelsAnitaMulveySerrels56711Gillian FordGillianFord70412Aldo HandscombeAldoHandscombe40101314dl," "15=ATEXTBTW(A16:A20," ")=LEN(C16#)16aa bb cc dd ee ffaabb cc dd eeff211217xx yyxxyy20218ggg ggg30019h i j k l m n ohi j k l m no111120asd hgf daasdhgfda3322122dl,", " (dbl delimiter "," plus a space " ")23=ATEXTBTW(A24:A28,", ")=LEN(C24#)24aa, bb, cc, dd, ee, ffaabb cc dd eeff211225xx, yyxxyy20226gggggg30027h, i, j, k, l, m, n, ohi j k l m no111128asd, hgf, daasdhgfda3322930dl,","31sample with extra spaces=ATEXTBTW(A32:A35,",")=LEN(C32#)32aa ,   bb, cc   ,ddaabb ccdd25233eee ,   ffeeeff30234gggg20035hh ,   kl   ,  mnohhklmno22336ATEXTBTWCell FormulasRangeFormulaC2,G31,C31,G23,C23,G15,C15,G2C2=FORMULATEXT(C3)C3:E12C3=ATEXTBTW(A3:A12," ")G3:I12,G32:I35,G24:I28,G16:I20G3=LEN(C3#)C16:E20C16=ATEXTBTW(A16:A20," ")C24:E28C24=ATEXTBTW(A24:A28,", ")C32:E35C32=ATEXTBTW(A32:A35,",")Dynamic array formulas.


----------



## Xlambda (May 19, 2022)

Great new challenge from Chandoo, today's YT (19May22) : Excel Challenge - Can you split their expenses?
Chandoo solved this brilliantly with a PQ table and a bunch of formulas for the final report.
To solve this with only a single cell formula, no PQ, no refresh, no helper anything there are 3 different approaches:
*1st*: does not call any other custom-made functions (has some limitations though)
*2nd*: solution, calls ATEXTSPLIT (no limitations)
*3rd*: solution, calls SPILLBYROW, function under ASCAN thread, will be published there, link will follow when will be ready (no limitations).
split-expenses.xlsxABCDEFGH1sample table: "exp"Task: Getting from table values to this report23Expense DetailsPersonHow much they spentShare of ExpensesTo Receive / Pay4Phil: 255.12,121.29,160.23Phil4928.882982.4351946.4455Alan: 43.17,226.76,343.66,358.01Alan2723.082982.435-259.3556Phil: 159.95,177.56,278.37Stu1961.672982.435-1020.7657Stu: 206.51,188.09,244.68,33.21,152.98Doug2316.112982.435-666.3258Phil: 285.91,306.4,140.689Doug: 226.76,226.76,169.6,208.67↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑10Phil: 326.4unq namessum of expensessum(clm2)/4clm2-clm311Phil: 308.43,224.95,102.3312Phil: 168.6413Stu: 259.9314Stu: 121.2915Stu: 17716Alan: 172.2317Alan: 214.04,162.58,205.66,188.16,128.4118Alan: 62.75,160.51,173.34,283.819Phil: 356.3,361.34,41.35,255.94,67.4220Stu: 168.25,161.28,248.4521Doug: 163.93,308.43,218.5822Phil: 226.04,265.97,140.81,197.4523Doug: 216.79,180.39,246.65,149.552425new ATEXTSPLIT 1


----------



## Xlambda (May 19, 2022)

*1st solution*. *Concept. Why we have limitations.*
split-expenses.xlsxABCDEFGHIJKLMNOPQRST1Concept 1st solution approach:23TEXTSPLIT still does not handle arrays, but can be tricked to do it, with some limitations.45TEXTSPLIT array will deliver6only the first cells columnCell by cell works fine7=TEXTSPLIT(B8:B9,",")8a,b,caabc=TEXTSPLIT(B8,",")9x,yxxy=TEXTSPLIT(B9,",")1011Trick to make TEXTSPLIT "work" with arrays step by step:Previous step delivers an extra row12that we have to exclude13=B14:B15&":"=TEXTJOIN("",,D14#)=TEXTSPLIT(F14,",",":")=DROP(I14#,-1)=IFNA(M14#,"")14a,b,ca,b,c:a,b,c:x,y:abcabcabc15x,yx,y:↑↑↑↑xy#N/Axy#N/Axy16↑↑↑↑#N/A#N/A17↑↑↑↑18Entire array in a single cell, therefore the limitations19of joining limit of 32767 chars. 20Can not be used for large arrays !!!!!2122trick single cell formula23=LET(a,B14:B15,b,TEXTJOIN("",,a&":"),c,TEXTSPLIT(b,",",":"),IFNA(DROP(c,-1),""))24abc25xy26new ATEXTSPLIT 2Cell FormulasRangeFormulaD7,D23,Q13,D13,F13,I13,M13D7=FORMULATEXT(D8)D8:D9D8=TEXTSPLIT(B8:B9,",")G8:I8,G9:H9G8=TEXTSPLIT(B8,",")J8:J9J8=FORMULATEXT(G8)D14:D15D14=B14:B15&":"F14F14=TEXTJOIN("",,D14#)I14:K16I14=TEXTSPLIT(F14,",",":")M14:O15M14=DROP(I14#,-1)Q14:S15Q14=IFNA(M14#,"")D24:F25D24=LET(a,B14:B15,b,TEXTJOIN("",,a&":"),c,TEXTSPLIT(b,",",":"),IFNA(DROP(c,-1),""))Dynamic array formulas.


----------



## Xlambda (May 30, 2021)

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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1TEXTJOINATEXTSPLIT2empty ignoredempty not ignored2 charsempty ignored3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=04sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check5a4ca,4,,ca4ca4ca><6.8><ta6.8t1316d0.5d,,,0.5d0.5d0.5><2.4  w><2.4  w6007g hi 2k  l3 ng h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n<Ac4><gh>< ><<Ac4gh 4218space9empty ignoredea arg.=0ea arg.=1empty not ignored10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=111a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check12d,0.5d0.5d0.5a6.8t131013g h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n2.4  w060014<Ac4gh 421015Complex scenariospace followed by an empty string16empty ignoredempty not ignored17ea 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 chars20,,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 chars22, ,,x   4, ,, x   4  x   4 2324Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument25                            -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument26                       We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)2728=LEN(H19#)=LEN(N19#)291113311130303011130001011331413110413110321510001051003334- 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 used3536ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)37fixed with 4 pos.a bc/a  b/ bcda bca  b bcd44438/____ /ab d/   d/a   ab d   da   44439/xxxx/check:pattern is kept40ATEXTSPLIT postCell FormulasRangeFormulaH4,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.


----------



## Xlambda (May 19, 2022)

*1st solution. Step by step calculations.*
split-expenses.xlsxABCDEFGHIJKLMNOPQ1sample table: "exp"step 12=TEXTBEFORE(exp[Expense Details],":")3Expense Details↓↓↓step 2step 3: TEXTSPLIT array (using trick concept)step 44Phil: 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.646Phil: 159.95,177.56,278.37Alan 43.17,226.76,343.66,358.0143.17226.76343.66358.01971.67Stu: 206.51,188.09,244.68,33.21,152.98Phil 159.95,177.56,278.37159.95177.56278.37615.888Phil: 285.91,306.4,140.68Stu 206.51,188.09,244.68,33.21,152.98206.51188.09244.6833.21152.98825.479Doug: 226.76,226.76,169.6,208.67Phil 285.91,306.4,140.68285.91306.4140.68732.9910Phil: 326.4Doug 226.76,226.76,169.6,208.67226.76226.76169.6208.67831.7911Phil: 308.43,224.95,102.33Phil326.4326.4326.412Phil: 168.64Phil 308.43,224.95,102.33308.43224.95102.33635.7113Stu: 259.93Phil168.64168.64168.6414Stu: 121.29Stu259.93259.93259.9315Stu: 177Stu121.29121.29121.2916Alan: 172.23Stu17717717717Alan: 214.04,162.58,205.66,188.16,128.41Alan172.23172.23172.2318Alan: 62.75,160.51,173.34,283.8Alan 214.04,162.58,205.66,188.16,128.41214.04162.58205.66188.16128.41898.8519Phil: 356.3,361.34,41.35,255.94,67.42Alan 62.75,160.51,173.34,283.862.75160.51173.34283.8680.420Stu: 168.25,161.28,248.45Phil 356.3,361.34,41.35,255.94,67.42356.3361.3441.35255.9467.421082.3521Doug: 163.93,308.43,218.58Stu 168.25,161.28,248.45168.25161.28248.45577.9822Phil: 226.04,265.97,140.81,197.45Doug 163.93,308.43,218.58163.93308.43218.58690.9423Doug: 216.79,180.39,246.65,149.55Phil 226.04,265.97,140.81,197.45226.04265.97140.81197.45830.2724Doug 216.79,180.39,246.65,149.55216.79180.39246.65149.55793.382526step 5: unq namesstep 6: sum expenses clmstep 7: share of expenses clmstep 8: To Receive/Pay clm27=UNIQUE(D5#)=MAP(D28#,LAMBDA(x,SUM((x=D5#)*N5#)))=SUM(F28#)/4+SEQUENCE(ROWS(D28#))^0-1=F28#-H28#28Phil4928.882982.4351946.44529Alan2723.082982.435-259.35530Stu1961.672982.435-1020.7731Doug2316.112982.435-666.32532new ATEXTSPLIT 3Cell FormulasRangeFormulaD2D2=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-1M28:M31M28=F28#-H28#Dynamic array formulas.


----------



## Xlambda (May 19, 2022)

*1st solution. Single cell formula set at D3:*

```
=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.xlsxABCDEFGH1sample table: "exp"Single cell formula23Expense DetailsPersonSpentShareTo Receive/Pay4Phil: 255.12,121.29,160.23Phil4928.882982.4351946.4455Alan: 43.17,226.76,343.66,358.01Alan2723.082982.435-259.3556Phil: 159.95,177.56,278.37Stu1961.672982.435-1020.7657Stu: 206.51,188.09,244.68,33.21,152.98Doug2316.112982.435-666.3258Phil: 285.91,306.4,140.689Doug: 226.76,226.76,169.6,208.6710Phil: 326.411Phil: 308.43,224.95,102.3312Phil: 168.6413Stu: 259.9314Stu: 121.2915Stu: 17716Alan: 172.2317Alan: 214.04,162.58,205.66,188.16,128.4118Alan: 62.75,160.51,173.34,283.819Phil: 356.3,361.34,41.35,255.94,67.4220Stu: 168.25,161.28,248.4521Doug: 163.93,308.43,218.5822Phil: 226.04,265.97,140.81,197.4523Doug: 216.79,180.39,246.65,149.5524new ATEXTSPLIT 4Cell FormulasRangeFormulaD3: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.


----------



## Xlambda (May 19, 2022)

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

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXY2Crazy array example to check TEXTSPLIT and ATEXTSPLIT neck to neck341. ignore empty2. include empty5=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,",")910checking LENchecking LEN11=LEN(D6:J7)=LEN(M6:X7)1221121112101201110001313111100103010011101415=ATEXTSPLIT(B6:B7,",")=ATEXTSPLIT(B6:B7,",",1)16a    b c a    b c 17  x  yz   x  yz 1819checking LENchecking LEN20=LEN(D16:J17)=LEN(M16#)2121121112101201110002213111100103010011102324=SUM(AND(D12#-D21#))=SUM(M12#-M21#)250<= same results =>026new ATEXTSPLIT 5Cell FormulasRangeFormulaD5,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.


----------



## Xlambda (May 19, 2022)

*2nd solution. Using ATEXTSPLIT. Step by step*
split-expenses.xlsxABCDEFGHIJKLM1sample table: "exp"step 1rest of steps identical to 1st solution2=TEXTBEFORE(exp[Expense Details],":")3Expense Details↓↓↓step 2step 3: using ATEXTSPLIT4Phil: 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.236Phil: 159.95,177.56,278.37Alan 43.17,226.76,343.66,358.0143.17226.76343.66358.017Stu: 206.51,188.09,244.68,33.21,152.98Phil 159.95,177.56,278.37159.95177.56278.378Phil: 285.91,306.4,140.68Stu 206.51,188.09,244.68,33.21,152.98206.51188.09244.6833.21152.989Doug: 226.76,226.76,169.6,208.67Phil 285.91,306.4,140.68285.91306.4140.6810Phil: 326.4Doug 226.76,226.76,169.6,208.67226.76226.76169.6208.6711Phil: 308.43,224.95,102.33Phil326.4326.412Phil: 168.64Phil 308.43,224.95,102.33308.43224.95102.3313Stu: 259.93Phil168.64168.6414Stu: 121.29Stu259.93259.9315Stu: 177Stu121.29121.2916Alan: 172.23Stu17717717Alan: 214.04,162.58,205.66,188.16,128.41Alan172.23172.2318Alan: 62.75,160.51,173.34,283.8Alan 214.04,162.58,205.66,188.16,128.41214.04162.58205.66188.16128.4119Phil: 356.3,361.34,41.35,255.94,67.42Alan 62.75,160.51,173.34,283.862.75160.51173.34283.820Stu: 168.25,161.28,248.45Phil 356.3,361.34,41.35,255.94,67.42356.3361.3441.35255.9467.4221Doug: 163.93,308.43,218.58Stu 168.25,161.28,248.45168.25161.28248.4522Phil: 226.04,265.97,140.81,197.45Doug 163.93,308.43,218.58163.93308.43218.5823Doug: 216.79,180.39,246.65,149.55Phil 226.04,265.97,140.81,197.45226.04265.97140.81197.4524Doug 216.79,180.39,246.65,149.55216.79180.39246.65149.5525new ATEXTSPLIT 6Cell FormulasRangeFormulaD2D2=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.


----------



## Xlambda (May 19, 2022)

*2nd solution. Using ATEXTSPLIT. Single cell formula at D3*

```
=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.xlsxABCDEFGH1sample table: "exp"single cell formula. Calls ATEXTSPLIT23Expense DetailsPersonSpentShareTo Receive/Pay4Phil: 255.12,121.29,160.23Phil4928.92982.4351946.4455Alan: 43.17,226.76,343.66,358.01Alan2723.12982.435-259.3556Phil: 159.95,177.56,278.37Stu1961.72982.435-1020.7657Stu: 206.51,188.09,244.68,33.21,152.98Doug2316.12982.435-666.3258Phil: 285.91,306.4,140.689Doug: 226.76,226.76,169.6,208.6710Phil: 326.411Phil: 308.43,224.95,102.3312Phil: 168.6413Stu: 259.9314Stu: 121.2915Stu: 17716Alan: 172.2317Alan: 214.04,162.58,205.66,188.16,128.4118Alan: 62.75,160.51,173.34,283.819Phil: 356.3,361.34,41.35,255.94,67.4220Stu: 168.25,161.28,248.4521Doug: 163.93,308.43,218.5822Phil: 226.04,265.97,140.81,197.4523Doug: 216.79,180.39,246.65,149.552425new ATEXTSPLIT 7Cell FormulasRangeFormulaD3: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.


----------



## Xlambda (May 20, 2022)

*1st solution alternative, step by step*
split-expenses.xlsxABCDEFGHIJKLMNOPQRSTUVW1sample table: "exp"1st solution, alternative, step by step2step 1step 2step 3step 43Expense 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.645Alan: 43.17,226.76,343.66,358.01Alan43.17226.76343.66358.010Alan43.17226.76343.66358.010971.66Phil: 159.95,177.56,278.37Phil159.95177.56278.3700Phil159.95177.56278.3700615.887Stu: 206.51,188.09,244.68,33.21,152.98Stu206.51188.09244.6833.21152.98Stu206.51188.09244.6833.21152.98825.478Phil: 285.91,306.4,140.68Phil285.91306.4140.6800Phil285.91306.4140.6800732.999Doug: 226.76,226.76,169.6,208.67Doug226.76226.76169.6208.670Doug226.76226.76169.6208.670831.7910Phil: 326.4Phil326.40000Phil326.40000326.411Phil: 308.43,224.95,102.33Phil308.43224.95102.3300Phil308.43224.95102.3300635.7112Phil: 168.64Phil168.640000Phil168.640000168.6413Stu: 259.93Stu259.930000Stu259.930000259.9314Stu: 121.29Stu121.290000Stu121.290000121.2915Stu: 177Stu1770000Stu177000017716Alan: 172.23Alan172.230000Alan172.230000172.2317Alan: 214.04,162.58,205.66,188.16,128.41Alan214.04162.58205.66188.16128.41Alan214.04162.58205.66188.16128.41898.8518Alan: 62.75,160.51,173.34,283.8Alan62.75160.51173.34283.80Alan62.75160.51173.34283.80680.419Phil: 356.3,361.34,41.35,255.94,67.42Phil356.3361.3441.35255.9467.42Phil356.3361.3441.35255.9467.421082.3520Stu: 168.25,161.28,248.45Stu168.25161.28248.4500Stu168.25161.28248.4500577.9821Doug: 163.93,308.43,218.58Doug163.93308.43218.5800Doug163.93308.43218.5800690.9422Phil: 226.04,265.97,140.81,197.45Phil226.04265.97140.81197.450Phil226.04265.97140.81197.450830.2723Doug: 216.79,180.39,246.65,149.55Doug216.79180.39246.65149.550Doug216.79180.39246.65149.550793.382425step 5step 6step 7step 826=UNIQUE(K4#)=MAP(D27#,LAMBDA(x,SUM(IF(x=K4#,S4#))))=SUM(F27#)/4+SEQUENCE(ROWS(D27#))^0-1=F27#-K27#27Phil4928.882982.41946.428Alan2723.082982.4-259.3629Stu1961.672982.4-1020.830Doug2316.112982.4-666.333132step 933=HSTACK(D27#,F27#,K27#,R27#)34Phil4928.882982.4351946.44535Alan2723.082982.435-259.35536Stu1961.672982.435-1020.76537Doug2316.112982.435-666.32538new ATEXTSPLIT 8Cell FormulasRangeFormulaD3,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-1R27:R30R27=F27#-K27#D34:G37D34=HSTACK(D27#,F27#,K27#,R27#)Dynamic array formulas.


----------



## Xlambda (May 20, 2022)

*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 {":",","}*

```
=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.xlsxABCDEFGH1sample table: "exp"23Expense DetailsPersonSpentShareTo Receive/Pay4Phil: 255.12,121.29,160.23Phil4928.882982.4351946.4455Alan: 43.17,226.76,343.66,358.01Alan2723.082982.435-259.3556Phil: 159.95,177.56,278.37Stu1961.672982.435-1020.7657Stu: 206.51,188.09,244.68,33.21,152.98Doug2316.112982.435-666.3258Phil: 285.91,306.4,140.689Doug: 226.76,226.76,169.6,208.6710Phil: 326.411Phil: 308.43,224.95,102.3312Phil: 168.6413Stu: 259.9314Stu: 121.2915Stu: 17716Alan: 172.2317Alan: 214.04,162.58,205.66,188.16,128.4118Alan: 62.75,160.51,173.34,283.819Phil: 356.3,361.34,41.35,255.94,67.4220Stu: 168.25,161.28,248.4521Doug: 163.93,308.43,218.5822Phil: 226.04,265.97,140.81,197.4523Doug: 216.79,180.39,246.65,149.5524new ATEXTSPLIT 9Cell FormulasRangeFormulaD3: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.


----------



## Xlambda (May 20, 2022)

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() )

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH1Versatility TEXTSPLIT/ATEXTSPLIT231. can split for an array of delimiters.4Splitting will take place wherever any of the delimiters present in the array of delimiters is found.56Complex 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.1011a. ignore empty argument of both function omitted => empty cells included1213=TEXTSPLIT(A14,{":",";",","})=LEN(C14:M15)14a, b;,c ,:; d: e ,fa bc  d e f1202002310015, : ; x;y,;z:v, :;    xyzv 01121011100both function return same rusults16=TEXTSPLIT(A15,{":",";",","})=SUM(O14#)-SUM(O19#)17018=ATEXTSPLIT(A14:A15,{":",",",";"})=LEN(C19#)19a bc  d e f1202002310020   xyzv 011210111002122b. ignore empty argument of both function=1 => empty cells ignored2324=TEXTSPLIT(A14,{":",";",","},,1)=LEN(C25:I26)25a bc  d e f122231026   xyzv 1121111both function return same rusults27=TEXTSPLIT(A15,{":",";",","},,1)=SUM(M25#)-SUM(M30#)28029=ATEXTSPLIT(A14:A15,{",",";",":"},1)=LEN(C30#)30a bc  d e f122231031   xyzv 112111132new ATEXTSPLIT 10Cell FormulasRangeFormulaC13,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.


----------



## Xlambda (May 20, 2022)

split-expenses.xlsxABCDEFGHIJKLM1Versatility TEXTSPLIT/ATEXTSPLIT232. 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.783. "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 returned11If ATEXTSPLIT omits "pad" argument, "" will be returned121314Splitting 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.4517Doug: 163.93,308.43,218.5818Phil: 226.04,265.97,140.81,197.45=TEXTSPLIT(D16,{":",","},"|",,"missing")after splitting everything19Stu168.25161.28248.45missingwill stay as text20Doug163.93308.43218.58missing=SUM(D19#)21Phil226.04265.97140.81197.4502223Splitting array using ATEXTSPLIT, "pad"="missing"24=ATEXTSPLIT(B16:B18,{":",","},,"missing")ATEXTSPLIT checks for possible25Stu168.25161.28248.45missingnumeric values and wherever finds them26Doug163.93308.43218.58missingconverts them to numbers27Phil226.04265.97140.81197.45=SUM(D25#)282099.1929new ATEXTSPLIT 11Cell FormulasRangeFormulaD15,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.


----------



## Xlambda (May 20, 2022)

split-expenses.xlsxABCDEFGHIJKLMNO1Versatility TEXTSPLIT/ATEXTSPLIT2sample:34. using multiple chars delimiters.numbers (with thousands separators)4sampleseparated  by space,comma,space5a/b//c///d////e/////f//////g///////h123,345.24 , 4,567.23 , 0.237 , 38,235,104.3267ie,omitted=TEXTSPLIT(K5," , ")8=TEXTSPLIT(B5,"///")123,345.244,567.230.23738,235,104.329a/b//cd/e//fg/h10check for numbers TEXTSPLIT11=ATEXTSPLIT(B5,"///")=ISNUMBER(K8#)12a/b//cd/e//fg/hFALSEFALSEFALSEFALSE1314ie,1=ATEXTSPLIT(K5," , ")15=TEXTSPLIT(B5,"///",,1)123345.244567.230.23738235104.3216a/b//cd/e//fg/h17check for numbers ATEXTSPLIT18=ATEXTSPLIT(B5,"///",1)=ISNUMBER(K15#)19a/b//cd/e//fg/hTRUETRUETRUETRUE20new ATEXTSPLIT 12Cell FormulasRangeFormulaK7,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.


----------



## Xlambda (May 30, 2021)

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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1TEXTJOINATEXTSPLIT2empty ignoredempty not ignored2 charsempty ignored3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=04sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check5a4ca,4,,ca4ca4ca><6.8><ta6.8t1316d0.5d,,,0.5d0.5d0.5><2.4  w><2.4  w6007g hi 2k  l3 ng h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n<Ac4><gh>< ><<Ac4gh 4218space9empty ignoredea arg.=0ea arg.=1empty not ignored10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=111a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check12d,0.5d0.5d0.5a6.8t131013g h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n2.4  w060014<Ac4gh 421015Complex scenariospace followed by an empty string16empty ignoredempty not ignored17ea 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 chars20,,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 chars22, ,,x   4, ,, x   4  x   4 2324Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument25                            -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument26                       We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)2728=LEN(H19#)=LEN(N19#)291113311130303011130001011331413110413110321510001051003334- 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 used3536ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)37fixed with 4 pos.a bc/a  b/ bcda bca  b bcd44438/____ /ab d/   d/a   ab d   da   44439/xxxx/check:pattern is kept40ATEXTSPLIT postCell FormulasRangeFormulaH4,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.


----------



## Xlambda (May 21, 2022)

*2nd solution using latest ATEXTSPLIT (capable of splitting for an array of delimiters, post #28). Single cell formula at M4:*

```
=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.xlsxABCDEFGHIJKLMNOPQ1sample table: "exp"ATEXTSPLIT returns numbers as numbers even if the array2has 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/Pay5Alan: 43.17,226.76,343.66,358.01Alan43.17226.76343.66358.01971.6Phil4928.882982.4351946.4456Phil: 159.95,177.56,278.37Phil159.95177.56278.37615.88Alan2723.082982.435-259.3557Stu: 206.51,188.09,244.68,33.21,152.98Stu206.51188.09244.6833.21152.98825.47Stu1961.672982.435-1020.7658Phil: 285.91,306.4,140.68Phil285.91306.4140.68732.99Doug2316.112982.435-666.3259Doug: 226.76,226.76,169.6,208.67Doug226.76226.76169.6208.67831.7910Phil: 326.4Phil326.4326.411Phil: 308.43,224.95,102.33Phil308.43224.95102.33635.7112Phil: 168.64Phil168.64168.6413Stu: 259.93Stu259.93259.9314Stu: 121.29Stu121.29121.2915Stu: 177Stu17717716Alan: 172.23Alan172.23172.2317Alan: 214.04,162.58,205.66,188.16,128.41Alan214.04162.58205.66188.16128.41898.8518Alan: 62.75,160.51,173.34,283.8Alan62.75160.51173.34283.8680.419Phil: 356.3,361.34,41.35,255.94,67.42Phil356.3361.3441.35255.9467.421082.3520Stu: 168.25,161.28,248.45Stu168.25161.28248.45577.9821Doug: 163.93,308.43,218.58Doug163.93308.43218.58690.9422Phil: 226.04,265.97,140.81,197.45Phil226.04265.97140.81197.45830.2723Doug: 216.79,180.39,246.65,149.55Doug216.79180.39246.65149.55793.3824new ATEXTSPLIT 13Cell FormulasRangeFormulaK2K2=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.


----------



## Xlambda (May 22, 2022)

*1st solution new alternative* (no ATEXTSPLIT, *uses BYROW(initial array)*, no need of BYROW(array construction))
Single cell formula at F6:

```
=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.xlsxABCDEFGHIJKL1sample table: "exp"1st solution.New concept, no array construction before BYROW, BYROW directly with initial array23Expense Details=BYROW(exp[Expense Details],LAMBDA(x,LET(y,TEXTSPLIT(x,{":",","}),SUM(IFERROR(--y,y)))))4Phil: 255.12,121.29,160.23536.645Alan: 43.17,226.76,343.66,358.01971.66Phil: 159.95,177.56,278.37615.88PersonSpentShareTo Receive/Pay7Stu: 206.51,188.09,244.68,33.21,152.98825.47Phil4928.882982.4351946.4458Phil: 285.91,306.4,140.68732.99Alan2723.082982.435-259.3559Doug: 226.76,226.76,169.6,208.67831.79Stu1961.672982.435-1020.76510Phil: 326.4326.4Doug2316.112982.435-666.32511Phil: 308.43,224.95,102.33635.7112Phil: 168.64168.6413Stu: 259.93259.9314Stu: 121.29121.2915Stu: 17717716Alan: 172.23172.2317Alan: 214.04,162.58,205.66,188.16,128.41898.8518Alan: 62.75,160.51,173.34,283.8680.419Phil: 356.3,361.34,41.35,255.94,67.421082.3520Stu: 168.25,161.28,248.45577.9821Doug: 163.93,308.43,218.58690.9422Phil: 226.04,265.97,140.81,197.45830.2723Doug: 216.79,180.39,246.65,149.55793.3824new ATEXTSPLIT 14Cell FormulasRangeFormulaD3D3=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.


----------



## Xlambda (Jun 27, 2022)

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 ""

```
=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)
    )
)
```
Book1ABCDEFGHIJKLM1Split a name and a sentence by using a common delimiter.23=ATEXTSPILL(B4:B5,LAMBDA(x,TEXTSPLIT(x," ")))4Dakota Lennon SanchezDakotaLennonSanchez5To be or not to beTobeornottobe67Split array constants into 2x3 array8=ATEXTSPILL(B9:B11,LAMBDA(x,TEXTSPLIT(x,",",";")))91,2,3;4,5,6123107,8,9;10,11,124561113,14,15;16,17,187891210111213131415141617181516As we see ATEXTSPILL is capable of staking the results arrays of each row.1718or from a single cell:19=ATEXTSPILL(TEXTJOIN(";",,B9:B11),LAMBDA(x,TEXTSPLIT(x,",",";")))2012321456227892310111224131415251617182627=ATEXTSPILL(B28:B29,LAMBDA(x,TEXTSPLIT(x,",",";")))28a,b,c,d;e,f,g,habcd291,2,3;4,5,6,7;8,9efgh3012331456732893334Note: The function excludes NA() errors by default even if pad argument in TEXTSPLIT is omitted35For pad functionality we can give a value to "nf" argument3637=ATEXTSPILL(B28:B29,LAMBDA(x,TEXTSPLIT(x,",",";")),"empty")38abcd39efghWherever possible text is converted to numbers40123empty=ISNUMBER(D38#)414567FALSEFALSEFALSEFALSE4289emptyemptyFALSEFALSEFALSEFALSE43TRUETRUETRUEFALSE44TRUETRUETRUETRUE45TRUETRUEFALSEFALSE46Sheet1Cell FormulasRangeFormulaD3,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.


----------



## Xlambda (Jun 27, 2022)

Book1ABCDEFGHIJKLMNO12including empty 3=ATEXTSPILL(B4:B5,LAMBDA(x, TEXTSPLIT(x,",",";")))4a,,,,d;e,f,g,had51,2,3;4,5,6,,7;8,9efgh612374567889910ignoring empty11=ATEXTSPILL(B4:B5,LAMBDA(x, TEXTSPLIT(x,",",";",1)))12ad13efgh1412315456716891718Cool example, "equalizing" different dimensions arrays into a 19fixed nr. of columns array, let's say 3 columns, to see what we get.2021a1abcdeAB22fghijCD23EF24a21234GH255678a32691011122728single cell formula29=ATEXTSPILL(TEXTJOIN({",",",",";"},,D21:H22,D24:G26,J21:K24),LAMBDA(x,TEXTSPLIT(x,",",";")))30abc31defThis was possible because of TEXTJOIN versatility32ghito work with an array of delimiters33j1234345=TEXTJOIN({",",",","|"},,J21:K24)35678A,B,C|D,E,F|G,H36910113712AB38CDE39FGH40Sheet2Cell FormulasRangeFormulaD3,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.


----------



## steven88 (Jun 27, 2022)

amazing.


----------



## Xlambda (Jun 28, 2022)

steven88 said:


> amazing.


Thanks a lot Steven!! So happy you like it!!✌️😉


----------



## Xlambda (Jun 28, 2022)

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.xlsxABCDEFGHIJKLMNOP1split only for spaces2cl=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x," ")))emty strings3Do. Or do not. There is no try. -AnonymousDo.Ordonot.Thereisnotry.-Anonymous4There are two options: Adapt or die. Therearetwooptions:Adaptordie.5One cannot step twice in the same river. -HeraclitusOnecannotsteptwiceinthesameriver.-Heraclitus6Whatever you are, be a good one. -Abraham LincolnWhateveryouare,beagoodone.-AbrahamLincoln78separators are: space , . : -split for all separators, single chars, (include empty by default)9=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",",",".",":","-"})))10dbl chars separators ". " , ", " ,": "DoOrdonotThereisnotryAnonymous11( . , and : followed by space)TherearetwooptionsAdaptordie12OnecannotsteptwiceinthesameriverHeraclitus13WhateveryouarebeagoodoneAbrahamLincoln1415split for all separators, some double chars sep, (include empty by default)16=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": ","-"})))17DoOrdonotThereisnotryAnonymous18TherearetwooptionsAdaptordie19OnecannotsteptwiceinthesameriverHeraclitus20WhateveryouarebeagoodoneAbrahamLincoln2122wanted solution: all possible separators, dbl chars sep, ignore empty23=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": ","-"},,1)))24DoOrdonotThereisnotryAnonymous25TherearetwooptionsAdaptordie26OnecannotsteptwiceinthesameriverHeraclitus27WhateveryouarebeagoodoneAbrahamLincoln2829extract author in a separate row30=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},"-",1)))31DoOrdonotThereisnotry32Anonymous33TherearetwooptionsAdaptordie34Onecannotsteptwiceinthesameriver35Heraclitus36Whateveryouarebeagoodone37AbrahamLincoln38ATS 1Cell FormulasRangeFormulaC2,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.


----------



## Xlambda (Jun 28, 2022)

ATEXTSPILL.xlsxABCDEFGHIJKLMNOP1ATEXTFILL with TEXTBEFORE/TEXTAFTER functions2cl=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. 78extract quotes9match to end argument,1extract quotes words single cell formula, nested ATEXTFILL with 2 different TEXT…functions10=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. DoOrdonotThereisnotry12There are two options: Adapt or die. TherearetwooptionsAdaptordie13One cannot step twice in the same river. Onecannotsteptwiceinthesameriver14Whatever you are, be a good one. Whateveryouarebeagoodone1516extracting authors17=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")))18Anonymous1920Heraclitus21Abraham Lincoln2223using "nf" argument to fill the blankextracting full name, single cell formula, nested ATEXTFILL24=ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")),"Anonymous")=ATEXTSPILL(ATEXTSPILL(A3:A6,LAMBDA(x,TEXTAFTER(x,"-")),"Anonymous"),LAMBDA(x,TEXTSPLIT(x," ")))25AnonymousAnonymous26AnonymousAnonymous27HeraclitusHeraclitus28Abraham LincolnAbrahamLincoln29ATS 2Cell FormulasRangeFormulaC2,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.


----------



## Xlambda (Jun 28, 2022)

ATEXTSPILL.xlsxABCDEFGHI1by default delimiters are case sensitive2cl=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x")))315.21x12.4x21.5X13.18x78.65x48.215.2112.421.5X13.1878.6548.2418.3x53.84X72.83x18.47x58.412x36.3718.353.84X72.8318.4758.41236.3756=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,{"x","X"})))715.2112.421.513.1878.6548.2818.353.8472.8318.4758.41236.37910or11using case insensitive match argument => 112=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x",,,1)))1315.2112.421.513.1878.6548.21418.353.8472.8318.4758.41236.371516=ATEXTSPILL(A3:A4,LAMBDA(x,TEXTSPLIT(x,"x","X")))1715.2112.421.51813.1878.6548.21918.353.842072.8318.4758.41236.3721ATS 3Cell FormulasRangeFormulaC2,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.


----------



## Xlambda (Jun 28, 2022)

*ATEXTSPILL with other functions*, no TEXT... related.
*Limitless possibilities to design patterns.*
ATEXTSPILL.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF1Versatility of ATEXTSPILL to expand, with any function capable of that.2Creating patterns, limitless possibilities3gradual random arrays4=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,4)))=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,RANDARRAY(,7,10*x,10*x+9,1)))51234161213131717156123426222220272228every row creates random nr. between712343032313731393710 and 19812344340464541404220 and 29912345754575955535030 and 3910123460666762656963·········111234707175707272721213=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)))=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,RANDARRAY(,x,10*x,10*x+9,1)))141151512232316123363239171234434644441812345565351505919123456616563626867201234567727976717776782122steps pattern (horizontal)23=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,REPT("A",SEQUENCE(,x)^0)))=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,2*x-1)))24A125AA12326AAA1234527AAAA123456728AAAAA12345678929AAAAAA123456789101130AAAAAAA123456789101112133132=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)^0-1+x))=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)+64)))331A3422AB35333ABC364444ABCD3755555ABCDE38666666ABCDEF397777777ABCDEFG4041cl=ATEXTSPILL(B42:B48,LAMBDA(x,SEQUENCE(,x)),"---")=ATEXTSPILL(SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)^0+63+x)))4271234567A433123BB44512345CCC45---DDDD46512345EEEEE473123FFFFFF4871234567GGGGGGG4950=ATEXTSPILL(8-SEQUENCE(7),LAMBDA(x,SEQUENCE(,x)))=ATEXTSPILL(8-SEQUENCE(7),LAMBDA(x,CHAR(SEQUENCE(,x)^0+63+x)))511234567GGGGGGG52123456FFFFFF5312345EEEEE541234DDDD55123CCC5612BB571A5859=ATEXTSPILL(ABS(SEQUENCE(9)-5),LAMBDA(x,SEQUENCE(,x+1)))=ATEXTSPILL(MOD(SEQUENCE(8),3)+1,LAMBDA(x,SEQUENCE(,x)))601234512611234123621231631212641123651216612312671234123681234569steps 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)))7112312721234127312345123474123456123475123456712347612345612345677123451234567812341234567912312345678801234567881ATS 4Cell FormulasRangeFormulaB4,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.


----------



## Xlambda (May 30, 2021)

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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1TEXTJOINATEXTSPLIT2empty ignoredempty not ignored2 charsempty ignored3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=04sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check5a4ca,4,,ca4ca4ca><6.8><ta6.8t1316d0.5d,,,0.5d0.5d0.5><2.4  w><2.4  w6007g hi 2k  l3 ng h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n<Ac4><gh>< ><<Ac4gh 4218space9empty ignoredea arg.=0ea arg.=1empty not ignored10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=111a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check12d,0.5d0.5d0.5a6.8t131013g h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n2.4  w060014<Ac4gh 421015Complex scenariospace followed by an empty string16empty ignoredempty not ignored17ea 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 chars20,,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 chars22, ,,x   4, ,, x   4  x   4 2324Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument25                            -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument26                       We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)2728=LEN(H19#)=LEN(N19#)291113311130303011130001011331413110413110321510001051003334- 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 used3536ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)37fixed with 4 pos.a bc/a  b/ bcda bca  b bcd44438/____ /ab d/   d/a   ab d   da   44439/xxxx/check:pattern is kept40ATEXTSPLIT postCell FormulasRangeFormulaH4,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.


----------



## Xlambda (Jun 28, 2022)

ATEXTSPILL.xlsxABCDEFGHIJKLMNO1Vertical patterns, we can flatten the horizontal ones and filter the blanks or we use same formulas simply by deleating a ","23=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)))81111A92112A103122B114113A121223B132233C143214A154224B161334C172344D183315A194325B201435C212445D223455E2344241525252635274528ATS 5Cell FormulasRangeFormulaB3B3=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.


----------



## Xlambda (Jun 28, 2022)

Challenge. Quite a common task in real life. *Create a random array with no dups by row.*
ATEXTSPILL.xlsxABCDEFGHIJKLMNOPQRSTUVWX1Task. Single cell formula to create an array 10 x 15 of random numbers between 1 and 100 with no duplicates by row23Using only RANDARRAY can produce duplicates by rowchecking dups by row, apart from CF4=RANDARRAY(10,15,1,100,1)=BYROW(B5#,LAMBDA(x,COUNT(UNIQUE(x,1))<15))579231641664173341873083217942TRUE6952817444674152446463737837TRUE7386650512688922726719353585059TRUE822157441355474718348665559FALSE97994931923243518843996933822TRUE106963755897217060769856947642TRUE1115737478862275445942529529727TRUE12208040545854685355144699345214TRUE131834100571883032977143333331TRUE14863476214282206766186366804254TRUE1516single cell formula17=ATEXTSPILL(SEQUENCE(10),LAMBDA(x,TAKE(SORTBY(SEQUENCE(,100),RANDARRAY(,100)),,15)))=BYROW(B18#,LAMBDA(x,COUNT(UNIQUE(x,1))<15))18755129668335193756801543522373FALSE1962279646856347810317698921130FALSE2033436626774462693474783558839FALSE21666720971716782752155791471842FALSE22244991923143463659739758878FALSE23563862573586483599872994187FALSE249482568193917342950469529833FALSE25672782372160203717445313510FALSE2625743241423982834054508877286FALSE2733448218142403417963966463562FALSE28ATS 6Cell FormulasRangeFormulaB4,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 FormattingCellConditionCell FormatStop If TrueR18Expression=R18textNOB18:P27Expression=COUNTIFS($B18:$P18,B18)>1textNOR5:R14Expression=R5textNOB5:P14Expression=COUNTIFS($B5:$P5,B5)>1textNO


----------



## Xlambda (Jun 28, 2022)

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.xlsxABCDEFGHIJK12=SPILLBYROWS(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},"-",1)))3Do. Or do not. There is no try. -AnonymousDoOrdonotThereisnotry4There are two options: Adapt or die. Anonymous5One cannot step twice in the same river. -HeraclitusTherearetwooptionsAdaptordie6Whatever you are, be a good one. -Abraham LincolnOnecannotsteptwiceinthesameriver7Heraclitus8Whateveryouarebeagoodone9AbrahamLincoln1011A tweak to trick ATEXTSPILL to handle 2D arrays is to "feed" entire rows into lambda helper argument.12Task. Extracting unique by rows in ascending order1314=RANDARRAY(5,8,1,20,1)151412122011514816518417717158171118128143162181594171667141916136121762021=SPILLBYROWS(C15#,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))225811121420234578151718242381112141618254679141516172613612172728=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,LET(y,INDEX(C15#,x,),SORT(UNIQUE(y,1),,,1))))2958111214203045781517183123811121416183246791415161733136121734Sheet12Cell FormulasRangeFormulaC2,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.


----------



## Xlambda (Jun 29, 2022)

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.xlsxABCDEFGHIJKLMNOPQRS1Copying data from site => single cell list with line feed separator CHAR(10)23Richard
Cruz
Calvin
Sheldon
Yadiel
Wade
Milo
Mark
Quinn
Bobby
Justus
Karter45Spliting as column vectorIf we not "inject" the random functionrandom function inside function argument6=TEXTSPLIT(B3,,CHAR(10))Shuffling the arrayinside function argument => same array sequencerecalculates with every iteration7↓↓↓=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.559265CalvinCalvinYadielRichardCruzCruzYadielSheldonBobby9Cruz0.930086MiloMiloBobbyJustusSheldonMarkJustusQuinnRichard10Calvin0.050665QuinnQuinnWadeKarterMarkWadeCalvinKarterMilo11Sheldon0.931163YadielCalvinYadielRichardCruzYadielMiloBobbyKarter12Yadiel0.283132BobbyMiloBobbyJustusSheldonQuinnWadeSheldonJustus13Wade0.505379WadeQuinnWadeKarterMarkCruzRichardMarkCalvin14Milo0.10434RichardCalvinYadielRichardCruzBobbyKarterWadeMilo15Mark0.945032JustusMiloBobbyJustusSheldonRichardSheldonJustusYadiel16Quinn0.151372KarterQuinnWadeKarterMarkMarkQuinnCalvinCruz17Bobby0.43663CruzCalvinYadielRichardCruzKarterSheldonBobbyJustus18Justus0.649646SheldonMiloBobbyJustusSheldonWadeMarkCalvinQuinn19Karter0.665065MarkQuinnWadeKarterMarkYadielMiloRichardCruz20CalvinYadielRichardCruzQuinnJustusYadielMark21MiloBobbyJustusSheldonBobbyRichardWadeCruz22QuinnWadeKarterMarkKarterMiloSheldonCalvin23ATS 8Cell FormulasRangeFormulaB6B6=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.


----------



## Xlambda (Jun 29, 2022)

ATEXTSPILL.xlsxABCDEFGHIJKLMNOPQRST1Reversed "engineering"2Extract unique values every other 3x4 array, ascending ordernr arrays3=ROWS(B4:E36)/34SheldonBobbyCalvinQuinnembedded lambda ar(i) useful for iteration115KarterCruzJustusBobbyar(2)6CruzMiloYadielRichard=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(2)7JustusRichardYadielSheldonJustusRichardYadielSheldon8CalvinKarterRichardCruzCalvinKarterRichardCruz9CruzBobbyJustusBobbyCruzBobbyJustusBobby10JustusMiloKarterWade11BobbyQuinnCalvinCruzar(4)12SheldonYadielMarkRichard=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(4)13YadielCalvinCruzRichardYadielCalvinCruzRichard14JustusSheldonJustusKarterJustusSheldonJustusKarter15MarkCalvinYadielMarkMarkCalvinYadielMark16RichardJustusQuinnCruz17KarterMiloCalvinMarksolution 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))))19CalvinBobbySheldonJustusBobbyCalvinCruzJustusKarterMiloQuinnRichardSheldonYadiel20SheldonCalvinMiloMarkBobbyCalvinCruzJustusKarterRichardSheldonYadiel21WadeCruzWadeQuinnBobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel22MarkSheldonJustusJustusCalvinCruzJustusKarterMarkRichardSheldonYadiel23QuinnMarkMiloMiloBobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel24QuinnSheldonCalvinBobbyBobbyCalvinCruzJustusMarkMiloQuinnSheldonWade25MarkKarterYadielSheldonBobbyCalvinJustusMarkMiloQuinnSheldon26CruzYadielKarterYadielBobbyCalvinCruzJustusKarterMarkSheldonWadeYadiel27BobbyJustusWadeCalvinCalvinCruzJustusKarterMarkMiloQuinnWadeYadiel28JustusQuinnMarkCalvinBobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel29CruzMarkQuinnYadielCalvinKarterMarkMiloQuinnRichardSheldonWadeYadiel30MarkKarterMiloWadeor31WadeMarkCruzQuinnlambda formula32KarterBobbySheldonRichard=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(INDEX(B4:E36,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1)))33JustusMiloCalvinYadielBobbyCalvinCruzJustusKarterMiloQuinnRichardSheldonYadiel34KarterWadeCalvinSheldonBobbyCalvinCruzJustusKarterRichardSheldonYadiel35SheldonKarterQuinnMiloBobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel36RichardMarkYadielYadielCalvinCruzJustusKarterMarkRichardSheldonYadiel37BobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel38BobbyCalvinCruzJustusMarkMiloQuinnSheldonWade39BobbyCalvinJustusMarkMiloQuinnSheldon40BobbyCalvinCruzJustusKarterMarkSheldonWadeYadiel41CalvinCruzJustusKarterMarkMiloQuinnWadeYadiel42BobbyCalvinCruzJustusKarterMarkMiloQuinnRichardSheldonWadeYadiel43CalvinKarterMarkMiloQuinnRichardSheldonWadeYadiel44ATS 9Cell FormulasRangeFormulaM3,G32,G18,G12,G6M3=FORMULATEXT(M4)M4M4=ROWS(B4:E36)/3G7: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.


----------



## Xlambda (Jun 29, 2022)

ATEXTSPILL.xlsxABCDEFGHIJKLMNOPQR1Same results keeping initial format of source array plus adding index array number2lambda 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))))45SheldonBobbyCalvinQuinnar 1BobbyCalvinCruzJustus6KarterCruzJustusBobbyKarterMiloQuinnRichard7CruzMiloYadielRichardSheldonYadiel8JustusRichardYadielSheldonar 2BobbyCalvinCruzJustus9CalvinKarterRichardCruzKarterRichardSheldonYadiel10CruzBobbyJustusBobbyar 3BobbyCalvinCruzJustus11JustusMiloKarterWadeKarterMarkMiloQuinn12BobbyQuinnCalvinCruzRichardSheldonWadeYadiel13SheldonYadielMarkRichardar 4CalvinCruzJustusKarter14YadielCalvinCruzRichardMarkRichardSheldonYadiel15JustusSheldonJustusKarterar 5BobbyCalvinCruzJustus16MarkCalvinYadielMarkKarterMarkMiloQuinn17RichardJustusQuinnCruzRichardSheldonWadeYadiel18KarterMiloCalvinMarkar 6BobbyCalvinCruzJustus19WadeYadielSheldonBobbyMarkMiloQuinnSheldon20CalvinBobbySheldonJustusWade21SheldonCalvinMiloMarkar 7BobbyCalvinJustusMark22WadeCruzWadeQuinnMiloQuinnSheldon23MarkSheldonJustusJustusar 8BobbyCalvinCruzJustus24QuinnMarkMiloMiloKarterMarkSheldonWade25QuinnSheldonCalvinBobbyYadiel26MarkKarterYadielSheldonar 9CalvinCruzJustusKarter27CruzYadielKarterYadielMarkMiloQuinnWade28BobbyJustusWadeCalvinYadiel29JustusQuinnMarkCalvinar 10BobbyCalvinCruzJustus30CruzMarkQuinnYadielKarterMarkMiloQuinn31MarkKarterMiloWadeRichardSheldonWadeYadiel32WadeMarkCruzQuinnar 11CalvinKarterMarkMilo33KarterBobbySheldonRichardQuinnRichardSheldonWade34JustusMiloCalvinYadielYadiel35KarterWadeCalvinSheldon36SheldonKarterQuinnMilo37RichardMarkYadielYadiel38ATS 10Cell FormulasRangeFormulaB3B3=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.


----------



## Xlambda (Jun 29, 2022)

*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.xlsxABCDEFGHIJKLMNOPQRS1I thought that also adding info about nr. of unique members/each array could be useful23G5:=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)))))45SheldonBobbyCalvinQuinnar 1BobbyCalvinCruzJustuscnt 106KarterCruzJustusBobbyKarterMiloQuinnRichard7CruzMiloYadielRichardSheldonYadiel8JustusRichardYadielSheldonar 2BobbyCalvinCruzJustuscnt 89CalvinKarterRichardCruzKarterRichardSheldonYadiel10CruzBobbyJustusBobbyar 3BobbyCalvinCruzJustuscnt 1211JustusMiloKarterWadeKarterMarkMiloQuinn12BobbyQuinnCalvinCruzRichardSheldonWadeYadiel13SheldonYadielMarkRichardar 4CalvinCruzJustusKartercnt 814YadielCalvinCruzRichardMarkRichardSheldonYadiel15JustusSheldonJustusKarterar 5BobbyCalvinCruzJustuscnt 1216MarkCalvinYadielMarkKarterMarkMiloQuinn17RichardJustusQuinnCruzRichardSheldonWadeYadiel18KarterMiloCalvinMarkar 6BobbyCalvinCruzJustuscnt 919WadeYadielSheldonBobbyMarkMiloQuinnSheldon20CalvinBobbySheldonJustusWade21SheldonCalvinMiloMarkar 7BobbyCalvinJustusMarkcnt 722WadeCruzWadeQuinnMiloQuinnSheldon23MarkSheldonJustusJustusar 8BobbyCalvinCruzJustuscnt 924QuinnMarkMiloMiloKarterMarkSheldonWade25QuinnSheldonCalvinBobbyYadiel26MarkKarterYadielSheldonar 9CalvinCruzJustusKartercnt 927CruzYadielKarterYadielMarkMiloQuinnWade28BobbyJustusWadeCalvinYadiel29JustusQuinnMarkCalvinar 10BobbyCalvinCruzJustuscnt 1230CruzMarkQuinnYadielKarterMarkMiloQuinn31MarkKarterMiloWadeRichardSheldonWadeYadiel32WadeMarkCruzQuinnar 11CalvinKarterMarkMilocnt 933KarterBobbySheldonRichardQuinnRichardSheldonWade34JustusMiloCalvinYadielYadiel35KarterWadeCalvinSheldon36SheldonKarterQuinnMilo37RichardMarkYadielYadiel38ATS 11Cell FormulasRangeFormulaB3B3=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.


----------



## Xlambda (Jun 30, 2022)

Even more complex, adding individual unique count for each array extraction.
*Nested lambda helper MAP inside custom-made lambda helper ATEXTSPILL
ATEXTSPILL.xlsxBCDEFGHIJKLMNOPQRSTUVWX1Adding info about array index nr., nr. of total unique members plus individual each member count for each array extraction2=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)))))45array #, extract unique, total unique countarray #, extract unique, individual count, total unique count67SheldonBobbyCalvinQuinnar 1BobbyCalvinCruzJustuscnt 10ar 1Bobby,2Calvin,1Cruz,2Justus,1cnt 108KarterCruzJustusBobbyKarterMiloQuinnRichardKarter,1Milo,1Quinn,1Richard,19CruzMiloYadielRichardSheldonYadielSheldon,1Yadiel,110JustusRichardYadielSheldonar 2BobbyCalvinCruzJustuscnt 8ar 2Bobby,2Calvin,1Cruz,2Justus,2cnt 811CalvinKarterRichardCruzKarterRichardSheldonYadielKarter,1Richard,2Sheldon,1Yadiel,112CruzBobbyJustusBobbyar 3BobbyCalvinCruzJustuscnt 12ar 3Bobby,1Calvin,1Cruz,1Justus,1cnt 1213JustusMiloKarterWadeKarterMarkMiloQuinnKarter,1Mark,1Milo,1Quinn,114BobbyQuinnCalvinCruzRichardSheldonWadeYadielRichard,1Sheldon,1Wade,1Yadiel,115SheldonYadielMarkRichardar 4CalvinCruzJustusKartercnt 8ar 4Calvin,2Cruz,1Justus,2Karter,1cnt 816YadielCalvinCruzRichardMarkRichardSheldonYadielMark,2Richard,1Sheldon,1Yadiel,217JustusSheldonJustusKarterar 5BobbyCalvinCruzJustuscnt 12ar 5Bobby,1Calvin,1Cruz,1Justus,1cnt 1218MarkCalvinYadielMarkKarterMarkMiloQuinnKarter,1Mark,1Milo,1Quinn,119RichardJustusQuinnCruzRichardSheldonWadeYadielRichard,1Sheldon,1Wade,1Yadiel,120KarterMiloCalvinMarkar 6BobbyCalvinCruzJustuscnt 9ar 6Bobby,1Calvin,2Cruz,1Justus,1cnt 921WadeYadielSheldonBobbyMarkMiloQuinnSheldonMark,1Milo,1Quinn,1Sheldon,222CalvinBobbySheldonJustusWadeWade,223SheldonCalvinMiloMarkar 7BobbyCalvinJustusMarkcnt 7ar 7Bobby,1Calvin,1Justus,2Mark,2cnt 724WadeCruzWadeQuinnMiloQuinnSheldonMilo,2Quinn,2Sheldon,225MarkSheldonJustusJustusar 8BobbyCalvinCruzJustuscnt 9ar 8Bobby,1Calvin,1Cruz,1Justus,1cnt 926QuinnMarkMiloMiloKarterMarkSheldonWadeKarter,2Mark,1Sheldon,1Wade,127QuinnSheldonCalvinBobbyYadielYadiel,328MarkKarterYadielSheldonar 9CalvinCruzJustusKartercnt 9ar 9Calvin,1Cruz,1Justus,1Karter,1cnt 929CruzYadielKarterYadielMarkMiloQuinnWadeMark,3Milo,1Quinn,2Wade,130BobbyJustusWadeCalvinYadielYadiel,131JustusQuinnMarkCalvinar 10BobbyCalvinCruzJustuscnt 12ar 10Bobby,1Calvin,1Cruz,1Justus,1cnt 1232CruzMarkQuinnYadielKarterMarkMiloQuinnKarter,1Mark,1Milo,1Quinn,133MarkKarterMiloWadeRichardSheldonWadeYadielRichard,1Sheldon,1Wade,1Yadiel,134WadeMarkCruzQuinnar 11CalvinKarterMarkMilocnt 9ar 11Calvin,1Karter,2Mark,1Milo,1cnt 935KarterBobbySheldonRichardQuinnRichardSheldonWadeQuinn,1Richard,1Sheldon,2Wade,136JustusMiloCalvinYadielYadielYadiel,237KarterWadeCalvinSheldon38SheldonKarterQuinnMilo39RichardMarkYadielYadiel40ATS 11Cell FormulasRangeFormulaB2B2=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.*


----------



## Xlambda (Jul 3, 2022)

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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBDBE1unicodessuits29824♠Deal 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}59830♦234567891011121314623456789101112131472345678910111213148234567891011121314910=SWITCH(E5#,11,"A",12,"J",13,"Q",14,"K",E5#)112345678910AJQK122345678910AJQK132345678910AJQK142345678910AJQK1516=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♦2122deck in order23=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♦2526shuffling (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♦2930deal the whole deck 5 cards/round(hand)7 cards13 cardschecking no dups31=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♦111111111111133K♣6♦5♦8♦4♣5♦8♦4♣4♥10♥9♦5♥5♥J♣A♦6♥2♦3♠8♣J♥3♥7♣A♠K♠3♦1111111111111344♥10♥9♦5♥J♣J♣A♦6♥2♦3♠8♣J♥8♥6♠8♠K♦J♠9♠A♣Q♠2♠J♦10♦Q♣3♣111111111111135A♦6♥2♦3♠8♣3♥7♣A♠K♠3♦8♥6♠9♥4♦9♣5♠Q♥K♥10♣2♥10♠4♠7♥2♣7♦111111111111136J♥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♦43DECK 1Cell FormulasRangeFormulaC2: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 FormattingCellConditionCell FormatStop If True1:1048576Expression=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)textNO


----------



## Xlambda (Jul 3, 2022)

*Shuffling n decks of cards with ATEXTSPILL 
DECK()* argument free lambda, returns a deck of cards in order (row orientation)

```
=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)

```
=LAMBDA([n],
    IF(
        n,
        ATEXTSPILL(SEQUENCE(MAX(1, n)), LAMBDA(x, SORTBY(DECK(), RANDARRAY(, 52)))),
        DECK()
    )
)
```
ATEXTSPILL.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABB12=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♦45n,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♦89n <0 => n=110=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♦1213n,1, => 1 deck shuffled14=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♠1617n,>0 => n decks shuffled, each row represent a separate shuffled deck => no dups /row18=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♦2223checking dups/row. If all 1 => no dups/row24B21:B23=COUNTIF(B19:BA19,DECK())25111111111111111111111111111111111111111111111111111126111111111111111111111111111111111111111111111111111127111111111111111111111111111111111111111111111111111128DECK 2Cell FormulasRangeFormulaB2,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 FormattingCellConditionCell FormatStop If True1:1048576Expression=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)textNO


----------



## Xlambda (May 30, 2021)

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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1TEXTJOINATEXTSPLIT2empty ignoredempty not ignored2 charsempty ignored3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=04sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check5a4ca,4,,ca4ca4ca><6.8><ta6.8t1316d0.5d,,,0.5d0.5d0.5><2.4  w><2.4  w6007g hi 2k  l3 ng h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n<Ac4><gh>< ><<Ac4gh 4218space9empty ignoredea arg.=0ea arg.=1empty not ignored10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=111a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check12d,0.5d0.5d0.5a6.8t131013g h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n2.4  w060014<Ac4gh 421015Complex scenariospace followed by an empty string16empty ignoredempty not ignored17ea 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 chars20,,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 chars22, ,,x   4, ,, x   4  x   4 2324Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument25                            -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument26                       We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)2728=LEN(H19#)=LEN(N19#)291113311130303011130001011331413110413110321510001051003334- 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 used3536ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)37fixed with 4 pos.a bc/a  b/ bcda bca  b bcd44438/____ /ab d/   d/a   ab d   da   44439/xxxx/check:pattern is kept40ATEXTSPLIT postCell FormulasRangeFormulaH4,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.


----------



## Xlambda (Jul 4, 2022)

What if we have 2 or more decks, each shuffled, NDECK(n ) (no dups/row) that we want to shuffle them, riffle style. (see attached pic)
*Shuffling riffle style. Concept.*
ATEXTSPILL.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABB1Shuffling n decks. Riffle style. Concept23=NDECK(2)42♣9♣10♦4♦4♠A♣J♣6♠3♦2♠6♣5♠J♥8♥Q♣10♠8♦8♠7♦A♦5♣10♥A♥A♠9♥K♣4♣2♦3♣K♠9♦Q♥3♠K♦9♠2♥6♦Q♠10♣8♣7♣7♥Q♦4♥J♦3♥6♥7♠J♠5♦K♥5♥56♦Q♦A♦9♥2♠10♥8♠7♦J♣2♥4♠K♦7♠3♠3♣7♥2♣5♦3♦A♥10♣3♥5♣8♦10♦9♠10♠A♣9♦4♣J♦K♠K♣6♥Q♥4♥A♠7♣J♠K♥6♠Q♣6♣5♠Q♠4♦8♣8♥5♥J♥9♣2♦67shuffling 2 decks, riffle style, possible if we use scan_by_colum argument = 1 in TOCOL8=WRAPROWS(TOCOL(B4#,,1),52)92♣6♦9♣Q♦10♦A♦4♦9♥4♠2♠A♣10♥J♣8♠6♠7♦3♦J♣2♠2♥6♣4♠5♠K♦J♥7♠8♥3♠Q♣3♣10♠7♥8♦2♣8♠5♦7♦3♦A♦A♥5♣10♣10♥3♥A♥5♣A♠8♦9♥10♦K♣9♠104♣10♠2♦A♣3♣9♦K♠4♣9♦J♦Q♥K♠3♠K♣K♦6♥9♠Q♥2♥4♥6♦A♠Q♠7♣10♣J♠8♣K♥7♣6♠7♥Q♣Q♦6♣4♥5♠J♦Q♠3♥4♦6♥8♣7♠8♥J♠5♥5♦J♥K♥9♣5♥2♦111213NDECK(2) has no dups /row14shufflled decks should/can have dups/row15B16:B17=COUNTIF(B9:BA9,DECK())1621211121110002102100111211110001122210002111220220111701011101112220120122111011112221100012220111002002111819also total nr. of occurences should be = total nr. of decks (2), no more, no less20=COUNTIF(B9#,DECK())2122222222222222222222222222222222222222222222222222222223Shuffling 3 decks2425=NDECK(3)26A♦Q♣5♥8♥8♠K♠2♥9♥K♥5♦7♠A♣J♦J♣6♣2♠4♣A♠10♣6♥7♦6♦4♦4♥7♣9♠9♦Q♦9♣2♦3♣3♠6♠J♥10♠Q♠K♦5♣K♣7♥5♠3♥3♦A♥J♠8♣8♦Q♥4♠2♣10♥10♦276♦6♥6♣4♥Q♣7♥3♣J♥3♠2♠K♦Q♦3♥5♠4♠2♥3♦A♠8♦10♠Q♥A♦10♥8♠Q♠A♣J♠8♣4♦8♥7♠4♣K♠J♦5♣K♥5♥10♦5♦9♣A♥2♣10♣6♠7♣J♣K♣9♥7♦9♦9♠2♦284♠8♣7♠9♦10♠K♣A♦6♥6♠10♦10♣8♠2♦7♦K♠6♦3♣5♣A♠Q♥5♦3♠4♣A♣8♥6♣7♥9♥2♠Q♦Q♠10♥J♣3♦2♣2♥A♥5♠4♥9♠J♥7♣J♠5♥J♦9♣3♥K♥8♦K♦Q♣4♦2930=WRAPROWS(TOCOL(B26#,,1),52)31A♦6♦4♠Q♣6♥8♣5♥6♣7♠8♥4♥9♦8♠Q♣10♠K♠7♥K♣2♥3♣A♦9♥J♥6♥K♥3♠6♠5♦2♠10♦7♠K♦10♣A♣Q♦8♠J♦3♥2♦J♣5♠7♦6♣4♠K♠2♠2♥6♦4♣3♦3♣A♠32A♠5♣10♣8♦A♠6♥10♠Q♥7♦Q♥5♦6♦A♦3♠4♦10♥4♣4♥8♠A♣7♣Q♠8♥9♠A♣6♣9♦J♠7♥Q♦8♣9♥9♣4♦2♠2♦8♥Q♦3♣7♠Q♠3♠4♣10♥6♠K♠J♣J♥J♦3♦10♠5♣332♣Q♠K♥2♥K♦5♥A♥5♣10♦5♠K♣5♦4♥7♥9♣9♠5♠A♥J♥3♥2♣7♣3♦10♣J♠A♥6♠5♥J♠7♣J♦8♣J♣9♣8♦K♣3♥Q♥9♥K♥4♠7♦8♦2♣9♦K♦10♥9♠Q♣10♦2♦4♦3435cheching total occurences = 336=COUNTIFS(B31#,DECK())37333333333333333333333333333333333333333333333333333338DECK 3Cell FormulasRangeFormulaB3,B36,B30,B25,B20,B8B3=FORMULATEXT(B4)B4:BA5B4=NDECK(2)B9:BA10,B31:BA33B9=WRAPROWS(TOCOL(B4#,,1),52)E15E15=FORMULATEXT(B16)B16:BA17B16=COUNTIF(B9:BA9,DECK())B21:BA21B21=COUNTIF(B9#,DECK())B26:BA28B26=NDECK(3)B37:BA37B37=COUNTIFS(B31#,DECK())Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If True1:1048576Expression=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)textNO


----------



## Xlambda (Jul 4, 2022)

*SNDECK([nd],[ch],[nh])* Shuffling nd decks of cards and dealing them, nr. of cards/hand ch, nh number of hands (gets calculated)
*[nd]*: number of decks
if omitted => 1 deck
*[ch]*: cards/hand
if omitted => all cards in a single hand => 1 row, nd*52 cards (columns)
*[nh]*: nr. of hands gets calculated according to total nr of cards and cards/hand ch 
if nh omitted => all hands to finish all cards, last hand can have <= ch, total nr of hands = roundup(nd*52/ch,0)
if nh< total nr hands, only the first nh will be returned
if nh=-1, last hand if it has <ch nr of cards, will not be returned => returns only hands that have ch cards
if nh> tot nh => nh=total nh
The function will return as top left cell info about nr. of rows(nh) x nr. of columns (ch)

```
=LAMBDA([nd], [ch], [nh],
    LET(
        m, MAX(nd, 1) * 52,
        d, NDECK(nd),
        f, SORTBY(TOCOL(d, , 1), RANDARRAY(m)),
        c, IF(ch, MIN(ch, m), m),
        a, IF(c = m, TOROW(f), WRAPROWS(f, c, "")),
        r, ROWS(a),
        b, IF(OR(a = ""), TAKE(a, r - 1), a),
        x, IFS(nh < 0, b, nh = 0, a, nh < r, TAKE(b, nh), 1, a),
        IFNA(VSTACK(ROWS(x) & "x" & COLUMNS(x), x), "")
    )
)
```
ATEXTSPILL.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBA12=SNDECK()31x524K♠J♣Q♦2♦A♠2♣9♦4♥J♠4♦Q♠6♠3♥10♣A♦K♦9♠6♦A♣4♣7♠10♠8♣2♥10♦4♠9♥6♥J♦7♣3♦8♠Q♣6♣10♥5♦K♣3♣Q♥8♦5♣7♥7♦9♣J♥K♥3♠8♥A♥5♠2♠5♥56nd,1 (single deck), ch,5nh,-1 => last row not returnednd,2,ch,7nd,2,ch,7,nh,-1nd,2,ch,7,nh,107=SNDECK(1,5)=SNDECK(1,5,-1)=SNDECK(2,7)=SNDECK(2,7,-1)=SNDECK(3,7,10)811x510x515x714x710x795♣7♣A♣10♦Q♣8♦5♥6♠Q♦K♣8♦9♦3♠7♦A♠10♦A♦7♣8♣10♣10♦6♠4♠6♥4♣9♦A♥7♦10♥A♣9♣104♠6♠9♣2♣K♥8♥5♠5♣2♦4♥2♠J♣3♥5♦J♦2♠3♦A♦Q♦Q♣5♥9♠4♣9♣5♠Q♦K♦K♥J♣6♥9♥11A♠Q♠5♠7♥5♦9♦9♥4♠J♦6♦4♠5♠4♦7♠5♦A♣10♥5♠K♦J♦9♦J♣Q♥9♣10♦K♠5♣Q♥8♥8♣Q♠122♥2♠3♣5♥6♣J♥9♠Q♥2♠J♣10♠7♥9♥9♦Q♠8♠J♥5♦A♣4♥3♥5♠A♦K♠2♥J♣8♠K♣J♠8♠A♣1310♠8♥K♦2♦9♦7♣A♥A♠7♦A♣4♥8♦6♦A♣K♣K♠4♦4♣2♥J♣10♥J♥9♥9♥4♦J♠6♦K♦2♠A♦8♦144♦J♦3♠J♣10♥K♥A♦9♣2♥K♦Q♦10♠5♥8♣6♦J♦2♦7♦2♣10♣2♦10♠3♦8♦Q♥7♠2♦K♥3♣A♦5♣15J♥A♥10♣3♥Q♦K♠3♠10♣3♥8♣7♥Q♣K♣6♣J♥6♣Q♦4♦8♥6♥Q♦6♠4♦8♣K♥7♣4♠5♠9♠2♣A♣163♦K♣9♥8♠8♣Q♠10♥10♠6♥8♠6♥6♥K♦8♥2♥4♣9♠10♠7♠3♥2♠9♠K♥K♥8♣10♣J♥4♥3♥8♦5♥176♥9♠4♣4♥Q♥Q♣4♦4♣5♦2♣9♣Q♥4♣8♣J♣A♥K♦3♣A♥7♣2♦8♠4♥9♦6♠5♥3♦5♠4♥10♥J♥188♦7♦6♦J♠7♠J♠7♠3♦10♦6♣3♣7♦10♣9♣7♣3♥9♠8♦J♥5♣7♠K♣7♦2♠Q♥6♣9♥10♠3♦8♥10♠19K♠A♦5♥9♥J♠4♠3♣2♣8♠A♠3♦Q♥8♠A♣3♣K♠207♣A♠2♣5♣10♥3♦K♥6♦J♠8♥7♥2♣K♦3♠2110♦Q♠4♥6♠A♥2♥J♠10♦A♠4♠2♥Q♣10♥J♠2210♣2♦8♥A♦K♥5♠7♠Q♠3♠6♣7♥J♦Q♠5♥23Q♣Q♥K♠3♠5♣6♠2425nd,3,ch,13,nh,omitted (all hands complete,tot cards divide by ch=13)since all 3 decks are shuffled separate and toghether also,26=SNDECK(3,13)rows can have dups, extracting unique by row2712x13=ATEXTSPILL(SEQUENCE(52*3/13),LAMBDA(x,UNIQUE(INDEX(B28:N39,x,),1)))285♦7♣2♦9♥K♥9♥7♣2♦A♣8♦7♠A♥4♣5♦7♣2♦9♥K♥A♣8♦7♠A♥4♣297♦10♠2♥3♣2♦K♣9♥6♦Q♥5♠3♥8♣4♦7♦10♠2♥3♣2♦K♣9♥6♦Q♥5♠3♥8♣4♦30K♥Q♠A♥8♥Q♦J♦K♦10♣K♠8♠A♣3♣9♣K♥Q♠A♥8♥Q♦J♦K♦10♣K♠8♠A♣3♣9♣3110♥J♣J♠8♦3♠5♥10♦2♣4♦3♠8♠6♦9♦10♥J♣J♠8♦3♠5♥10♦2♣4♦8♠6♦9♦324♠5♦Q♣5♣Q♥Q♦4♣8♠3♣6♠3♦J♥10♦4♠5♦Q♣5♣Q♥Q♦4♣8♠3♣6♠3♦J♥10♦336♥8♥6♥4♥Q♣9♣4♣5♦2♣7♥K♠Q♦6♠6♥8♥4♥Q♣9♣4♣5♦2♣7♥K♠Q♦6♠3410♥9♦3♥K♣K♥J♣J♥A♠2♣6♦5♠2♥7♥10♥9♦3♥K♣K♥J♣J♥A♠2♣6♦5♠2♥7♥353♦7♠8♣4♠J♠3♦7♠5♠7♦6♣8♥J♦9♠3♦7♠8♣4♠J♠5♠7♦6♣8♥J♦9♠365♥10♣4♥5♥A♠4♦2♥7♦Q♠K♦J♣10♣A♦5♥10♣4♥A♠4♦2♥7♦Q♠K♦J♣A♦37Q♣4♠6♠2♠3♠5♣10♠9♣10♥A♦7♣7♥A♣Q♣4♠6♠2♠3♠5♣10♠9♣10♥A♦7♣7♥A♣38A♥J♠9♦9♠6♥K♣3♥Q♠A♠4♥5♣K♦8♣A♥J♠9♦9♠6♥K♣3♥Q♠A♠4♥5♣K♦8♣399♠8♦6♣J♥J♦Q♥K♠2♠A♦2♠6♣10♠10♦9♠8♦6♣J♥J♦Q♥K♠2♠A♦10♠10♦40DECK 4Cell FormulasRangeFormulaB2,P27,B26,AE7,W7,O7,H7,B7B2=FORMULATEXT(B3)B3:BA4B3=SNDECK()B8:F19B8=SNDECK(1,5)H8:L18H8=SNDECK(1,5,-1)O8:U23O8=SNDECK(2,7)W8:AC22W8=SNDECK(2,7,-1)AE8:AK18AE8=SNDECK(3,7,10)B27:N39B27=SNDECK(3,13)P28:AB39P28=ATEXTSPILL(SEQUENCE(52*3/13),LAMBDA(x,UNIQUE(INDEX(B28:N39,x,),1)))Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If True1:1048576Expression=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)textNO


----------



## Xlambda (Jul 4, 2022)

ATEXTSPILL.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABB1RECAP23DECK() single deck in order4=DECK()52♠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♦67NDECK(n) shuffles n decks of cards individually (no dups/row)8=NDECK(4)96♥Q♥3♦7♠5♣9♥A♠Q♦3♣7♣J♦8♥Q♠K♥2♥5♠6♣6♦J♥10♦A♦4♥K♦Q♣10♣9♦9♣8♣J♣A♣K♠4♣4♦5♦3♥8♠J♠2♦9♠2♣5♥8♦7♥K♣10♥3♠4♠A♥10♠7♦2♠6♠1010♣7♣5♣10♠10♥4♣2♥K♦2♦J♠4♠2♠3♣K♥5♥4♦5♦8♦A♠6♥6♦7♠6♣Q♠Q♣3♠9♣K♠Q♥3♥2♣6♠A♦9♥8♠8♥J♥A♣10♦9♦Q♦4♥3♦9♠5♠J♦J♣K♣7♥7♦A♥8♣11A♠5♦7♦8♦4♥3♣Q♣K♦10♣9♠5♥J♠Q♠5♣10♠10♦2♣6♦3♦4♦9♦6♠5♠8♥K♥3♠J♥J♦2♦Q♦6♣A♥K♣9♣6♥8♠4♠3♥2♠2♥4♣8♣7♠7♥J♣K♠9♥A♣A♦Q♥7♣10♥12K♦A♦2♦2♠A♠10♠3♠6♥Q♦5♦4♣9♥3♥6♠4♥5♠6♣8♠K♣A♥Q♠3♣8♣3♦4♠7♦Q♣4♦9♣A♣10♥9♦8♦7♠K♥J♦Q♥5♣5♥J♣8♥7♥K♠10♣2♥J♠10♦2♣9♠J♥6♦7♣1314count occurrences/rowcount all occurrences1552=SUM(COUNTIF(B9:BA9,DECK()))=SUM(COUNTIF(B9#,DECK()))1652=SUM(COUNTIF(B10:BA10,DECK()))2081752=SUM(COUNTIF(B11:BA11,DECK()))=4*521852=SUM(COUNTIF(B12:BA12,DECK()))2081920SNDECK([nd],[ch],[nh]), shuffles and deals nh hands of ch cards/hand21the function does 3 shuffles, 1st done by NDECK function, each deck individually, 2nd riffle shuffle with TOCOL, 3rd shuffle another sortby with randarray2223LAMBDA([nd], [ch], [nh],
    LET(
        m, MAX(nd, 1) * 52,
        d, NDECK(nd),
        f, SORTBY(TOCOL(d, , 1), RANDARRAY(m)),242526←←1st shuffle2728↑↑↑↑↑292nd shuffle3rd shuffle30riffle shuffle31can have dups/row32=SNDECK(4,13)count all occurrencescount unique/row3316x13=SUM(COUNTIF(B34:N49,DECK()))=BYROW(B34:N49,LAMBDA(x,COLUMNS(UNIQUE(x,1))))34A♦9♣10♠4♥K♠8♠8♣4♣7♠Q♦7♣8♥4♣20812TRUE356♣A♦2♣2♥8♥7♦6♥5♣7♠K♣6♦4♠A♥13FALSE369♠8♦9♣8♣Q♠4♥6♦J♦3♥4♥5♥A♠9♠11TRUE37K♦2♦8♥A♥K♠7♦9♠6♠10♣K♥4♣6♥3♥13FALSE385♠8♦J♥A♠3♣Q♣3♥8♣J♣5♠10♦5♥Q♣11TRUE39J♠10♦Q♠2♠J♠9♥5♦10♦8♥Q♥3♦J♥Q♥10TRUE402♦6♣J♦K♦5♣4♦6♦Q♥A♠2♥6♠9♦10♣13FALSE417♦J♥A♠5♣9♣3♦5♣8♠10♠J♠A♣4♠2♣12TRUE4210♥A♦3♠4♠Q♥7♠K♠6♦J♣2♦6♣9♠A♦12TRUE438♦J♠K♦Q♣3♣2♥8♠7♠8♣A♥7♣5♦A♣13FALSE44Q♣7♥4♥5♠3♠J♦7♥9♥2♠2♦K♥2♠2♠10TRUE45Q♦3♣Q♠K♣7♣3♦4♠K♣3♣9♦8♦6♥5♥11TRUE46K♣5♠3♠K♦9♥3♠10♥10♥4♦6♠7♦10♣2♥11TRUE47A♥K♥2♣5♥J♣9♦10♣K♠6♣3♦9♦3♥10♥12TRUE484♦5♦Q♦Q♦5♦4♦7♥9♣Q♠J♣7♥10♦A♣9TRUE494♣6♥6♠K♥J♥J♦2♣10♠9♥10♠8♠7♣A♣12TRUE50=V34#<1351rows with dups52DECK 5Cell FormulasRangeFormulaB4,P33,V33,B32,J17,J15,B8B4=FORMULATEXT(B5)B5:BA5B5=DECK()B9:BA12B9=NDECK(4)B15:B18B15=SUM(COUNTIF(B9:BA9,DECK()))C15:C18C15=FORMULATEXT(B15)J16J16=SUM(COUNTIF(B9#,DECK()))J18J18=4*52B33:N49B33=SNDECK(4,13)P34P34=SUM(COUNTIF(B34:N49,DECK()))V34:V49V34=BYROW(B34:N49,LAMBDA(x,COLUMNS(UNIQUE(x,1))))X34:X49X34=V34#<13X50X50=FORMULATEXT(X34)Dynamic array formulas.Cells with Conditional FormattingCellConditionCell FormatStop If True1:1048576Expression=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)textNOX34:X49Expression=X34textNO


----------



## Xlambda (Jul 10, 2022)

*HowToSplitASentenceWithNoDelimiters?*
This is inspired by an off-topic comment posted by *B C* on latest Chandoo's YT: Data Cleaning PRO Tip: Dealing with text & numbers in same column
B C's post:
*"Thanks, Chandoo!  Question: HowWouldYouAddSpacesAtCapitalLetters?"*
text-and-values.xlsxABCDEFGHIJKLM12sample3HowToSplitASentenceWithNoDelimiters?4HowWouldYouAddSpacesAtCapitalLetters?5Axxx,Bxx,CDE,FxG67capital letters formula8=CHAR(SEQUENCE(26,,65))9A10B1st solution, when all capital letters belong to standard alphabet11C12D=REDUCE(C3:C5,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,TRIM(SUBSTITUTE(v,x," "&x))))13EHow To Split A Sentence With No Delimiters?14FHow Would You Add Spaces At Capital Letters?15GAxxx, Bxx, C D E, Fx GTo extract the words16H=ATEXTSPILL(C13#,LAMBDA(x,TEXTSPLIT(x,{" ",",","?"},,1)))17IHowToSplitASentenceWithNoDelimiters18JHowWouldYouAddSpacesAtCapitalLetters19KAxxxBxxCDEFxG20L21Mor this extraction22N=IF(SEQUENCE(,COLUMNS(E17#))=1,E17#,LOWER(E17#))23OHowtosplitasentencewithnodelimiters24PHowwouldyouaddspacesatcapitalletters25QAxxxbxxcdefxg26R27S28T29U30V31W32X33Y34Z35cap lett. 1Cell FormulasRangeFormulaA8,E22,E16,C12A8=FORMULATEXT(A9)A9:A34A9=CHAR(SEQUENCE(26,,65))C13:C15C13=REDUCE(C3:C5,CHAR(SEQUENCE(26,,65)),LAMBDA(v,x,TRIM(SUBSTITUTE(v,x," "&x))))E17:L19E17=ATEXTSPILL(C13#,LAMBDA(x,TEXTSPLIT(x,{" ",",","?"},,1)))E23:L25E23=IF(SEQUENCE(,COLUMNS(E17#))=1,E17#,LOWER(E17#))Dynamic array formulas.


----------



## Xlambda (Jul 10, 2022)

text-and-values.xlsxABCDEFGHIJKLMN1single cell2Äx,xBbxçÇydË,GÓö?2nd solution (Chandoo's idea to use EXACT)3this we should use it when we have "unusual" letters45for a single cell6=LET(x,B2,m,MID(x,SEQUENCE(LEN(x)),1),TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m))," "&m,m))))7Äx ,x Bbxç Çyd Ë , G Óö ?8Note:9punctuation marks behavior with EXACT10=EXACT(F11:F15,UPPER(F11:F15))11?TRUE12,TRUE13:TRUE14-TRUE15(TRUE1617for that reason will have a " " before any delimiter in D71819=TEXTSPLIT(D7,{" ",",","?"},,1)20ÄxxBbxçÇydËGÓö21cap lett. 2Cell FormulasRangeFormulaD6,F19,H10D6=FORMULATEXT(D7)D7D7=LET(x,B2,m,MID(x,SEQUENCE(LEN(x)),1),TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m))," "&m,m))))H11:H15H11=EXACT(F11:F15,UPPER(F11:F15))F20:L20F20=TEXTSPLIT(D7,{" ",",","?"},,1)Dynamic array formulas.


----------



## Xlambda (Jul 10, 2022)

text-and-values.xlsxABCDEFGHIJKLMNOPQRSTUV1What if the "sentence" contains numbers, or decimal numbers.2EXACT/UPPER construction(delivers true's) will add a " " space before any digit and any decimal point, like with any punctuation mark (to anything that is not a letter)34=EXACT(B5:B7,UPPER(B5:B7))51TRUE63TRUEsample single cell7.TRUEÀáa35,Ññxx21.03,Òô12.589old formula (previous formula)10=LET(x,F7,m,MID(x,SEQUENCE(LEN(x)),1),TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m))," "&m,m))))11Àáa 3 5 , Ññxx 2 1 . 0 3 , Òô 1 2 . 512extracting "words" will not keep the integrated numbers together13=TEXTSPLIT(F11,{" ",","},,1)14Àáa35Ññxx21.03Òô12.51516amended formula to deal with numbers (digits and decimal points)1718=LET(x,F7,m,MID(x,SEQUENCE(LEN(x)),1),TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m)),IF(ISNUMBER(--m),"",IF(m=".",""," "))&m,m))))19Àáa35 , Ññxx21.03 , Òô12.520numbers, decimal numbers kept together21=TEXTSPLIT(F19,{" ",","},,1)22Àáa35Ññxx21.03Òô12.523cap lett. 3Cell FormulasRangeFormulaD4,H21,F18,H13,F10D4=FORMULATEXT(D5)D5:D7D5=EXACT(B5:B7,UPPER(B5:B7))F11F11=LET(x,F7,m,MID(x,SEQUENCE(LEN(x)),1),TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m))," "&m,m))))H14:U14,H22:J22H14=TEXTSPLIT(F11,{" ",","},,1)F19F19=LET(x,F7,m,MID(x,SEQUENCE(LEN(x)),1),TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m)),IF(ISNUMBER(--m),"",IF(m=".",""," "))&m,m))))Dynamic array formulas.


----------



## Xlambda (Jul 10, 2022)

text-and-values.xlsxABCDEFGHIJKLMNOP12D array sample,(there are spaces " " and "," here and there that will not affect the results)2Àáa35Ññxx21.03 Òô12.5Àáa0.35,Ññxx21.03 Òô12.53Àáa35.7, Ññxx8.03,Òô12.57 =""4 Àáa35.8Ññxx0.23Òô12 empty string5blank6If we have a formula that works for a single cell,7with MAP we can apply it to an entire array/range89=MAP(B2:C4,LAMBDA(x,LET(m,MID(x,SEQUENCE(LEN(x)),1),IFERROR(TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m)),IF(ISNUMBER(--m),"",IF(m=".",""," "))&m,m))),""))))10Àáa35 Ññxx21.03 Òô12.5Àáa0.35 , Ññxx21.03 Òô12.511Àáa35.7 , Ññxx8.03 , Òô12.5712Àáa35.8 Ññxx0.23 Òô12single cell formula13to extract words (no need of IFERROR inside MAP, ATEXTSPILL can handle it)1415Àáa35Ññxx21.03Òô12.516Àáa0.35Ññxx21.03Òô12.517Àáa35.7Ññxx8.03Òô12.57181920Àáa35.8Ññxx0.23Òô122122E15:23 =LET(
   ar,B2:C4,
   a,TOCOL(IF(ar="","",ar)),
   ATEXTSPILL(a,LAMBDA(x,
       LET(
         m,MID(x,SEQUENCE(LEN(x)),1),
         y,TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m)),IF(ISNUMBER(--m),"",IF(m=".",""," "))&m,m))),
        TEXTSPLIT(y,{" ",",","?"},,1)
             )
                      )
      )
)242526272829303132333435cap lett. 4Cell FormulasRangeFormulaC3,E4C3=""E3,B9E3=FORMULATEXT(E4)B10:C12B10=MAP(B2:C4,LAMBDA(x,LET(m,MID(x,SEQUENCE(LEN(x)),1),IFERROR(TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m)),IF(ISNUMBER(--m),"",IF(m=".",""," "))&m,m))),""))))E15:G20E15=LET(ar,B2:C4,a,TOCOL(IF(ar="","",ar)),ATEXTSPILL(a,LAMBDA(x,LET(m,MID(x,SEQUENCE(LEN(x)),1),y,TRIM(TEXTJOIN("",,IF(EXACT(m,UPPER(m)),IF(ISNUMBER(--m),"",IF(m=".",""," "))&m,m))),TEXTSPLIT(y,{" ",",","?"},,1)))))Dynamic array formulas.


----------



## Xlambda (Jul 10, 2022)

text-and-values.xlsxABCDEFGHIJKLMN1single cell sample3rd solution concept, check for letters2Àáa35Ññxx21.03 Òô12.5 - for a char "l", if exact(lower(l),upper(l)) = false => "l" is a letter3=> if not(exact(lower(l),upper(i))) = true => "l" is a letter45=MID(B2,SEQUENCE(LEN(B2)),1)6↓↓check for letters7↓↓=NOT(EXACT(LOWER(D11#),UPPER(D11#)))8↓↓↓↓=EXACT(D11#,UPPER(D11#))9↓↓↓↓↓↓if 1, is letter and is upper10↓↓↓↓↓↓=H11#*F11#=IF(J11#," "&D11#,D11#)11ÀTRUETRUE1 À12áTRUEFALSE0á13aTRUEFALSE0a143FALSETRUE03155FALSETRUE0516ÑTRUETRUE1 Ñ17ñTRUEFALSE0ñ18xTRUEFALSE0x19xTRUEFALSE0x202FALSETRUE02211FALSETRUE0122.FALSETRUE0.230FALSETRUE00243FALSETRUE0325 FALSETRUE0 26ÒTRUETRUE1 Ò27ôTRUEFALSE0ô281FALSETRUE01292FALSETRUE0230.FALSETRUE0.315FALSETRUE053233=TEXTJOIN("",,L11#)34 Àáa35 Ññxx21.03  Òô12.535cap lett. 5Cell FormulasRangeFormulaD5D5=FORMULATEXT(D11)F7F7=FORMULATEXT(F11)H8H8=FORMULATEXT(H11)J10,L10,B33J10=FORMULATEXT(J11)D11:D31D11=MID(B2,SEQUENCE(LEN(B2)),1)F11:F31F11=NOT(EXACT(LOWER(D11#),UPPER(D11#)))H11:H31H11=EXACT(D11#,UPPER(D11#))J11:J31J11=H11#*F11#L11:L31L11=IF(J11#," "&D11#,D11#)B34B34=TEXTJOIN("",,L11#)Dynamic array formulas.


----------



## Xlambda (Jul 10, 2022)

text-and-values.xlsxABCDEFGHIJKLMN122D sample3Àáa35Ññxx21.03 Òô12.5?Àáa0.35,Ññxx21.03 Òô12.5Àáa35Ññxx21.03Òô12.54Àáa35.7, Ññxx8.03,Òô12.57 Àáa0.35Ññxx21.03Òô12.55 Àáa35.8Ññxx0.23Òô12 ?Àáa35.7Ññxx8.03Òô12.57678Àáa35.8Ññxx0.23Òô129E3:10 =LET(
       ar,B2:C4,
       a,TOCOL(IF(ar="","",ar)),
      ATEXTSPILL(a,LAMBDA(x,LET(
                                              m,MID(x,SEQUENCE(LEN(x)),1),
                                              y,TEXTJOIN("",,IF(NOT(EXACT(LOWER(m),UPPER(m)))*EXACT(m,UPPER(m))," "&m,m)),
                                              TEXTSPLIT(y,{" ",",","?"},,1)
                                                       )
                          )
      )
)111213141516171819202122cap lett. 6Cell FormulasRangeFormulaE3:G8E3=LET(ar,B3:C5,a,TOCOL(IF(ar="","",ar)),ATEXTSPILL(a,LAMBDA(x,LET(m,MID(x,SEQUENCE(LEN(x)),1),y,TEXTJOIN("",,IF(NOT(EXACT(LOWER(m),UPPER(m)))*EXACT(m,UPPER(m))," "&m,m)),TEXTSPLIT(y,{" ",",","?"},,1)))))C4C4=""Dynamic array formulas.


----------



## Xlambda (Jul 10, 2022)

For fun, *single cell formula alternative to the PQ solution*, main subject of the YT.
text-and-values.xlsxABCDEFGHIJKLMN12sample, shorter than original raw data in Chandoo's workbooksingle cell formula to replace PQ3DateCompanyPerson NameRoom numberDateCompanyPerson NameRoom numberText Value401-Jan-22AvambaAnatole Ridehalgh400801-01-22AvambaAnatole Ridehalgh4008Hotels501-Jan-22FatzAldrich McKevin200201-01-22FatzAldrich McKevin2002Hotels601-Jan-22LeexoStanley Hadrill401201-01-22LeexoStanley Hadrill4012Hotels7Hotels01-01-22RhyzioLyndell Tice1006Booking801-Jan-22RhyzioLyndell Tice100601-01-22EadelBroderic Handscombe3015Booking901-Jan-22EadelBroderic Handscombe301501-01-22OozzDeina Harwin2005Booking1001-Jan-22OozzDeina Harwin200501-01-22InnotypeBenyamin Crocetti2001Booking1101-Jan-22InnotypeBenyamin Crocetti200101-01-22Thea Melan5006Booking1201-Jan-22Thea Melan500601-01-22Alexei Kneale3002Booking1301-Jan-22Alexei Kneale300201-01-22JayoTate Manntschke3014Booking1401-Jan-22JayoTate Manntschke301401-01-22Eudora Nettle6003Cleartrip15Booking01-01-22RiffpediaElianore Vigar3002Cleartrip1601-Jan-22Eudora Nettle600301-01-22TazzAlonso Mundee4006Cleartrip1701-Jan-22RiffpediaElianore Vigar300201-01-22BrowsezoomYsabel Lordon6003Hotels1801-Jan-22TazzAlonso Mundee400601-01-22SkinteRaff Verecker3012Hotels19Cleartrip01-01-22Drusi Loomis2005Hotels2001-Jan-22BrowsezoomYsabel Lordon600301-01-22TwitterlistAndrea Humpatch4005Hotels2101-Jan-22SkinteRaff Verecker301201-01-22ShuffletagCammy Curle2016Expedia2201-Jan-22Drusi Loomis200501-01-22GebaHowey Oseman6001Expedia2301-Jan-22TwitterlistAndrea Humpatch400501-01-22Kendall Skynner6003Expedia24Hotels01-01-22FlipopiaPrince Coppenhall2002Booking2501-Jan-22ShuffletagCammy Curle201601-01-22TopicblabNoni Tarbett1012Booking2601-Jan-22GebaHowey Oseman600101-01-22FeedmixBondon Tuny3007Booking2701-Jan-22Kendall Skynner600301-01-22SkynoodleAndros Cathcart1008Booking28Expedia01-01-22MitaAubert Racher4004Travel Agent 0072901-Jan-22FlipopiaPrince Coppenhall200201-01-22SkinixCurcio Lewis3008Travel Agent 0073001-Jan-22TopicblabNoni Tarbett101201-01-22AimbuWeider Brookz1001Travel Agent 0073101-Jan-22FeedmixBondon Tuny300701-01-22BabblestormAlric Reeder7001Travel Agent 0073201-Jan-22SkynoodleAndros Cathcart100801-01-22JayoMelany Brimblecombe3010Travel Agent 00733Booking01-01-22QuinuArt Giannotti3013Travel Agent 0073401-Jan-22MitaAubert Racher400401-01-22DynazzyEricha MacBain4002Expedia3501-Jan-22SkinixCurcio Lewis300801-01-22TagtuneScarlett Berthel2001Expedia3601-Jan-22AimbuWeider Brookz100101-01-22Brana Stovin3008Expedia3701-Jan-22BabblestormAlric Reeder700101-01-22KimiaLottie Barnsdall2014Expedia3801-Jan-22JayoMelany Brimblecombe301001-01-22CentizuFaydra Hulland5006Expedia3901-Jan-22QuinuArt Giannotti301301-01-22MuxoFelice Kramer4002Expedia40Travel Agent 00701-01-22DynaboxDeane Gemson5005Expedia4101-Jan-22DynazzyEricha MacBain400201-01-22SkalithWillie Norree3002Expedia4201-Jan-22TagtuneScarlett Berthel200101-01-22TagchatAlmira Bartolomeotti2004Expedia4301-Jan-22Brana Stovin300801-01-22BuzzdogSharona Ferreres4012Travel Agent 0074401-Jan-22KimiaLottie Barnsdall201401-01-22ZavaIlaire Gaynes1013Travel Agent 0074501-Jan-22CentizuFaydra Hulland500601-01-22Prinz Rohan7001Travel Agent 0074601-Jan-22MuxoFelice Kramer400201-01-22NtagsCorny Madill3002Booking4701-Jan-22DynaboxDeane Gemson500501-01-22YouspanYasmin Snelgar3012Travel Agent 0074801-Jan-22SkalithWillie Norree300201-01-22SkyvuSherlock Hyland6001Travel Agent 0074901-Jan-22TagchatAlmira Bartolomeotti200401-01-22QuinuKalindi Shaughnessy6003Travel Agent 00750Expedia01-01-22TwitterlistLauretta Stoke4004Travel Agent 0075101-Jan-22BuzzdogSharona Ferreres401201-01-22BrightdogLucie Jewiss2009Hotels5201-Jan-22ZavaIlaire Gaynes101301-01-22ShufflebeatDorita Boulger6005Hotels5301-Jan-22Prinz Rohan700101-01-22FivechatOrsola Cowdery4001Hotels54Travel Agent 00701-01-22TopicloungeLilah Attryde1005Hotels5501-Jan-22NtagsCorny Madill300201-01-22YouopiaAlley Pyer2002Hotels56Booking5701-Jan-22YouspanYasmin Snelgar30125801-Jan-22SkyvuSherlock Hyland60015901-Jan-22QuinuKalindi Shaughnessy60036001-Jan-22TwitterlistLauretta Stoke400461Travel Agent 0076201-Jan-22BrightdogLucie Jewiss20096301-Jan-22ShufflebeatDorita Boulger60056401-Jan-22FivechatOrsola Cowdery40016501-Jan-22TopicloungeLilah Attryde10056601-Jan-22YouopiaAlley Pyer200267Hotels6869F3 :70=LET(ar,A4:D67,h,A3:D3,d,A4:A67,f,D4:D67,t,"Text Value",
a,IF(ar="","",ar),s,SEQUENCE(ROWS(a)),
x,f="",
k,XLOOKUP(s,FILTER(s,x),FILTER(d,x),,1),
IFNA(VSTACK(h,FILTER(HSTACK(a,k),NOT(x))),t))71no PQCell FormulasRangeFormulaF3:J55F3=LET(ar,A4:D67,h,A3:D3,d,A4:A67,f,D4:D67,t,"Text Value",
a,IF(ar="","",ar),s,SEQUENCE(ROWS(a)),
x,f="",
k,XLOOKUP(s,FILTER(s,x),FILTER(d,x),,1),
IFNA(VSTACK(h,FILTER(HSTACK(a,k),NOT(x))),t))A70A70=FORMULATEXT(F3)Dynamic array formulas.


----------



## Xlambda (May 30, 2021)

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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1TEXTJOINATEXTSPLIT2empty ignoredempty not ignored2 charsempty ignored3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=04sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check5a4ca,4,,ca4ca4ca><6.8><ta6.8t1316d0.5d,,,0.5d0.5d0.5><2.4  w><2.4  w6007g hi 2k  l3 ng h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n<Ac4><gh>< ><<Ac4gh 4218space9empty ignoredea arg.=0ea arg.=1empty not ignored10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=111a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check12d,0.5d0.5d0.5a6.8t131013g h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n2.4  w060014<Ac4gh 421015Complex scenariospace followed by an empty string16empty ignoredempty not ignored17ea 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 chars20,,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 chars22, ,,x   4, ,, x   4  x   4 2324Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument25                            -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument26                       We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)2728=LEN(H19#)=LEN(N19#)291113311130303011130001011331413110413110321510001051003334- 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 used3536ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)37fixed with 4 pos.a bc/a  b/ bcda bca  b bcd44438/____ /ab d/   d/a   ab d   da   44439/xxxx/check:pattern is kept40ATEXTSPLIT postCell FormulasRangeFormulaH4,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.


----------



## Xlambda (Jul 10, 2022)

*Leading 0's, trailing 0's, leading and trailing 0's with a separator.*
This one is inspired by Leila's today's YT shorts: 



text-and-values.xlsxABCDEFGHIJKLMN1leading 0's2=TEXT(B3:B7,REPT(0,MAX(LEN(B3:B7))))or=LET(a,B3:B7,REPLACE(a,1,,REPT(0,MAX(LEN(a))-LEN(a))))3100000100000141234561234561234565000000000000612300012300012371200001200001289trailing 0's10=LET(a,B11:B15,l,LEN(a),REPLACE(a,l+1,,REPT(0,MAX(l)-l)))11110000012123456123456130000001412312300015121200001617leading and trailing 0's18191|1000001|10000020123|1000123|1000002112|1234000012|12340022123456|123456123456|12345623000000|00000024|123000000|123000251234|001234|0000002612|12000012|1200002728E19:29 =LET(
     a,B18:B25,
     t,ATEXTSPILL(a,LAMBDA(x,TEXTSPLIT(x,"|"))),
     aa,INDEX(t,,1),ab,INDEX(t,,2),
     ac,IF(aa="",0,aa),ad,IF(ab="",0,ab),
     x,TEXT(ac,REPT(0,MAX(LEN(ac)))),
     l,LEN(ad),
     y,REPLACE(ad,l+1,,REPT(0,MAX(l)-l)),
     x&"|"&y
)3031323334353637383940leila leading 0Cell FormulasRangeFormulaD2,D10,I2D2=FORMULATEXT(D3)D3:D7D3=TEXT(B3:B7,REPT(0,MAX(LEN(B3:B7))))I3:I7I3=LET(a,B3:B7,REPLACE(a,1,,REPT(0,MAX(LEN(a))-LEN(a))))D11:D15D11=LET(a,B11:B15,l,LEN(a),REPLACE(a,l+1,,REPT(0,MAX(l)-l)))E19:E26E19=LET(a,B19:B26,t,ATEXTSPILL(a,LAMBDA(x,TEXTSPLIT(x,"|"))),aa,INDEX(t,,1),ab,INDEX(t,,2),ac,IF(aa="",0,aa),ad,IF(ab="",0,ab),x,TEXT(ac,REPT(0,MAX(LEN(ac)))),l,LEN(ad),y,REPLACE(ad,l+1,,REPT(0,MAX(l)-l)),x&"|"&y)Dynamic array formulas.


----------



## Xlambda (Jul 24, 2022)

It's Sunday, some *fun* with *modular lambda "programming"* (when a structure of lambdas is assembled/called by a final lambda).
Inspired by today's home page article: Spell Out Numbers in Excel
Module functions *DE*, *SH*, *SN* (*D*igits in *E*nglish, *S*pell *H*undreds, *S*pell *N*umbers)
Final function *SPELLNR*
*SPELLNR* can handle any 2D arrays, positive or negative numbers with decimals or not (2 decimals approximation), ignoring text, blanks, and numbers that in absolute value are bigger than 1 quadrillion.
*DE()* *D*igits in *E*nglish, argument free or lambda constants function, can be also a defined name

```
=LAMBDA(
    {
        0, 10, "", "", "ten";
        1, 11, "one", "", "eleven";
        2, 12, "two", "twenty", "twelve";
        3, 13, "three", "thirty", "thirteen";
        4, 14, "four", "forty", "fourteen";
        5, 15, "five", "fifty", "fifteen";
        6, 16, "six", "sixty", "sixteen";
        7, 17, "seven", "seventy", "seventeen";
        8, 18, "eight", "eighty", "eighteen";
        9, 19, "nine", "ninety", "nineteen"
    }
)
```
spell numbers.xlsxABCDEFGHIJKLM12=DE()3010ten4111oneeleven5212twotwentytwelve6313threethirtythirteen7414fourfortyfourteen8515fivefiftyfifteen9616sixsixtysixteen10717sevenseventyseventeen11818eighteightyeighteen12919nineninetynineteen1314Note: If we want spelling in other language only this we have to change with the correspondent translations.15SPELLNR 1Cell FormulasRangeFormulaB2B2=FORMULATEXT(B3)B3:F12B3=DE()Dynamic array formulas.


----------



## Xlambda (Jul 24, 2022)

*SH(a)* *S*pell *H*undreds, spells any positive integer that has max 3 digits. *Calls DE()*
*a*: any array of max 3 digits positive integers

```
=LAMBDA(a,
    LET(
        s, " ",
        h, "hundred",
        t, TEXT(a, "000"),
        l, VLOOKUP(--LEFT(t, 1), DE(), 3, 0),
        ra, IFNA(VLOOKUP(--RIGHT(t, 2), DROP(DE(), , 1), 4, 0), ""),
        rb, VLOOKUP(--MID(t, 2, 1), DE(), 4, 0),
        r, VLOOKUP(--RIGHT(t, 1), DE(), 3, 0),
        TRIM(IF(l = "", "", l & s & h) & s & IF(ra = "", rb & s & r, ra))
    )
)
```
spell numbers.xlsxABCDEFGHIJKLMNOP1aaaa2=SEQUENCE(50)-1=SEQUENCE(50,,50)=SEQUENCE(50,,100)=SEQUENCE(50,,950)3↓↓=SH(B4#)↓↓=SH(E4#)↓↓=SH(H4#)↓↓=SH(K4#)40 50fifty100one hundred950nine hundred fifty51one51fifty one101one hundred one951nine hundred fifty onea62two52fifty two102one hundred two952nine hundred fifty two21773three53fifty three103one hundred three953nine hundred fifty three77790184four54fifty four104one hundred four954nine hundred fifty four95five55fifty five105one hundred five955nine hundred fifty five=SH(N6:O7)106six56fifty six106one hundred six956nine hundred fifty sixtwoseventeen117seven57fifty seven107one hundred seven957nine hundred fifty sevenseven hundred seventy sevennine hundred one128eight58fifty eight108one hundred eight958nine hundred fifty eight139nine59fifty nine109one hundred nine959nine hundred fifty nine1410ten60sixty110one hundred ten960nine hundred sixty1511eleven61sixty one111one hundred eleven961nine hundred sixty one1612twelve62sixty two112one hundred twelve962nine hundred sixty two1713thirteen63sixty three113one hundred thirteen963nine hundred sixty three1814fourteen64sixty four114one hundred fourteen964nine hundred sixty four1915fifteen65sixty five115one hundred fifteen965nine hundred sixty five2016sixteen66sixty six116one hundred sixteen966nine hundred sixty six2117seventeen67sixty seven117one hundred seventeen967nine hundred sixty seven2218eighteen68sixty eight118one hundred eighteen968nine hundred sixty eight2319nineteen69sixty nine119one hundred nineteen969nine hundred sixty nine2420twenty70seventy120one hundred twenty970nine hundred seventy2521twenty one71seventy one121one hundred twenty one971nine hundred seventy one2622twenty two72seventy two122one hundred twenty two972nine hundred seventy two2723twenty three73seventy three123one hundred twenty three973nine hundred seventy three2824twenty four74seventy four124one hundred twenty four974nine hundred seventy four2925twenty five75seventy five125one hundred twenty five975nine hundred seventy five3026twenty six76seventy six126one hundred twenty six976nine hundred seventy six3127twenty seven77seventy seven127one hundred twenty seven977nine hundred seventy seven3228twenty eight78seventy eight128one hundred twenty eight978nine hundred seventy eight3329twenty nine79seventy nine129one hundred twenty nine979nine hundred seventy nine3430thirty80eighty130one hundred thirty980nine hundred eighty3531thirty one81eighty one131one hundred thirty one981nine hundred eighty one3632thirty two82eighty two132one hundred thirty two982nine hundred eighty two3733thirty three83eighty three133one hundred thirty three983nine hundred eighty three3834thirty four84eighty four134one hundred thirty four984nine hundred eighty four3935thirty five85eighty five135one hundred thirty five985nine hundred eighty five4036thirty six86eighty six136one hundred thirty six986nine hundred eighty six4137thirty seven87eighty seven137one hundred thirty seven987nine hundred eighty seven4238thirty eight88eighty eight138one hundred thirty eight988nine hundred eighty eight4339thirty nine89eighty nine139one hundred thirty nine989nine hundred eighty nine4440forty90ninety140one hundred forty990nine hundred ninety4541forty one91ninety one141one hundred forty one991nine hundred ninety one4642forty two92ninety two142one hundred forty two992nine hundred ninety two4743forty three93ninety three143one hundred forty three993nine hundred ninety three4844forty four94ninety four144one hundred forty four994nine hundred ninety four4945forty five95ninety five145one hundred forty five995nine hundred ninety five5046forty six96ninety six146one hundred forty six996nine hundred ninety six5147forty seven97ninety seven147one hundred forty seven997nine hundred ninety seven5248forty eight98ninety eight148one hundred forty eight998nine hundred ninety eight5349forty nine99ninety nine149one hundred forty nine999nine hundred ninety nine54SPELLNR 2Cell FormulasRangeFormulaB2,K2,H2,E2B2=FORMULATEXT(B4)C3,N9,L3,I3,F3C3=FORMULATEXT(C4)B4:B53B4=SEQUENCE(50)-1C4:C53,L4:L53,I4:I53,F4:F53C4=SH(B4#)E4:E53E4=SEQUENCE(50,,50)H4:H53H4=SEQUENCE(50,,100)K4:K53K4=SEQUENCE(50,,950)N10:O11N10=SH(N6:O7)Dynamic array formulas.


----------



## Xlambda (Jul 24, 2022)

*SN(a,[cy])* *S*pelling *N*umbers. *Calls SH(a)*
a: single value or cell reference of positive number (with decimals or not)
[cy]: currency argument, if omitted, empty string ""

```
=LAMBDA(a, [cy],
    LET(
        c, IF(cy = "", "", cy),
        s, " ",
        t, {"trillion"; "billion"; "million"; "thousand"; ""},
        nd, ROUNDUP(a, 2),
        n, TEXT(TEXTBEFORE(nd, ".", , , 1), "000000000000000"),
        dd, TEXTAFTER(nd, ".", , , 1),
        d, IF(LEN(dd) = 1, dd & 0, dd),
        m, SH(MID(n, SEQUENCE(5, , , 3), 3)),
        x, IF(m = "", "", m & s & t),
        v, TEXTJOIN(", ", , x),
        y, IF(c = "", v, IF(v = "", "no " & c, v & s & c)),
        z, IF(d = "", "", IF(y = "", "", " and ") & SH(d) & " cents"),
        TRIM(y & z)
    )
)
```
spell numbers.xlsxABCDE1cy,omitted2formulas C2:C26=SN(C3)30  40.1ten cents50.9ninety cents60.123thirteen cents70.125thirteen cents80.126thirteen cents90.123456thirteen cents1010.986ten and ninety nine cents1111.776eleven and seventy eight cents1219.99nineteen and ninety nine cents1319.999twenty14101one hundred one151001one thousand, one1610001ten thousand, one17100000one hundred thousand181000001one million, one1910000000ten million20what really is in cell100000001one hundred million, one21=VALUETOTEXT(C22:C27)999999999.9nine hundred ninety nine million, nine hundred ninety nine thousand, nine hundred ninety nine and ninety four cents221000000000000.01→→1E+12one trillion and one cents2310000000000000.1→→1E+13ten trillion and ten cents24100000000000000→→1E+14one hundred trillion25999999999999999→→1E+15nine hundred ninety nine trillion, nine hundred ninety nine billion, nine hundred ninety nine million, nine hundred ninety nine thousand, nine hundred ninety nine2610000000023.49→→10000000023ten billion, twenty three and forty nine cents27190000000000003→→1.9E+14one hundred ninety trillion, three28SPELLNR 3Cell FormulasRangeFormulaE3E3=SN(C3)A21,D2A21=FORMULATEXT(A22)A22:A27A22=VALUETOTEXT(C22:C27)D3:D27D3=SN(C3)Dynamic array formulas.


----------



## Xlambda (Jul 24, 2022)

spell numbers.xlsxABCDE1cy,"euros"2formulas C3:C28=SN(C3,"euros")30no euros40.1no euros and ten cents50.9no euros and ninety cents60.123no euros and thirteen cents70.125no euros and thirteen cents80.126no euros and thirteen cents90.123456no euros and thirteen cents1010.986ten euros and ninety nine cents1111.776eleven euros and seventy eight cents1219.99nineteen euros and ninety nine cents1319.999twenty euros14978.44nine hundred seventy eight euros and forty four cents157562003.8seven million, five hundred sixty two thousand, three euros and eighty cents16200101.03two hundred thousand, one hundred one euros and three cents17555555555.5five hundred fifty five million, five hundred fifty five thousand, five hundred fifty five euros and fifty cents182222.22two thousand, two hundred twenty two euros and twenty two cents1933333.33thirty three thousand, three hundred thirty three euros and thirty three cents20444444.44four hundred forty four thousand, four hundred forty four euros and forty four cents215555555.55five million, five hundred fifty five thousand, five hundred fifty five euros and fifty five cents22=VALUETOTEXT(C23:C28)66666666.66sixty six million, six hundred sixty six thousand, six hundred sixty six euros and sixty six cents23777777777.77→→777777777.8seven hundred seventy seven million, seven hundred seventy seven thousand, seven hundred seventy seven euros and seventy seven cents248888888888.88→→8888888889eight billion, eight hundred eighty eight million, eight hundred eighty eight thousand, eight hundred eighty eight euros and eighty eight cents2599999999999.9→→1E+11ninety nine billion, nine hundred ninety nine million, nine hundred ninety nine thousand, nine hundred ninety nine euros and ninety cents26100000000000→→1E+11one hundred billion euros27100000000000000→→1E+14one hundred trillion euros28999000000000001→→9.99E+14nine hundred ninety nine trillion, one euros29SPELLNR 4Cell FormulasRangeFormulaA22,D2A22=FORMULATEXT(A23)A23:A28A23=VALUETOTEXT(C23:C28)D3:D28D3=SN(C3,"euros")Dynamic array formulas.


----------



## Xlambda (Jul 24, 2022)

*SPELLNR(ar,[cy])* *S*pell *N*umbers. *Calls SN(a,[cy])*
*a*: any array
*[cy]*: currency argument, if omitted, empty string ""

```
=LAMBDA(ar, [cy],
    LET(
        r, IF(ar = "", "", ar),
        a, IFERROR(ABS(r), ""),
        c, ISNUMBER(a) * (a < 1000000000000000),
        y, IF(c, a, ""),
        g, LEFT(ar, 1),
        m, MAP(y, LAMBDA(x, IF(x = "", "", SN(x, cy)))),
        ca, (g = "-") * (m <> ""),
        z, IF(ca, "negative " & m, m),
        IF(z = "", r, z)
    )
)
```
spell numbers.xlsxABCDE12=RANDARRAY(60,,-999999,999999)single cell lambda3↓↓single cell lambdacy,"dollars"4↓↓=SPELLNR(B5#)=SPELLNR(B5#,"dollars")5827025.9511eight hundred twenty seven thousand, twenty five and ninety six centseight hundred twenty seven thousand, twenty five dollars and ninety six cents6967190.8414nine hundred sixty seven thousand, one hundred ninety and eighty five centsnine hundred sixty seven thousand, one hundred ninety dollars and eighty five cents7-942990.1307negative nine hundred forty two thousand, nine hundred ninety and fourteen centsnegative nine hundred forty two thousand, nine hundred ninety dollars and fourteen cents8973607.4679nine hundred seventy three thousand, six hundred seven and forty seven centsnine hundred seventy three thousand, six hundred seven dollars and forty seven cents9-474929.3543negative four hundred seventy four thousand, nine hundred twenty nine and thirty six centsnegative four hundred seventy four thousand, nine hundred twenty nine dollars and thirty six cents10184486.2849one hundred eighty four thousand, four hundred eighty six and twenty nine centsone hundred eighty four thousand, four hundred eighty six dollars and twenty nine cents11-684815.8461negative six hundred eighty four thousand, eight hundred fifteen and eighty five centsnegative six hundred eighty four thousand, eight hundred fifteen dollars and eighty five cents12687994.0197six hundred eighty seven thousand, nine hundred ninety four and two centssix hundred eighty seven thousand, nine hundred ninety four dollars and two cents13354397.5686three hundred fifty four thousand, three hundred ninety seven and fifty seven centsthree hundred fifty four thousand, three hundred ninety seven dollars and fifty seven cents14542401.1714five hundred forty two thousand, four hundred one and eighteen centsfive hundred forty two thousand, four hundred one dollars and eighteen cents15-409379.2076negative four hundred nine thousand, three hundred seventy nine and twenty one centsnegative four hundred nine thousand, three hundred seventy nine dollars and twenty one cents16468130.7616four hundred sixty eight thousand, one hundred thirty and seventy seven centsfour hundred sixty eight thousand, one hundred thirty dollars and seventy seven cents17274897.5428two hundred seventy four thousand, eight hundred ninety seven and fifty five centstwo hundred seventy four thousand, eight hundred ninety seven dollars and fifty five cents18952624.8789nine hundred fifty two thousand, six hundred twenty four and eighty eight centsnine hundred fifty two thousand, six hundred twenty four dollars and eighty eight cents19-301276.5422negative three hundred one thousand, two hundred seventy six and fifty five centsnegative three hundred one thousand, two hundred seventy six dollars and fifty five cents20-227509.427negative two hundred twenty seven thousand, five hundred nine and forty three centsnegative two hundred twenty seven thousand, five hundred nine dollars and forty three cents2111786.84995eleven thousand, seven hundred eighty six and eighty five centseleven thousand, seven hundred eighty six dollars and eighty five cents22784848.5872seven hundred eighty four thousand, eight hundred forty eight and fifty nine centsseven hundred eighty four thousand, eight hundred forty eight dollars and fifty nine cents23-58175.2977negative fifty eight thousand, one hundred seventy five and thirty centsnegative fifty eight thousand, one hundred seventy five dollars and thirty cents24894871.2297eight hundred ninety four thousand, eight hundred seventy one and twenty three centseight hundred ninety four thousand, eight hundred seventy one dollars and twenty three cents25-804604.9034negative eight hundred four thousand, six hundred four and ninety one centsnegative eight hundred four thousand, six hundred four dollars and ninety one cents26899840.2359eight hundred ninety nine thousand, eight hundred forty and twenty four centseight hundred ninety nine thousand, eight hundred forty dollars and twenty four cents27659820.9247six hundred fifty nine thousand, eight hundred twenty and ninety three centssix hundred fifty nine thousand, eight hundred twenty dollars and ninety three cents28234891.8114two hundred thirty four thousand, eight hundred ninety one and eighty two centstwo hundred thirty four thousand, eight hundred ninety one dollars and eighty two cents29-753338.7395negative seven hundred fifty three thousand, three hundred thirty eight and seventy four centsnegative seven hundred fifty three thousand, three hundred thirty eight dollars and seventy four cents30-46511.586negative forty six thousand, five hundred eleven and fifty nine centsnegative forty six thousand, five hundred eleven dollars and fifty nine cents31892110.4368eight hundred ninety two thousand, one hundred ten and forty four centseight hundred ninety two thousand, one hundred ten dollars and forty four cents32-976080.4727negative nine hundred seventy six thousand, eighty and forty eight centsnegative nine hundred seventy six thousand, eighty dollars and forty eight cents33-382185.2579negative three hundred eighty two thousand, one hundred eighty five and twenty six centsnegative three hundred eighty two thousand, one hundred eighty five dollars and twenty six cents34-535319.9342negative five hundred thirty five thousand, three hundred nineteen and ninety four centsnegative five hundred thirty five thousand, three hundred nineteen dollars and ninety four cents35990369.7093nine hundred ninety thousand, three hundred sixty nine and seventy one centsnine hundred ninety thousand, three hundred sixty nine dollars and seventy one cents36-166673.6204negative one hundred sixty six thousand, six hundred seventy three and sixty three centsnegative one hundred sixty six thousand, six hundred seventy three dollars and sixty three cents3713649.43095thirteen thousand, six hundred forty nine and forty four centsthirteen thousand, six hundred forty nine dollars and forty four cents38-377885.0568negative three hundred seventy seven thousand, eight hundred eighty five and six centsnegative three hundred seventy seven thousand, eight hundred eighty five dollars and six cents39-551332.3562negative five hundred fifty one thousand, three hundred thirty two and thirty six centsnegative five hundred fifty one thousand, three hundred thirty two dollars and thirty six cents40-246979.6525negative two hundred forty six thousand, nine hundred seventy nine and sixty six centsnegative two hundred forty six thousand, nine hundred seventy nine dollars and sixty six cents41783339.9629seven hundred eighty three thousand, three hundred thirty nine and ninety seven centsseven hundred eighty three thousand, three hundred thirty nine dollars and ninety seven cents42-373118.696negative three hundred seventy three thousand, one hundred eighteen and seventy centsnegative three hundred seventy three thousand, one hundred eighteen dollars and seventy cents43-452089.6908negative four hundred fifty two thousand, eighty nine and seventy centsnegative four hundred fifty two thousand, eighty nine dollars and seventy cents44311082.0227three hundred eleven thousand, eighty two and three centsthree hundred eleven thousand, eighty two dollars and three cents45849946.2659eight hundred forty nine thousand, nine hundred forty six and twenty seven centseight hundred forty nine thousand, nine hundred forty six dollars and twenty seven cents46-614049.3494negative six hundred fourteen thousand, forty nine and thirty five centsnegative six hundred fourteen thousand, forty nine dollars and thirty five cents47423451.1556four hundred twenty three thousand, four hundred fifty one and sixteen centsfour hundred twenty three thousand, four hundred fifty one dollars and sixteen cents48748526.8031seven hundred forty eight thousand, five hundred twenty six and eighty one centsseven hundred forty eight thousand, five hundred twenty six dollars and eighty one cents49-764905.1543negative seven hundred sixty four thousand, nine hundred five and sixteen centsnegative seven hundred sixty four thousand, nine hundred five dollars and sixteen cents50830786.7686eight hundred thirty thousand, seven hundred eighty six and seventy seven centseight hundred thirty thousand, seven hundred eighty six dollars and seventy seven cents51933814.8802nine hundred thirty three thousand, eight hundred fourteen and eighty nine centsnine hundred thirty three thousand, eight hundred fourteen dollars and eighty nine cents52522546.4019five hundred twenty two thousand, five hundred forty six and forty one centsfive hundred twenty two thousand, five hundred forty six dollars and forty one cents53562837.7008five hundred sixty two thousand, eight hundred thirty seven and seventy one centsfive hundred sixty two thousand, eight hundred thirty seven dollars and seventy one cents54621399.4858six hundred twenty one thousand, three hundred ninety nine and forty nine centssix hundred twenty one thousand, three hundred ninety nine dollars and forty nine cents55884890.6126eight hundred eighty four thousand, eight hundred ninety and sixty two centseight hundred eighty four thousand, eight hundred ninety dollars and sixty two cents56666756.6951six hundred sixty six thousand, seven hundred fifty six and seventy centssix hundred sixty six thousand, seven hundred fifty six dollars and seventy cents57-823222.371negative eight hundred twenty three thousand, two hundred twenty two and thirty eight centsnegative eight hundred twenty three thousand, two hundred twenty two dollars and thirty eight cents58-418770.8902negative four hundred eighteen thousand, seven hundred seventy and ninety centsnegative four hundred eighteen thousand, seven hundred seventy dollars and ninety cents59-977571.1008negative nine hundred seventy seven thousand, five hundred seventy one and eleven centsnegative nine hundred seventy seven thousand, five hundred seventy one dollars and eleven cents60-343443.0269negative three hundred forty three thousand, four hundred forty three and three centsnegative three hundred forty three thousand, four hundred forty three dollars and three cents61-212901.8229negative two hundred twelve thousand, nine hundred one and eighty three centsnegative two hundred twelve thousand, nine hundred one dollars and eighty three cents62-431349.9871negative four hundred thirty one thousand, three hundred forty nine and ninety nine centsnegative four hundred thirty one thousand, three hundred forty nine dollars and ninety nine cents63-345083.8729negative three hundred forty five thousand, eighty three and eighty eight centsnegative three hundred forty five thousand, eighty three dollars and eighty eight cents64-573884.7048negative five hundred seventy three thousand, eight hundred eighty four and seventy one centsnegative five hundred seventy three thousand, eight hundred eighty four dollars and seventy one cents65SPELLNR 5Cell FormulasRangeFormulaB2B2=FORMULATEXT(B5)C4:D4C4=FORMULATEXT(C5)B5:B64B5=RANDARRAY(60,,-999999,999999)C5:C64C5=SPELLNR(B5#)D5:D64D5=SPELLNR(B5#,"dollars")Dynamic array formulas.


----------



## Xlambda (Jul 24, 2022)

SPELLNR can deal with positive/negative numbers which in absolute values are less than 1 quadrillion (1E+15), ignoring the rest, text, blanks/empty strings, numbers n that abs(n )>1E+15
spell numbers.xlsxABCDEFGHI12cy,omitted3ar=SPELLNR(B4:D6)423876.29-text-0.12twenty three thousand, eight hundred seventy six and twenty nine cents-textnegative twelve cents52675213.47-532009.17two million, six hundred seventy five thousand, two hundred thirteen and forty seven centsnegative five hundred thirty two thousand, nine and seventeen cents6-1000001.011.23457E+1690000009.09negative one million, one and one cents1.23457E+16ninety million, nine and nine cents78cy,"units"9text=SPELLNR(B4:D6,"units")10blank/empty stringtwenty three thousand, eight hundred seventy six units and twenty nine cents-textnegative no units and twelve cents11lrg nr.> 1 quadrillion (1E+15)two million, six hundred seventy five thousand, two hundred thirteen units and forty seven centsnegative five hundred thirty two thousand, nine units and seventeen cents12negative one million, one units and one cents1.23457E+16ninety million, nine units and nine cents13SPELLNR 6Cell FormulasRangeFormulaF3,F9F3=FORMULATEXT(F4)F4:H6F4=SPELLNR(B4:D6)F10:H12F10=SPELLNR(B4:D6,"units")Dynamic array formulas.


----------



## Xlambda (Jul 27, 2022)

*Fun fact: No number less than 1000 contains the letter "a" *
spell numbers.xlsxABCDEFGHI12=SPELLNR(SEQUENCE(999))=SEARCH("a",B3#)=--ISERR(D3#)=SUM(F3#)3one#VALUE!19994two#VALUE!15three#VALUE!16four#VALUE!17five#VALUE!18six#VALUE!19seven#VALUE!110eight#VALUE!111nine#VALUE!112ten#VALUE!113eleven#VALUE!114twelve#VALUE!115thirteen#VALUE!116fourteen#VALUE!117fifteen#VALUE!118sixteen#VALUE!119seventeen#VALUE!120eighteen#VALUE!121nineteen#VALUE!122twenty#VALUE!123twenty one#VALUE!124twenty two#VALUE!125twenty three#VALUE!126twenty four#VALUE!127twenty five#VALUE!128twenty six#VALUE!129twenty seven#VALUE!130twenty eight#VALUE!131twenty nine#VALUE!132thirty#VALUE!133thirty one#VALUE!134thirty two#VALUE!135thirty three#VALUE!136thirty four#VALUE!137thirty five#VALUE!138thirty six#VALUE!139thirty seven#VALUE!140thirty eight#VALUE!141thirty nine#VALUE!142forty#VALUE!143forty one#VALUE!144forty two#VALUE!145forty three#VALUE!146forty four#VALUE!147forty five#VALUE!148forty six#VALUE!149forty seven#VALUE!150forty eight#VALUE!151forty nine#VALUE!152fifty#VALUE!153fifty one#VALUE!154fifty two#VALUE!155fifty three#VALUE!156fifty four#VALUE!157fifty five#VALUE!158fifty six#VALUE!159fifty seven#VALUE!160fifty eight#VALUE!161fifty nine#VALUE!162sixty#VALUE!163sixty one#VALUE!164sixty two#VALUE!165sixty three#VALUE!166sixty four#VALUE!167sixty five#VALUE!168sixty six#VALUE!169sixty seven#VALUE!170sixty eight#VALUE!171sixty nine#VALUE!172seventy#VALUE!173seventy one#VALUE!174seventy two#VALUE!175seventy three#VALUE!176seventy four#VALUE!177seventy five#VALUE!178seventy six#VALUE!179seventy seven#VALUE!180seventy eight#VALUE!181seventy nine#VALUE!182eighty#VALUE!183eighty one#VALUE!184eighty two#VALUE!185eighty three#VALUE!186eighty four#VALUE!187eighty five#VALUE!188eighty six#VALUE!189eighty seven#VALUE!190eighty eight#VALUE!191eighty nine#VALUE!192ninety#VALUE!193ninety one#VALUE!194ninety two#VALUE!195ninety three#VALUE!196ninety four#VALUE!197ninety five#VALUE!198ninety six#VALUE!199ninety seven#VALUE!1100ninety eight#VALUE!1101ninety nine#VALUE!1102one hundred#VALUE!1103one hundred one#VALUE!1104one hundred two#VALUE!1105one hundred three#VALUE!1106one hundred four#VALUE!1107one hundred five#VALUE!1108one hundred six#VALUE!1109one hundred seven#VALUE!1110one hundred eight#VALUE!1111one hundred nine#VALUE!1112one hundred ten#VALUE!1113one hundred eleven#VALUE!1114one hundred twelve#VALUE!1115one hundred thirteen#VALUE!1116one hundred fourteen#VALUE!1117one hundred fifteen#VALUE!1118one hundred sixteen#VALUE!1119one hundred seventeen#VALUE!1120one hundred eighteen#VALUE!1121one hundred nineteen#VALUE!1122one hundred twenty#VALUE!1123one hundred twenty one#VALUE!1124one hundred twenty two#VALUE!1125one hundred twenty three#VALUE!1126one hundred twenty four#VALUE!1127one hundred twenty five#VALUE!1128one hundred twenty six#VALUE!1129one hundred twenty seven#VALUE!1130one hundred twenty eight#VALUE!1131one hundred twenty nine#VALUE!1132one hundred thirty#VALUE!1133one hundred thirty one#VALUE!1134one hundred thirty two#VALUE!1135one hundred thirty three#VALUE!1136one hundred thirty four#VALUE!1137one hundred thirty five#VALUE!1138one hundred thirty six#VALUE!1139one hundred thirty seven#VALUE!1140one hundred thirty eight#VALUE!1141one hundred thirty nine#VALUE!1142one hundred forty#VALUE!1143one hundred forty one#VALUE!1144one hundred forty two#VALUE!1145one hundred forty three#VALUE!1146one hundred forty four#VALUE!1147one hundred forty five#VALUE!1148one hundred forty six#VALUE!1149one hundred forty seven#VALUE!1150one hundred forty eight#VALUE!1151one hundred forty nine#VALUE!1152one hundred fifty#VALUE!1153one hundred fifty one#VALUE!1154one hundred fifty two#VALUE!1155one hundred fifty three#VALUE!1156one hundred fifty four#VALUE!1157one hundred fifty five#VALUE!1158one hundred fifty six#VALUE!1159one hundred fifty seven#VALUE!1160one hundred fifty eight#VALUE!1161one hundred fifty nine#VALUE!1162one hundred sixty#VALUE!1163one hundred sixty one#VALUE!1164one hundred sixty two#VALUE!1165one hundred sixty three#VALUE!1166one hundred sixty four#VALUE!1167one hundred sixty five#VALUE!1168one hundred sixty six#VALUE!1169one hundred sixty seven#VALUE!1170one hundred sixty eight#VALUE!1171one hundred sixty nine#VALUE!1172one hundred seventy#VALUE!1173one hundred seventy one#VALUE!1174one hundred seventy two#VALUE!1175one hundred seventy three#VALUE!1176one hundred seventy four#VALUE!1177one hundred seventy five#VALUE!1178one hundred seventy six#VALUE!1179one hundred seventy seven#VALUE!1180one hundred seventy eight#VALUE!1181one hundred seventy nine#VALUE!1182one hundred eighty#VALUE!1183one hundred eighty one#VALUE!1184one hundred eighty two#VALUE!1185one hundred eighty three#VALUE!1186one hundred eighty four#VALUE!1187one hundred eighty five#VALUE!1188one hundred eighty six#VALUE!1189one hundred eighty seven#VALUE!1190one hundred eighty eight#VALUE!1191one hundred eighty nine#VALUE!1192one hundred ninety#VALUE!1193one hundred ninety one#VALUE!1194one hundred ninety two#VALUE!1195one hundred ninety three#VALUE!1196one hundred ninety four#VALUE!1197one hundred ninety five#VALUE!1198one hundred ninety six#VALUE!1199one hundred ninety seven#VALUE!1200one hundred ninety eight#VALUE!1201one hundred ninety nine#VALUE!1202two hundred#VALUE!1203two hundred one#VALUE!1204two hundred two#VALUE!1Sheet13Cell FormulasRangeFormulaD2,F2,H2,B2D2=FORMULATEXT(D3)B3:B1001B3=SPELLNR(SEQUENCE(999))D3:D1001D3=SEARCH("a",B3#)F3:F1001F3=--ISERR(D3#)H3H3=SUM(F3#)Dynamic array formulas.


----------



## Xlambda (Sep 18, 2022)

Redesigned all modules needed to spell numbers in a more compact form. Introduced all the key english words used for spelling numbers in one place, the initial DE function. Having them in one place and not spreaded through other functions, will make it easier to translate at one place only, if we need spelling in another language. Only the units name can be changed in the expression of last function, the main function, SPLNRS, as will see.
*1st module: **DE()* Digits in English, argument free function template.

```
=LAMBDA(
    {
        0, "", "ten", "", " hundred ";
        1, "one", "eleven", "", " hundredth ";
        2, "two", "twelve", "twenty", "";
        3, "three", "thirteen", "thirty", "";
        4, "four", "fourteen", "forty", "";
        5, "five", "fifteen", "fifty", "";
        6, "six", "sixteen", "sixty", "";
        7, "seven", "seventeen", "seventy", "";
        8, "eight", "eighteen", "eighty", "";
        9, "nine", "nineteen", "ninety", "";
        " trillion ", " billion ", " million ", " thousands ", ""
    }
)
```
*Important Note:* " hundred ", " hundredth ", " trillion ", " billion ", " million ", and " thousands " words have leading and trailing spaces.
This design gimmick helped me make the functions shorter. This structure should be kept in case of using other languages.
All references to these words in the following functions will be made using INDEX(DE(),row,clm) and not explicitly.
SPLNRS.xlsxABCDEFG12=DE()30ten hundred 41oneeleven hundredth 52twotwelvetwenty63threethirteenthirty74fourfourteenforty85fivefifteenfifty96sixsixteensixty107sevenseventeenseventy118eighteighteeneighty129ninenineteenninety13 trillion  billion  million  thousands 141516=DE()170ten hundred 181oneeleven hundredth 192twotwelvetwenty203threethirteenthirty214fourfourteenforty225fivefifteenfifty236sixsixteensixty247sevenseventeenseventy258eighteighteeneighty269ninenineteenninety27 trillion  billion  million  thousands 2829color codes30 - leading and tailing spaces31 - empty strings "" (not blanks)32 - no leading or trailing spaces 33DE()Cell FormulasRangeFormulaB2,B16B2=FORMULATEXT(B3)B3:F13,B17:F27B3=DE()Dynamic array formulas.


----------



## Xlambda (Sep 18, 2022)

*2nd module:* *SH(a)* Spelling Hundreds function
*a:* array (single value, 1D or 2D arrays) , integers up to 3 digits.

```
=LAMBDA(a,
    LET(
        h, INDEX(DE(), 1, 5),
        t, TEXT(a, "000"),
        l, --LEFT(t, 1),
        m, --MID(t, 2, 1),
        r, --RIGHT(t, 1),
        x, VLOOKUP(l, DE(), 2),
        y, VLOOKUP(m, DE(), 4),
        z, IF(m = 1, VLOOKUP(r, DE(), 3), VLOOKUP(r, DE(), 2)),
        v, y & " " & z,
        IFERROR(TRIM(IF(x = "", v, x & h & v)), "")
    )
)
```
Spelling all possible 3 digits tops numbers 0-999 using as input array SEQUENCE(50,20)-1
SPLNRS.xlsxABCDEFGHIJKLMNOPQRSTUV12=SH(SEQUENCE(50,20)-1)3 onetwothreefourfivesixseveneightnineteneleventwelvethirteenfourteenfifteensixteenseventeeneighteennineteen4twentytwenty onetwenty twotwenty threetwenty fourtwenty fivetwenty sixtwenty seventwenty eighttwenty ninethirtythirty onethirty twothirty threethirty fourthirty fivethirty sixthirty seventhirty eightthirty nine5fortyforty oneforty twoforty threeforty fourforty fiveforty sixforty sevenforty eightforty ninefiftyfifty onefifty twofifty threefifty fourfifty fivefifty sixfifty sevenfifty eightfifty nine6sixtysixty onesixty twosixty threesixty foursixty fivesixty sixsixty sevensixty eightsixty nineseventyseventy oneseventy twoseventy threeseventy fourseventy fiveseventy sixseventy sevenseventy eightseventy nine7eightyeighty oneeighty twoeighty threeeighty foureighty fiveeighty sixeighty seveneighty eighteighty nineninetyninety oneninety twoninety threeninety fourninety fiveninety sixninety sevenninety eightninety nine8one hundredone hundred oneone hundred twoone hundred threeone hundred fourone hundred fiveone hundred sixone hundred sevenone hundred eightone hundred nineone hundred tenone hundred elevenone hundred twelveone hundred thirteenone hundred fourteenone hundred fifteenone hundred sixteenone hundred seventeenone hundred eighteenone hundred nineteen9one hundred twentyone hundred twenty oneone hundred twenty twoone hundred twenty threeone hundred twenty fourone hundred twenty fiveone hundred twenty sixone hundred twenty sevenone hundred twenty eightone hundred twenty nineone hundred thirtyone hundred thirty oneone hundred thirty twoone hundred thirty threeone hundred thirty fourone hundred thirty fiveone hundred thirty sixone hundred thirty sevenone hundred thirty eightone hundred thirty nine10one hundred fortyone hundred forty oneone hundred forty twoone hundred forty threeone hundred forty fourone hundred forty fiveone hundred forty sixone hundred forty sevenone hundred forty eightone hundred forty nineone hundred fiftyone hundred fifty oneone hundred fifty twoone hundred fifty threeone hundred fifty fourone hundred fifty fiveone hundred fifty sixone hundred fifty sevenone hundred fifty eightone hundred fifty nine11one hundred sixtyone hundred sixty oneone hundred sixty twoone hundred sixty threeone hundred sixty fourone hundred sixty fiveone hundred sixty sixone hundred sixty sevenone hundred sixty eightone hundred sixty nineone hundred seventyone hundred seventy oneone hundred seventy twoone hundred seventy threeone hundred seventy fourone hundred seventy fiveone hundred seventy sixone hundred seventy sevenone hundred seventy eightone hundred seventy nine12one hundred eightyone hundred eighty oneone hundred eighty twoone hundred eighty threeone hundred eighty fourone hundred eighty fiveone hundred eighty sixone hundred eighty sevenone hundred eighty eightone hundred eighty nineone hundred ninetyone hundred ninety oneone hundred ninety twoone hundred ninety threeone hundred ninety fourone hundred ninety fiveone hundred ninety sixone hundred ninety sevenone hundred ninety eightone hundred ninety nine13two hundredtwo hundred onetwo hundred twotwo hundred threetwo hundred fourtwo hundred fivetwo hundred sixtwo hundred seventwo hundred eighttwo hundred ninetwo hundred tentwo hundred eleventwo hundred twelvetwo hundred thirteentwo hundred fourteentwo hundred fifteentwo hundred sixteentwo hundred seventeentwo hundred eighteentwo hundred nineteen14two hundred twentytwo hundred twenty onetwo hundred twenty twotwo hundred twenty threetwo hundred twenty fourtwo hundred twenty fivetwo hundred twenty sixtwo hundred twenty seventwo hundred twenty eighttwo hundred twenty ninetwo hundred thirtytwo hundred thirty onetwo hundred thirty twotwo hundred thirty threetwo hundred thirty fourtwo hundred thirty fivetwo hundred thirty sixtwo hundred thirty seventwo hundred thirty eighttwo hundred thirty nine15two hundred fortytwo hundred forty onetwo hundred forty twotwo hundred forty threetwo hundred forty fourtwo hundred forty fivetwo hundred forty sixtwo hundred forty seventwo hundred forty eighttwo hundred forty ninetwo hundred fiftytwo hundred fifty onetwo hundred fifty twotwo hundred fifty threetwo hundred fifty fourtwo hundred fifty fivetwo hundred fifty sixtwo hundred fifty seventwo hundred fifty eighttwo hundred fifty nine16two hundred sixtytwo hundred sixty onetwo hundred sixty twotwo hundred sixty threetwo hundred sixty fourtwo hundred sixty fivetwo hundred sixty sixtwo hundred sixty seventwo hundred sixty eighttwo hundred sixty ninetwo hundred seventytwo hundred seventy onetwo hundred seventy twotwo hundred seventy threetwo hundred seventy fourtwo hundred seventy fivetwo hundred seventy sixtwo hundred seventy seventwo hundred seventy eighttwo hundred seventy nine17two hundred eightytwo hundred eighty onetwo hundred eighty twotwo hundred eighty threetwo hundred eighty fourtwo hundred eighty fivetwo hundred eighty sixtwo hundred eighty seventwo hundred eighty eighttwo hundred eighty ninetwo hundred ninetytwo hundred ninety onetwo hundred ninety twotwo hundred ninety threetwo hundred ninety fourtwo hundred ninety fivetwo hundred ninety sixtwo hundred ninety seventwo hundred ninety eighttwo hundred ninety nine18three hundredthree hundred onethree hundred twothree hundred threethree hundred fourthree hundred fivethree hundred sixthree hundred seventhree hundred eightthree hundred ninethree hundred tenthree hundred eleventhree hundred twelvethree hundred thirteenthree hundred fourteenthree hundred fifteenthree hundred sixteenthree hundred seventeenthree hundred eighteenthree hundred nineteen19three hundred twentythree hundred twenty onethree hundred twenty twothree hundred twenty threethree hundred twenty fourthree hundred twenty fivethree hundred twenty sixthree hundred twenty seventhree hundred twenty eightthree hundred twenty ninethree hundred thirtythree hundred thirty onethree hundred thirty twothree hundred thirty threethree hundred thirty fourthree hundred thirty fivethree hundred thirty sixthree hundred thirty seventhree hundred thirty eightthree hundred thirty nine20three hundred fortythree hundred forty onethree hundred forty twothree hundred forty threethree hundred forty fourthree hundred forty fivethree hundred forty sixthree hundred forty seventhree hundred forty eightthree hundred forty ninethree hundred fiftythree hundred fifty onethree hundred fifty twothree hundred fifty threethree hundred fifty fourthree hundred fifty fivethree hundred fifty sixthree hundred fifty seventhree hundred fifty eightthree hundred fifty nine21three hundred sixtythree hundred sixty onethree hundred sixty twothree hundred sixty threethree hundred sixty fourthree hundred sixty fivethree hundred sixty sixthree hundred sixty seventhree hundred sixty eightthree hundred sixty ninethree hundred seventythree hundred seventy onethree hundred seventy twothree hundred seventy threethree hundred seventy fourthree hundred seventy fivethree hundred seventy sixthree hundred seventy seventhree hundred seventy eightthree hundred seventy nine22three hundred eightythree hundred eighty onethree hundred eighty twothree hundred eighty threethree hundred eighty fourthree hundred eighty fivethree hundred eighty sixthree hundred eighty seventhree hundred eighty eightthree hundred eighty ninethree hundred ninetythree hundred ninety onethree hundred ninety twothree hundred ninety threethree hundred ninety fourthree hundred ninety fivethree hundred ninety sixthree hundred ninety seventhree hundred ninety eightthree hundred ninety nine23four hundredfour hundred onefour hundred twofour hundred threefour hundred fourfour hundred fivefour hundred sixfour hundred sevenfour hundred eightfour hundred ninefour hundred tenfour hundred elevenfour hundred twelvefour hundred thirteenfour hundred fourteenfour hundred fifteenfour hundred sixteenfour hundred seventeenfour hundred eighteenfour hundred nineteen24four hundred twentyfour hundred twenty onefour hundred twenty twofour hundred twenty threefour hundred twenty fourfour hundred twenty fivefour hundred twenty sixfour hundred twenty sevenfour hundred twenty eightfour hundred twenty ninefour hundred thirtyfour hundred thirty onefour hundred thirty twofour hundred thirty threefour hundred thirty fourfour hundred thirty fivefour hundred thirty sixfour hundred thirty sevenfour hundred thirty eightfour hundred thirty nine25four hundred fortyfour hundred forty onefour hundred forty twofour hundred forty threefour hundred forty fourfour hundred forty fivefour hundred forty sixfour hundred forty sevenfour hundred forty eightfour hundred forty ninefour hundred fiftyfour hundred fifty onefour hundred fifty twofour hundred fifty threefour hundred fifty fourfour hundred fifty fivefour hundred fifty sixfour hundred fifty sevenfour hundred fifty eightfour hundred fifty nine26four hundred sixtyfour hundred sixty onefour hundred sixty twofour hundred sixty threefour hundred sixty fourfour hundred sixty fivefour hundred sixty sixfour hundred sixty sevenfour hundred sixty eightfour hundred sixty ninefour hundred seventyfour hundred seventy onefour hundred seventy twofour hundred seventy threefour hundred seventy fourfour hundred seventy fivefour hundred seventy sixfour hundred seventy sevenfour hundred seventy eightfour hundred seventy nine27four hundred eightyfour hundred eighty onefour hundred eighty twofour hundred eighty threefour hundred eighty fourfour hundred eighty fivefour hundred eighty sixfour hundred eighty sevenfour hundred eighty eightfour hundred eighty ninefour hundred ninetyfour hundred ninety onefour hundred ninety twofour hundred ninety threefour hundred ninety fourfour hundred ninety fivefour hundred ninety sixfour hundred ninety sevenfour hundred ninety eightfour hundred ninety nine28five hundredfive hundred onefive hundred twofive hundred threefive hundred fourfive hundred fivefive hundred sixfive hundred sevenfive hundred eightfive hundred ninefive hundred tenfive hundred elevenfive hundred twelvefive hundred thirteenfive hundred fourteenfive hundred fifteenfive hundred sixteenfive hundred seventeenfive hundred eighteenfive hundred nineteen29five hundred twentyfive hundred twenty onefive hundred twenty twofive hundred twenty threefive hundred twenty fourfive hundred twenty fivefive hundred twenty sixfive hundred twenty sevenfive hundred twenty eightfive hundred twenty ninefive hundred thirtyfive hundred thirty onefive hundred thirty twofive hundred thirty threefive hundred thirty fourfive hundred thirty fivefive hundred thirty sixfive hundred thirty sevenfive hundred thirty eightfive hundred thirty nine30five hundred fortyfive hundred forty onefive hundred forty twofive hundred forty threefive hundred forty fourfive hundred forty fivefive hundred forty sixfive hundred forty sevenfive hundred forty eightfive hundred forty ninefive hundred fiftyfive hundred fifty onefive hundred fifty twofive hundred fifty threefive hundred fifty fourfive hundred fifty fivefive hundred fifty sixfive hundred fifty sevenfive hundred fifty eightfive hundred fifty nine31five hundred sixtyfive hundred sixty onefive hundred sixty twofive hundred sixty threefive hundred sixty fourfive hundred sixty fivefive hundred sixty sixfive hundred sixty sevenfive hundred sixty eightfive hundred sixty ninefive hundred seventyfive hundred seventy onefive hundred seventy twofive hundred seventy threefive hundred seventy fourfive hundred seventy fivefive hundred seventy sixfive hundred seventy sevenfive hundred seventy eightfive hundred seventy nine32five hundred eightyfive hundred eighty onefive hundred eighty twofive hundred eighty threefive hundred eighty fourfive hundred eighty fivefive hundred eighty sixfive hundred eighty sevenfive hundred eighty eightfive hundred eighty ninefive hundred ninetyfive hundred ninety onefive hundred ninety twofive hundred ninety threefive hundred ninety fourfive hundred ninety fivefive hundred ninety sixfive hundred ninety sevenfive hundred ninety eightfive hundred ninety nine33six hundredsix hundred onesix hundred twosix hundred threesix hundred foursix hundred fivesix hundred sixsix hundred sevensix hundred eightsix hundred ninesix hundred tensix hundred elevensix hundred twelvesix hundred thirteensix hundred fourteensix hundred fifteensix hundred sixteensix hundred seventeensix hundred eighteensix hundred nineteen34six hundred twentysix hundred twenty onesix hundred twenty twosix hundred twenty threesix hundred twenty foursix hundred twenty fivesix hundred twenty sixsix hundred twenty sevensix hundred twenty eightsix hundred twenty ninesix hundred thirtysix hundred thirty onesix hundred thirty twosix hundred thirty threesix hundred thirty foursix hundred thirty fivesix hundred thirty sixsix hundred thirty sevensix hundred thirty eightsix hundred thirty nine35six hundred fortysix hundred forty onesix hundred forty twosix hundred forty threesix hundred forty foursix hundred forty fivesix hundred forty sixsix hundred forty sevensix hundred forty eightsix hundred forty ninesix hundred fiftysix hundred fifty onesix hundred fifty twosix hundred fifty threesix hundred fifty foursix hundred fifty fivesix hundred fifty sixsix hundred fifty sevensix hundred fifty eightsix hundred fifty nine36six hundred sixtysix hundred sixty onesix hundred sixty twosix hundred sixty threesix hundred sixty foursix hundred sixty fivesix hundred sixty sixsix hundred sixty sevensix hundred sixty eightsix hundred sixty ninesix hundred seventysix hundred seventy onesix hundred seventy twosix hundred seventy threesix hundred seventy foursix hundred seventy fivesix hundred seventy sixsix hundred seventy sevensix hundred seventy eightsix hundred seventy nine37six hundred eightysix hundred eighty onesix hundred eighty twosix hundred eighty threesix hundred eighty foursix hundred eighty fivesix hundred eighty sixsix hundred eighty sevensix hundred eighty eightsix hundred eighty ninesix hundred ninetysix hundred ninety onesix hundred ninety twosix hundred ninety threesix hundred ninety foursix hundred ninety fivesix hundred ninety sixsix hundred ninety sevensix hundred ninety eightsix hundred ninety nine38seven hundredseven hundred oneseven hundred twoseven hundred threeseven hundred fourseven hundred fiveseven hundred sixseven hundred sevenseven hundred eightseven hundred nineseven hundred tenseven hundred elevenseven hundred twelveseven hundred thirteenseven hundred fourteenseven hundred fifteenseven hundred sixteenseven hundred seventeenseven hundred eighteenseven hundred nineteen39seven hundred twentyseven hundred twenty oneseven hundred twenty twoseven hundred twenty threeseven hundred twenty fourseven hundred twenty fiveseven hundred twenty sixseven hundred twenty sevenseven hundred twenty eightseven hundred twenty nineseven hundred thirtyseven hundred thirty oneseven hundred thirty twoseven hundred thirty threeseven hundred thirty fourseven hundred thirty fiveseven hundred thirty sixseven hundred thirty sevenseven hundred thirty eightseven hundred thirty nine40seven hundred fortyseven hundred forty oneseven hundred forty twoseven hundred forty threeseven hundred forty fourseven hundred forty fiveseven hundred forty sixseven hundred forty sevenseven hundred forty eightseven hundred forty nineseven hundred fiftyseven hundred fifty oneseven hundred fifty twoseven hundred fifty threeseven hundred fifty fourseven hundred fifty fiveseven hundred fifty sixseven hundred fifty sevenseven hundred fifty eightseven hundred fifty nine41seven hundred sixtyseven hundred sixty oneseven hundred sixty twoseven hundred sixty threeseven hundred sixty fourseven hundred sixty fiveseven hundred sixty sixseven hundred sixty sevenseven hundred sixty eightseven hundred sixty nineseven hundred seventyseven hundred seventy oneseven hundred seventy twoseven hundred seventy threeseven hundred seventy fourseven hundred seventy fiveseven hundred seventy sixseven hundred seventy sevenseven hundred seventy eightseven hundred seventy nine42seven hundred eightyseven hundred eighty oneseven hundred eighty twoseven hundred eighty threeseven hundred eighty fourseven hundred eighty fiveseven hundred eighty sixseven hundred eighty sevenseven hundred eighty eightseven hundred eighty nineseven hundred ninetyseven hundred ninety oneseven hundred ninety twoseven hundred ninety threeseven hundred ninety fourseven hundred ninety fiveseven hundred ninety sixseven hundred ninety sevenseven hundred ninety eightseven hundred ninety nine43eight hundredeight hundred oneeight hundred twoeight hundred threeeight hundred foureight hundred fiveeight hundred sixeight hundred seveneight hundred eighteight hundred nineeight hundred teneight hundred eleveneight hundred twelveeight hundred thirteeneight hundred fourteeneight hundred fifteeneight hundred sixteeneight hundred seventeeneight hundred eighteeneight hundred nineteen44eight hundred twentyeight hundred twenty oneeight hundred twenty twoeight hundred twenty threeeight hundred twenty foureight hundred twenty fiveeight hundred twenty sixeight hundred twenty seveneight hundred twenty eighteight hundred twenty nineeight hundred thirtyeight hundred thirty oneeight hundred thirty twoeight hundred thirty threeeight hundred thirty foureight hundred thirty fiveeight hundred thirty sixeight hundred thirty seveneight hundred thirty eighteight hundred thirty nine45eight hundred fortyeight hundred forty oneeight hundred forty twoeight hundred forty threeeight hundred forty foureight hundred forty fiveeight hundred forty sixeight hundred forty seveneight hundred forty eighteight hundred forty nineeight hundred fiftyeight hundred fifty oneeight hundred fifty twoeight hundred fifty threeeight hundred fifty foureight hundred fifty fiveeight hundred fifty sixeight hundred fifty seveneight hundred fifty eighteight hundred fifty nine46eight hundred sixtyeight hundred sixty oneeight hundred sixty twoeight hundred sixty threeeight hundred sixty foureight hundred sixty fiveeight hundred sixty sixeight hundred sixty seveneight hundred sixty eighteight hundred sixty nineeight hundred seventyeight hundred seventy oneeight hundred seventy twoeight hundred seventy threeeight hundred seventy foureight hundred seventy fiveeight hundred seventy sixeight hundred seventy seveneight hundred seventy eighteight hundred seventy nine47eight hundred eightyeight hundred eighty oneeight hundred eighty twoeight hundred eighty threeeight hundred eighty foureight hundred eighty fiveeight hundred eighty sixeight hundred eighty seveneight hundred eighty eighteight hundred eighty nineeight hundred ninetyeight hundred ninety oneeight hundred ninety twoeight hundred ninety threeeight hundred ninety foureight hundred ninety fiveeight hundred ninety sixeight hundred ninety seveneight hundred ninety eighteight hundred ninety nine48nine hundrednine hundred onenine hundred twonine hundred threenine hundred fournine hundred fivenine hundred sixnine hundred sevennine hundred eightnine hundred ninenine hundred tennine hundred elevennine hundred twelvenine hundred thirteennine hundred fourteennine hundred fifteennine hundred sixteennine hundred seventeennine hundred eighteennine hundred nineteen49nine hundred twentynine hundred twenty onenine hundred twenty twonine hundred twenty threenine hundred twenty fournine hundred twenty fivenine hundred twenty sixnine hundred twenty sevennine hundred twenty eightnine hundred twenty ninenine hundred thirtynine hundred thirty onenine hundred thirty twonine hundred thirty threenine hundred thirty fournine hundred thirty fivenine hundred thirty sixnine hundred thirty sevennine hundred thirty eightnine hundred thirty nine50nine hundred fortynine hundred forty onenine hundred forty twonine hundred forty threenine hundred forty fournine hundred forty fivenine hundred forty sixnine hundred forty sevennine hundred forty eightnine hundred forty ninenine hundred fiftynine hundred fifty onenine hundred fifty twonine hundred fifty threenine hundred fifty fournine hundred fifty fivenine hundred fifty sixnine hundred fifty sevennine hundred fifty eightnine hundred fifty nine51nine hundred sixtynine hundred sixty onenine hundred sixty twonine hundred sixty threenine hundred sixty fournine hundred sixty fivenine hundred sixty sixnine hundred sixty sevennine hundred sixty eightnine hundred sixty ninenine hundred seventynine hundred seventy onenine hundred seventy twonine hundred seventy threenine hundred seventy fournine hundred seventy fivenine hundred seventy sixnine hundred seventy sevennine hundred seventy eightnine hundred seventy nine52nine hundred eightynine hundred eighty onenine hundred eighty twonine hundred eighty threenine hundred eighty fournine hundred eighty fivenine hundred eighty sixnine hundred eighty sevennine hundred eighty eightnine hundred eighty ninenine hundred ninetynine hundred ninety onenine hundred ninety twonine hundred ninety threenine hundred ninety fournine hundred ninety fivenine hundred ninety sixnine hundred ninety sevennine hundred ninety eightnine hundred ninety nine53Sheet3Cell FormulasRangeFormulaB2B2=FORMULATEXT(B3)B3:U52B3=SH(SEQUENCE(50,20)-1)Dynamic array formulas.


----------



## Xlambda (May 30, 2021)

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.

```
=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.xlsxABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAI1TEXTJOINATEXTSPLIT2empty ignoredempty not ignored2 charsempty ignored3empty not ignoredea arg.=0ea arg.=1delimiterea arg.=04sample=TEXTJOIN(",",0,A5:D5)=ATEXTSPLIT(F5:F7,",",)=ATEXTSPLIT(F5:F7,",",1)=ATEXTSPLIT(T5:T7,"><",)LEN check5a4ca,4,,ca4ca4ca><6.8><ta6.8t1316d0.5d,,,0.5d0.5d0.5><2.4  w><2.4  w6007g hi 2k  l3 ng h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n<Ac4><gh>< ><<Ac4gh 4218space9empty ignoredea arg.=0ea arg.=1empty not ignored10=TEXTJOIN(",",,A5:D5)=ATEXTSPLIT(F11:F13,",",)=ATEXTSPLIT(F11:F13,",",1)ea arg.=111a,4,ca4ca4c=ATEXTSPLIT(T5:T7,"><",1)LEN check12d,0.5d0.5d0.5a6.8t131013g h,i 2,k  l,3 ng hi 2k  l3 ng hi 2k  l3 n2.4  w060014<Ac4gh 421015Complex scenariospace followed by an empty string16empty ignoredempty not ignored17ea 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 chars20,,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 chars22, ,,x   4, ,, x   4  x   4 2324Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument25                            -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument26                       We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)2728=LEN(H19#)=LEN(N19#)291113311130303011130001011331413110413110321510001051003334- 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 used3536ex. code patternsample=ATEXTSPLIT(F37:F38,"/",)=LEN(H37#)37fixed with 4 pos.a bc/a  b/ bcda bca  b bcd44438/____ /ab d/   d/a   ab d   da   44439/xxxx/check:pattern is kept40ATEXTSPLIT postCell FormulasRangeFormulaH4,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.


----------



## Xlambda (Sep 18, 2022)

*3rd module:* *SN(a**)* Spelling Numbers
*a:* single value, positive integer up to 15 digits ( <1E+16, one quadrillion)

```
=LAMBDA(c, [nd],
    LET(
        k, INDEX(DE(), 11, ),
        a, INT(ABS(c)),
        n, IF(LEN(a) > 15, 0, a),
        t, TEXT(n, REPT(0, 15)),
        m, MID(t, SEQUENCE(, 5, , 3), 3),
        h, SH(m),
        x, TEXTJOIN("", , h & IF(h = "", "", k)),
        r, LEFT(TEXTAFTER(c, ".", , , , 0), 2),
        d, IF(LEN(r) = 1, r & 0, r),
        y, SH(d),
        IF(nd, y, x)
    )
)
```
SPLNRS.xlsxABCDEFGH1pattern formulas to2replace typing3=REPT(9,SEQUENCE(16,,16,-1))=E4#&""=--B4#4999999999999999999999999999999901E+16 =SN(E4)59999999999999999999999999999991E+15nine hundred ninety nine trillion nine hundred ninety nine billion nine hundred ninety nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E5)699999999999999999999999999991E+14ninety nine trillion nine hundred ninety nine billion nine hundred ninety nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E6)7999999999999999999999999991E+13nine trillion nine hundred ninety nine billion nine hundred ninety nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E7)89999999999999999999999991E+12nine hundred ninety nine billion nine hundred ninety nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E8)9999999999999999999999999999999999ninety nine billion nine hundred ninety nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E9)10999999999999999999999999999999nine billion nine hundred ninety nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E10)11999999999999999999999999999nine hundred ninety nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E11)12999999999999999999999999ninety nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E12)13999999999999999999999nine million nine hundred ninety nine thousands nine hundred ninety nine=SN(E13)14999999999999999999nine hundred ninety nine thousands nine hundred ninety nine=SN(E14)15999999999999999ninety nine thousands nine hundred ninety nine=SN(E15)16999999999999nine thousands nine hundred ninety nine=SN(E16)17999999999nine hundred ninety nine=SN(E17)18999999ninety nine=SN(E18)19999nine=SN(E19)2021=1&REPT(0,SEQUENCE(15)-1)&1=E22#&""=--B22#22111111eleven=SN(E22)23101101101one hundred one=SN(E23)24100110011001one thousands one=SN(E24)25100011000110001ten thousands one=SN(E25)26100001100001100001one hundred thousands one=SN(E26)27100000110000011000001one million one=SN(E27)28100000011000000110000001ten million one=SN(E28)29100000001100000001100000001one hundred million one=SN(E29)30100000000110000000011000000001one billion one=SN(E30)31100000000011000000000110000000001ten billion one=SN(E31)321000000000011000000000011E+11one hundred billion one=SN(E32)33100000000000110000000000011E+12one trillion one=SN(E33)3410000000000001100000000000011E+13ten trillion one=SN(E34)351000000000000011000000000000011E+14one hundred trillion one=SN(E35)36100000000000000110000000000000001E+15 =SN(E36)373839 - after 15 digits numbers we loose precision (last digit is 0 even if we used 9 and 1) => SN returns empty string ""40SNCell FormulasRangeFormulaB3,D21:E21,B21,D3:E3B3=FORMULATEXT(B4)D4:D19,D22:D36D4=E4#&""E4:E19,E22:E36E4=--B4#B4:B19B4=REPT(9,SEQUENCE(16,,16,-1))F4:F19,F22:F36F4=SN(E4)G4:G19,G22:G36G4=FORMULATEXT(F4)B22:B36B22=1&REPT(0,SEQUENCE(15)-1)&1Dynamic array formulas.


----------



## Xlambda (Sep 18, 2022)

Forgot to mention the new functionality added by second argument "nd". Actually, SN function can handle positive decimal numbers, rounding them to 2 digits.
*SN(c,[nd])* Spell Numbers. Calls *SH(a)*
*c*: single value/cell, positive decimal numbers < 1 quadrillion
*[nd]*: number's decimals argument
- if nd omitted, only INT part of the number is spelled
- if nd 1 or <>0, only number's decimals are spelled
SPLNRS.xlsxABCDEFGHI1=RANDARRAY(40,,1,100000)nd, omitted => spelling INT partnd, 1 => spelling decimals2↓↓↓397702.56825ninety seven thousands seven hundred two=SN(B3)fifty six=SN(B3,1)420409.42607twenty thousands four hundred nine=SN(B4)forty two=SN(B4,1)520746.85651twenty thousands seven hundred forty six=SN(B5)eighty five=SN(B5,1)626932.53347twenty six thousands nine hundred thirty two=SN(B6)fifty three=SN(B6,1)742940.80901forty two thousands nine hundred forty=SN(B7)eighty=SN(B7,1)830311.15541thirty thousands three hundred eleven=SN(B8)fifteen=SN(B8,1)96505.022883six thousands five hundred five=SN(B9)two=SN(B9,1)1072210.82081seventy two thousands two hundred ten=SN(B10)eighty two=SN(B10,1)1177101.23987seventy seven thousands one hundred one=SN(B11)twenty three=SN(B11,1)123902.495388three thousands nine hundred two=SN(B12)forty nine=SN(B12,1)1375828.70297seventy five thousands eight hundred twenty eight=SN(B13)seventy=SN(B13,1)1482240.43437eighty two thousands two hundred forty=SN(B14)forty three=SN(B14,1)1532232.28521thirty two thousands two hundred thirty two=SN(B15)twenty eight=SN(B15,1)1687495.94881eighty seven thousands four hundred ninety five=SN(B16)ninety four=SN(B16,1)1764026.98905sixty four thousands twenty six=SN(B17)ninety eight=SN(B17,1)188524.822263eight thousands five hundred twenty four=SN(B18)eighty two=SN(B18,1)191354.381408one thousands three hundred fifty four=SN(B19)thirty eight=SN(B19,1)2073236.2476seventy three thousands two hundred thirty six=SN(B20)twenty four=SN(B20,1)2180278.38307eighty thousands two hundred seventy eight=SN(B21)thirty eight=SN(B21,1)2211755.34121eleven thousands seven hundred fifty five=SN(B22)thirty four=SN(B22,1)2368140.79487sixty eight thousands one hundred forty=SN(B23)seventy nine=SN(B23,1)2483420.25187eighty three thousands four hundred twenty=SN(B24)twenty five=SN(B24,1)2546341.57505forty six thousands three hundred forty one=SN(B25)fifty seven=SN(B25,1)2683785.53183eighty three thousands seven hundred eighty five=SN(B26)fifty three=SN(B26,1)2788819.41514eighty eight thousands eight hundred nineteen=SN(B27)forty one=SN(B27,1)2831223.40664thirty one thousands two hundred twenty three=SN(B28)forty=SN(B28,1)2958748.10724fifty eight thousands seven hundred forty eight=SN(B29)ten=SN(B29,1)301259.293816one thousands two hundred fifty nine=SN(B30)twenty nine=SN(B30,1)3115428.78558fifteen thousands four hundred twenty eight=SN(B31)seventy eight=SN(B31,1)329053.159079nine thousands fifty three=SN(B32)fifteen=SN(B32,1)3381874.28178eighty one thousands eight hundred seventy four=SN(B33)twenty eight=SN(B33,1)3439330.87146thirty nine thousands three hundred thirty=SN(B34)eighty seven=SN(B34,1)3576535.69963seventy six thousands five hundred thirty five=SN(B35)sixty nine=SN(B35,1)3668282.01577sixty eight thousands two hundred eighty two=SN(B36)one=SN(B36,1)372218.17782two thousands two hundred eighteen=SN(B37)seventeen=SN(B37,1)3813299.91768thirteen thousands two hundred ninety nine=SN(B38)ninety one=SN(B38,1)3959174.64585fifty nine thousands one hundred seventy four=SN(B39)sixty four=SN(B39,1)4087416.30462eighty seven thousands four hundred sixteen=SN(B40)thirty=SN(B40,1)4121197.93743twenty one thousands one hundred ninety seven=SN(B41)ninety three=SN(B41,1)4280459.41039eighty thousands four hundred fifty nine=SN(B42)forty one=SN(B42,1)43Sheet3Cell FormulasRangeFormulaB1B1=FORMULATEXT(B3)B3:B42B3=RANDARRAY(40,,1,100000)D3:D42D3=SN(B3)E3:E42,H3:H42E3=FORMULATEXT(D3)G3:G42G3=SN(B3,1)Dynamic array formulas.


----------



## Xlambda (Sep 18, 2022)

*4th module*: *SPLNRS(ar,[un])*
Main function that assembles all the other modules relationships and does the rest of cosmetics. *Calls SN that calls SH that calls DE.*
To keep it unclustered of too many arguments for unit selection, chose a plain design that defines default unit to be used inside the function's expression, since will not be changed so often.
*ar*: any array of positive or negative decimal numbers
- if ar contains text, errors, blanks/empty strings, or numbers that in absolute value are greater than 1 quadrillion -1, the function will ignore them, returning empty strings instead.
*[un]*: unit argument
- if omitted or 0, default unit's names spelling will be added
The default unit in our case: " Dollars"/" Cents" can be changed inside the function's expression, together with "negative " and " and " words if different translation is needed.
- if 1 or <>0, no unit's name spelling will be added.

```
=LAMBDA(ar, [un],
    LET(
        u, IF(un, "", " Dollars"),
        h, IF(un, INDEX(DE(), 2, 5), " Cents"),
        g, "negative ",
        p, " and ",
        MAP(
            ar,
            LAMBDA(a,
                LET(
                    n, SN(a),
                    d, SN(a, 1),
                    x, IF(
                        n = "",
                        IF(d = "", "", d & h),
                        IF(d = "", n & u, n & u & p & d & h)
                    ),
                    IFERROR(IF(a < 0, g & x, x), "")
                )
            )
        )
    )
)
```
SPLNRS.xlsxABCDEFGHI1=RANDARRAY(20,,-1000000000,1000000000)-1<=ar<=12↓↓↓un, omitted=RANDARRAY(20,,-1,1)3↓↓↓=SPLNRS(B4#)↓↓↓=SPLNRS(F4#)4ar-646420771.8negative six hundred forty six million four hundred twenty thousands seven hundred seventy one Dollars and eighty three Centsar0.393619238thirty nine Cents5-306021547.1negative three hundred six million twenty one thousands five hundred forty seven Dollars and eleven Cents-0.6058294negative sixty Cents6-245872533.4negative two hundred forty five million eight hundred seventy two thousands five hundred thirty three Dollars and forty Cents-0.12291204negative twelve Cents7754545146seven hundred fifty four million five hundred forty five thousands one hundred forty six Dollars-0.75428247negative seventy five Cents8583090548.3five hundred eighty three million ninety thousands five hundred forty eight Dollars and thirty two Cents0.463274936forty six Cents9972446830.9nine hundred seventy two million four hundred forty six thousands eight hundred thirty Dollars and ninety four Cents0.519382307fifty one Cents10254700856.8two hundred fifty four million seven hundred thousands eight hundred fifty six Dollars and seventy nine Cents-0.54502695negative fifty four Cents11237225830.4two hundred thirty seven million two hundred twenty five thousands eight hundred thirty Dollars and thirty seven Cents0.54524941fifty four Cents12-803488160.8negative eight hundred three million four hundred eighty eight thousands one hundred sixty Dollars and eighty Cents0.543190105fifty four Cents13-771685306.8negative seven hundred seventy one million six hundred eighty five thousands three hundred six Dollars and eighty four Cents-0.46412196negative forty six Cents14441633269.8four hundred forty one million six hundred thirty three thousands two hundred sixty nine Dollars and seventy six Cents-0.07641586negative seven Cents15-869418398.2negative eight hundred sixty nine million four hundred eighteen thousands three hundred ninety eight Dollars and twenty one Cents-0.25065356negative twenty five Cents16631463792.4six hundred thirty one million four hundred sixty three thousands seven hundred ninety two Dollars and thirty five Cents0.272158829twenty seven Cents17-157997948.2negative one hundred fifty seven million nine hundred ninety seven thousands nine hundred forty eight Dollars and twenty one Cents-0.8389929negative eighty three Cents18-213953591.5negative two hundred thirteen million nine hundred fifty three thousands five hundred ninety one Dollars and fifty two Cents0.551709823fifty five Cents19-208205080.7negative two hundred eight million two hundred five thousands eighty Dollars and sixty nine Cents-0.33794313negative thirty three Cents20-31539376.39negative thirty one million five hundred thirty nine thousands three hundred seventy six Dollars and thirty eight Cents0.505204421fifty Cents21-95437514.86negative ninety five million four hundred thirty seven thousands five hundred fourteen Dollars and eighty six Cents0.932753939ninety three Cents22610791237.9six hundred ten million seven hundred ninety one thousands two hundred thirty seven Dollars and eighty six Cents0.417721416forty one Cents23-409109942.8negative four hundred nine million one hundred nine thousands nine hundred forty two Dollars and eighty one Cents-0.20266916negative twenty Cents2425even if excel displays26only 1 digit decimals,27more of them are hiddenun,128=B4#&""=SPLNRS(B4#,1)=SPLNRS(F4#,1)29-646420771.831535negative six hundred forty six million four hundred twenty thousands seven hundred seventy one and eighty three hundredth thirty nine hundredth 30-306021547.110803negative three hundred six million twenty one thousands five hundred forty seven and eleven hundredth negative sixty hundredth 31-245872533.406896negative two hundred forty five million eight hundred seventy two thousands five hundred thirty three and forty hundredth negative twelve hundredth 32754545146.000781seven hundred fifty four million five hundred forty five thousands one hundred forty sixnegative seventy five hundredth 33583090548.323593five hundred eighty three million ninety thousands five hundred forty eight and thirty two hundredth forty six hundredth 34972446830.947536nine hundred seventy two million four hundred forty six thousands eight hundred thirty and ninety four hundredth fifty one hundredth 35254700856.799245two hundred fifty four million seven hundred thousands eight hundred fifty six and seventy nine hundredth negative fifty four hundredth 36237225830.370408two hundred thirty seven million two hundred twenty five thousands eight hundred thirty and thirty seven hundredth fifty four hundredth 37-803488160.807744negative eight hundred three million four hundred eighty eight thousands one hundred sixty and eighty hundredth fifty four hundredth 38-771685306.846232negative seven hundred seventy one million six hundred eighty five thousands three hundred six and eighty four hundredth negative forty six hundredth 39441633269.766593four hundred forty one million six hundred thirty three thousands two hundred sixty nine and seventy six hundredth negative seven hundredth 40-869418398.215256negative eight hundred sixty nine million four hundred eighteen thousands three hundred ninety eight and twenty one hundredth negative twenty five hundredth 41631463792.356621six hundred thirty one million four hundred sixty three thousands seven hundred ninety two and thirty five hundredth twenty seven hundredth 42-157997948.212424negative one hundred fifty seven million nine hundred ninety seven thousands nine hundred forty eight and twenty one hundredth negative eighty three hundredth 43-213953591.525161negative two hundred thirteen million nine hundred fifty three thousands five hundred ninety one and fifty two hundredth fifty five hundredth 44-208205080.696565negative two hundred eight million two hundred five thousands eighty and sixty nine hundredth negative thirty three hundredth 45-31539376.3871949negative thirty one million five hundred thirty nine thousands three hundred seventy six and thirty eight hundredth fifty hundredth 46-95437514.8631116negative ninety five million four hundred thirty seven thousands five hundred fourteen and eighty six hundredth ninety three hundredth 47610791237.864715six hundred ten million seven hundred ninety one thousands two hundred thirty seven and eighty six hundredth forty one hundredth 48-409109942.819211negative four hundred nine million one hundred nine thousands nine hundred forty two and eighty one hundredth negative twenty hundredth 49SPLNRSCell FormulasRangeFormulaB1B1=FORMULATEXT(B4)F2F2=FORMULATEXT(F4)B4:B23B4=RANDARRAY(20,,-1000000000,1000000000)D3,H28,D28,B28,H3D3=FORMULATEXT(D4)D4:D23,H4:H23D4=SPLNRS(B4#)F4:F23F4=RANDARRAY(20,,-1,1)B29:B48B29=B4#&""D29:D48,H29:H48D29=SPLNRS(B4#,1)Dynamic array formulas.


----------



## Xlambda (Sep 18, 2022)

SPLNRS.xlsxABCDEFGHI1data types managementun,12"ar" 2D =SPLNRS(B3:D5,1)35432.68-456.725#N/Afive thousands four hundred thirty two and sixty eight hundredth negative four hundred fifty six and seventy two hundredth 4text-37567.19-101001negative thirty seven thousands five hundred sixty seven and nineteen hundredth negative one hundred one thousands one59.87988E+1510101010ten million one hundred one thousands ten67un, omitted8=SPLNRS(B3:D5)9five thousands four hundred thirty two Dollars and sixty eight Centsnegative four hundred fifty six Dollars and seventy two Cents10negative thirty seven thousands five hundred sixty seven Dollars and nineteen Centsnegative one hundred one thousands one Dollars11ten million one hundred one thousands ten Dollars1213SPLNRS 1Cell FormulasRangeFormulaD3D3=NA()F2,F8F2=FORMULATEXT(F3)F3:H5F3=SPLNRS(B3:D5,1)F9:H11F9=SPLNRS(B3:D5)Dynamic array formulas.


----------



## Xlambda (Oct 9, 2022)

jaeiow said:


> It seems like you could just use textbefore/after for this.


You are right @jaeiow. Here is a function that uses both TEXTBEFORE, TEXTAFTER, and not only for spaces, for any characters.
*TRIMENDS(t,ch,[lt])* *Trim leading, trailing characters, or both*. All other chars are left intact.
*t:* text, single cell/value
*ch*: character
*[lt]*: leading, trailing argument: 0 or omitted=>remove all leading "ch" ; if 1, trailing "ch" ; if 2, both leading and trailing

```
=LAMBDA(t, ch, [lt],
    LET(
        n, LEN(t),
        s, SUBSTITUTE(t, ch, ""),
        l, LEFT(s, 1),
        r, RIGHT(s, 1),
        x, LEN(TEXTBEFORE(t, l)),
        y, LEN(TEXTAFTER(t, r, -1)),
        SWITCH(lt, 0, RIGHT(t, n - x), 1, LEFT(t, n - y), 2, MID(t, x + 1, n - x - y))
    )
)
```
ATRIM new.xlsxABCDEFGHIJK12tch,"x", lt, omitted3xxxxjyhgu xxxx-uoygxx xxxouytoyu ytfytfxxxxxx=TRIMENDS(B3,"x")4jyhgu xxxx-uoygxx xxxouytoyu ytfytfxxxxxx56ch,"x",lt,17=TRIMENDS(B3,"x",1)8xxxxjyhgu xxxx-uoygxx xxxouytoyu ytfytf910ch,"x", lt,211=TRIMENDS(B3,"x",2)12jyhgu xxxx-uoygxx xxxouytoyu ytfytf1314Trim both ends of an array of strings for "x"15array=MAP(B16:B17,LAMBDA(x,TRIMENDS(x,"x",2)))16xxxxjyhgu xxxx-uoygxx xxxouytoyu ytfytfxxxxxxjyhgu xxxx-uoygxx xxxouytoyu ytfytf17xxbb-jbi-xxx (lkjh8976) 1234xxxxxxxxxbb-jbi-xxx (lkjh8976) 12341819Trim array acording to first chars (variable, leading=trailing)20=LEFT(B22:B23,1)21array↓↓=MAP(B22:B23,D22#,LAMBDA(x,y,TRIMENDS(x,y,2)))22yyiughi-yuguyutyyyyyyyiughi-yuguyut23zzzzzzzzzzzziugug 9876987zzzzzziugug 98769872425Trim array, variable chars, leading<>trailing, single cell formula2627array=MAP(B28:B29,LEFT(B28:B29,1),RIGHT(B28:B29,1),LAMBDA(x,y,z,TRIMENDS(TRIMENDS(x,y),z,1)))28yyyljhgl765 ugugu976987wwwwwljhgl765 ugugu97698729xxxxx7865jhgj jhgjhg8765zzzzzzzzzzzz7865jhgj jhgjhg876530trim endsCell FormulasRangeFormulaG3,G21,G15,G11,G7G3=FORMULATEXT(G4)G4G4=TRIMENDS(B3,"x")G8G8=TRIMENDS(B3,"x",1)G12G12=TRIMENDS(B3,"x",2)G16:G17G16=MAP(B16:B17,LAMBDA(x,TRIMENDS(x,"x",2)))D20D20=FORMULATEXT(D22)D22:D23D22=LEFT(B22:B23,1)G22:G23G22=MAP(B22:B23,D22#,LAMBDA(x,y,TRIMENDS(x,y,2)))D27D27=FORMULATEXT(G28)G28:G29G28=MAP(B28:B29,LEFT(B28:B29,1),RIGHT(B28:B29,1),LAMBDA(x,y,z,TRIMENDS(TRIMENDS(x,y),z,1)))Dynamic array formulas.


----------

