Skip to content

Instantly share code, notes, and snippets.

@SergeiStPete
Last active June 15, 2023 10:04
Show Gist options
  • Save SergeiStPete/8a721d4e2461bb6a91eb3679360d45e6 to your computer and use it in GitHub Desktop.
Save SergeiStPete/8a721d4e2461bb6a91eb3679360d45e6 to your computer and use it in GitHub Desktop.
EXCEL SplitText() with Lambda
/*
Waiting for a native function simple SPLITTEXT() is here.
- no error handling
- no help string
- separators between quotes are not ignored
Use:
=SPLITTEXT( text, separators )
if separator is missed comma is taken by default
examples:
=splitText("a,b=c,d=e", {",", "="})
returns {"a", "b", "c", "d", "e"}
=splitText("a,b=c,d=e")
returns {"a", "b=c", "d=e"}
Sergei Baklan
Date: 2022-02-09
Modified:
2022-02-14 - multiple separators are added
------------------------------------------------------*/
splitText = LAMBDA(str, [sepPar],
LET(
sep, IF(ISOMITTED(sepPar), ",", sepPar),
seps, MAKEARRAY(
COLUMNS(sep),
1,
LAMBDA(r, c, INDEX(sep, r))
),
sepPosition, MIN(IFERROR(SEARCH(seps, str), "")),
IF(
sepPosition = 0,
str,
expandArrayLeft(
LEFT(str, sepPosition - 1),
splitText(
RIGHT(str, LEN(str) - sepPosition),
sep
)
)
)
)
);
expandArrayLeft = LAMBDA(value, array,
LET(
columnIndex, SEQUENCE(, COLUMNS(array) + 1, 0),
IF(
columnIndex,
INDEX(array, , columnIndex),
value
)
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment