Skip to content

Instantly share code, notes, and snippets.

@YeeBePrime

YeeBePrime/excel-lambda-textsplitArr.txt

Created September 18, 2022 01:45
Show Gist options
  • Save YeeBePrime/ae9c26ca29a1b558dc57dc6236280e7f to your computer and use it in GitHub Desktop.
Save YeeBePrime/ae9c26ca29a1b558dc57dc6236280e7f to your computer and use it in GitHub Desktop.
Download ZIP
Excel lambda Splits text strings , support 1 dimension array.
Raw
excel-lambda-textsplitArr.txt
/*
TEXTSPLITARR
Splits text strings by using column and row delimiters. Support 1 dimension array.
The TEXTSPLITARR function works LIKE the built-in TEXTSPLIT function but support 1 dimension text array
Syntax
=TEXTSPLITARR (text,col_delimiter,[row_delimiter])
The TEXTSPLITARR function syntax has the following arguments:
text The text / one-dimension text array you want to split. Required.
col_delimiter The text that marks the point where to spill the text across columns.
row_delimiter The text that marks the point where to spill the text down rows. Optional.
Remark: do not support multple delimiters, in such case use substituteMult() before textsplitArr()
Examples
A2=abc,de,fgh
A3=xy,wv
=TEXTSPLITARR(A2:A3,",")
Return
B2=abc C2=de D2=fgh
B3=xy C3=wv
*/
TEXTSPLITARR=LAMBDA(textArray, col_delimiter, [row_delimiter],
LET(
_textA, FILTER(
textArray,
textArray <> 0
),
_del, IF(
ISOMITTED(
col_delimiter
),
row_delimiter,
col_delimiter
),
_delCnt, LEN(_textA) -
LEN(
SUBSTITUTE(
_textA,
_del,
""
)
),
seqA, SEQUENCE(
,
MAX(_delCnt + 1),
1,
1
),
_sub, TRIM(
MID(
SUBSTITUTE(
_textA,
_del,
REPT(
" ",
LEN(
_textA
)
)
),
IF(
seqA = 1,
1,
1 +
(seqA *
LEN(
_textA
)) -
LEN(
_textA
)
),
LEN(_textA)
)
),
IF(
ISOMITTED(
col_delimiter
),
MAKEARRAY(
COLUMNS(_sub),
ROWS(_sub),
LAMBDA(r, c,
INDEX(
_sub,
c,
r
)
)
),
_sub
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment