Xlambda
Well-known Member
- Joined
- Mar 8, 2021
- Messages
- 832
- Office Version
- 365
- Platform
- Windows
ATEXTSPLIT array text split by any delimiter,(one or more chars), fine-tuned for complex scenarios.
Wrote this function inspired by MrExcel video: Excel TEXTJOIN And The Need For TEXTSPLIT with Ann K. Emery - 2407
The idea was to create a function that is clean and robust, does not call other functions, non-recursive, no FILTERXML, and uses same arguments as TEXTJOIN. What TEXTJOIN joins, ATEXTSPLIT should be able to split it back like it was, with same arguments. When we join, we choose the delimiter, but when we split imported data, things can go quite tricky.
ar: array 1D vertical, dl: delimiter, any string , ea: empty argument , 0 or omitted - ignores empty ; 1 - does not ignore empty
Note: There are 2 special characters in the formula, (h,"º",d,"ª"), first variables after "LET" . These should be changed if any of them is found on the array of strings. Formula advises if this happens.
Wrote this function inspired by MrExcel video: Excel TEXTJOIN And The Need For TEXTSPLIT with Ann K. Emery - 2407
The idea was to create a function that is clean and robust, does not call other functions, non-recursive, no FILTERXML, and uses same arguments as TEXTJOIN. What TEXTJOIN joins, ATEXTSPLIT should be able to split it back like it was, with same arguments. When we join, we choose the delimiter, but when we split imported data, things can go quite tricky.
ar: array 1D vertical, dl: delimiter, any string , ea: empty argument , 0 or omitted - ignores empty ; 1 - does not ignore empty
Note: There are 2 special characters in the formula, (h,"º",d,"ª"), first variables after "LET" . These should be changed if any of them is found on the array of strings. Formula advises if this happens.
Excel Formula:
=LAMBDA(ar,dl,ea,
LET(h,"º",d,"ª",ch,ISNUMBER(SEARCH(CHOOSE({1,2},h,d),ar)),
a,SUBSTITUTE(ar,dl,d),b,IF(ea,a,SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(a," ",h),d," "))," ",d),h," ")),
n,LEN(b)-LEN(SUBSTITUTE(b,d,""))+1,c,MAX(n),sc,SEQUENCE(,c),
x,SEARCH(h,SUBSTITUTE(d&b,d,h,sc)),y,SEARCH(h,SUBSTITUTE(b&d,d,h,sc)),m,IFERROR(MID(b,x,y-x),""),
IF(OR(ch),"change special chars",IFERROR(--m,m))
)
)| LAMBDA 7.0.xlsx | |||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | |||
| 1 | TEXTJOIN | ATEXTSPLIT | |||||||||||||||||||||||||||||||||||
| 2 | empty ignored | empty not ignored | 2 chars | empty ignored | |||||||||||||||||||||||||||||||||
| 3 | empty not ignored | ea arg.=0 | ea arg.=1 | delimiter | ea arg.=0 | ||||||||||||||||||||||||||||||||
| 4 | sample | =TEXTJOIN(",",0,A5:D5) | =ATEXTSPLIT(F5:F7,",",) | =ATEXTSPLIT(F5:F7,",",1) | =ATEXTSPLIT(T5:T7,"><",) | LEN check | |||||||||||||||||||||||||||||||
| 5 | a | 4 | c | a,4,,c | a | 4 | c | a | 4 | c | a><6.8><t | a | 6.8 | t | 1 | 3 | 1 | ||||||||||||||||||||
| 6 | d | 0.5 | d,,,0.5 | d | 0.5 | d | 0.5 | ><2.4 w>< | 2.4 w | 6 | 0 | 0 | |||||||||||||||||||||||||
| 7 | g h | i 2 | k l | 3 n | g h,i 2,k l,3 n | g h | i 2 | k l | 3 n | g h | i 2 | k l | 3 n | <Ac4><gh>< >< | <Ac4 | gh | 4 | 2 | 1 | ||||||||||||||||||
| 8 | space | ||||||||||||||||||||||||||||||||||||
| 9 | empty ignored | ea arg.=0 | ea arg.=1 | empty not ignored | |||||||||||||||||||||||||||||||||
| 10 | =TEXTJOIN(",",,A5:D5) | =ATEXTSPLIT(F11:F13,",",) | =ATEXTSPLIT(F11:F13,",",1) | ea arg.=1 | |||||||||||||||||||||||||||||||||
| 11 | a,4,c | a | 4 | c | a | 4 | c | =ATEXTSPLIT(T5:T7,"><",1) | LEN check | ||||||||||||||||||||||||||||
| 12 | d,0.5 | d | 0.5 | d | 0.5 | a | 6.8 | t | 1 | 3 | 1 | 0 | |||||||||||||||||||||||||
| 13 | g h,i 2,k l,3 n | g h | i 2 | k l | 3 n | g h | i 2 | k l | 3 n | 2.4 w | 0 | 6 | 0 | 0 | |||||||||||||||||||||||
| 14 | <Ac4 | gh | 4 | 2 | 1 | 0 | |||||||||||||||||||||||||||||||
| 15 | Complex scenario | space followed by an empty string | |||||||||||||||||||||||||||||||||||
| 16 | empty ignored | empty not ignored | |||||||||||||||||||||||||||||||||||
| 17 | ea arg.=0 | ea arg.=1 | error debug. | ||||||||||||||||||||||||||||||||||
| 18 | sample | =ATEXTSPLIT(F19:F22,",",) | =ATEXTSPLIT(F19:F22,",",1) | =ATEXTSPLIT(X19,",",) | |||||||||||||||||||||||||||||||||
| 19 | a,b,c,2.3,,d e, | a | b | c | 2.3 | d e | a | b | c | 2.3 | d e | a,ºb,c | change special chars | ||||||||||||||||||||||||
| 20 | ,,g,,h, ,1.8 | g | h | 1.8 | g | h | 1.8 | =ATEXTSPLIT(X21,",",1) | |||||||||||||||||||||||||||||
| 21 | , k m, ,4.9, ,p | k m | 4.9 | p | k m | 4.9 | p | a,ªb,c | change special chars | ||||||||||||||||||||||||||||
| 22 | , ,,x 4, ,, | x 4 | x 4 | ||||||||||||||||||||||||||||||||||
| 23 | |||||||||||||||||||||||||||||||||||||
| 24 | Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument | ||||||||||||||||||||||||||||||||||||
| 25 | -two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument | ||||||||||||||||||||||||||||||||||||
| 26 | We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters) | ||||||||||||||||||||||||||||||||||||
| 27 | |||||||||||||||||||||||||||||||||||||
| 28 | =LEN(H19#) | =LEN(N19#) | |||||||||||||||||||||||||||||||||||
| 29 | 1 | 1 | 1 | 3 | 3 | 1 | 1 | 1 | 3 | 0 | 3 | 0 | |||||||||||||||||||||||||
| 30 | 1 | 1 | 1 | 3 | 0 | 0 | 0 | 1 | 0 | 1 | 1 | 3 | |||||||||||||||||||||||||
| 31 | 4 | 1 | 3 | 1 | 1 | 0 | 4 | 1 | 3 | 1 | 1 | 0 | |||||||||||||||||||||||||
| 32 | 1 | 5 | 1 | 0 | 0 | 0 | 1 | 0 | 5 | 1 | 0 | 0 | |||||||||||||||||||||||||
| 33 | |||||||||||||||||||||||||||||||||||||
| 34 | - if values themselves embed inner spaces, could be important to keep them (like special codes with fixed length that follow a pattern),TRIM tweaks can not be used | ||||||||||||||||||||||||||||||||||||
| 35 | |||||||||||||||||||||||||||||||||||||
| 36 | ex. code pattern | sample | =ATEXTSPLIT(F37:F38,"/",) | =LEN(H37#) | |||||||||||||||||||||||||||||||||
| 37 | fixed with 4 pos. | a bc/a b/ bcd | a bc | a b | bcd | 4 | 4 | 4 | |||||||||||||||||||||||||||||
| 38 | /_ | _ | _ | _ / | ab d/ d/a | ab d | d | a | 4 | 4 | 4 | ||||||||||||||||||||||||||
| 39 | /x | x | x | x/ | check:pattern is kept | ||||||||||||||||||||||||||||||||
| 40 | |||||||||||||||||||||||||||||||||||||
ATEXTSPLIT post | |||||||||||||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H4,N36,H36,N28,H28,Z18,Z20,N18,H18,F10,X11,N10,H10,F4,X4,N4 | H4 | =FORMULATEXT(H5) |
| H5:K7,H11:K13 | H5 | =ATEXTSPLIT(F5:F7,",",) |
| N5:Q7,N11:Q13 | N5 | =ATEXTSPLIT(F5:F7,",",1) |
| X5:Z7 | X5 | =ATEXTSPLIT(T5:T7,"><",) |
| AC5:AE7,AC12:AF14 | AC5 | =LEN(X5#) |
| F5:F7 | F5 | =TEXTJOIN(",",0,A5:D5) |
| X12:AA14 | X12 | =ATEXTSPLIT(T5:T7,"><",1) |
| F11:F13 | F11 | =TEXTJOIN(",",,A5:D5) |
| H19:L22 | H19 | =ATEXTSPLIT(F19:F22,",",) |
| N19:T22 | N19 | =ATEXTSPLIT(F19:F22,",",1) |
| Z19 | Z19 | =ATEXTSPLIT(X19,",",) |
| Z21 | Z21 | =ATEXTSPLIT(X21,",",1) |
| H29:L32,N29:T32 | H29 | =LEN(H19#) |
| H37:J38 | H37 | =ATEXTSPLIT(F37:F38,"/",) |
| N37:P38 | N37 | =LEN(H37#) |
| Dynamic array formulas. | ||
Upvote
0
