Forum Discussion
Default values for LAMBDA paramters - Part II
That's since IF("text", ... returns #VALUE!. In previous sample I assumed using of numbers.
Here you may change on IF(delimiter<>0,delimiter," ")
As a comment, I'd do not recommend to use FILTERXML as general function for splitting the text, only if we assume if the text has no inside some special symbols which are not recognized by this function. For example, splitting of a,b&c,d returns an error since instead of & you shall use & . Similar for some other characters.
Here's my latest iteration implementing a text split LAMBDA; you can provide a delimiter to split on, or if you leave that parameter blank, it splits on each char:
=LAMBDA(str,del,
LET(
dsd,del&str&del,
posArray,
IF(
ISBLANK(del),SEQUENCE(LEN(str)+1),
FIND(
CHAR(1),
SUBSTITUTE(
dsd,
del,
CHAR(1),
SEQUENCE((LEN(dsd)-LEN(SUBSTITUTE(dsd,del,"")))/LEN(del))
)
)
)-1,
startPosArray,INDEX(posArray,SEQUENCE(ROWS(posArray)-1)),
splitPosArray,INDEX(posArray,SEQUENCE(ROWS(posArray)-1,1,2,1)),
charArray,MID(str,startPosArray+1,splitPosArray-startPosArray-LEN(del)),
valArray,IFERROR(VALUE(charArray),charArray),
valArray
)
)
Couple of cosmetics: it splits vertically, more logical horizontally (however that cold be defined by parameter) and if separator is not defined it splits on characters. I'd suggest
=LAMBDA(str,del,
LET(
sep, IF(del=0,",",del),
TEXTCountSeps,
LAMBDA(str,sep,
SUM(
LET(
n, LEN(str),
k, SEQUENCE(n),
IF(n=0, "", 1*(MID(str,k,1)=sep))
))
),
TextSepPositions,
LAMBDA(str,sep,up,
LET(
n, LEN(str),
k, SEQUENCE(,n+up,up),
arr, IF(k>n, n,
IFERROR( IF(
1*(MID(str,k,1)=sep),
k+1-2*up,
0 ),
1) ),
FILTER(arr,arr)
)
),
TextSplit, LAMBDA(str,sep,
LET(
start, TextSepPositions(str, sep, 0),
end, TextSepPositions(str, sep, 1),
TRIM(MID(str, start, end-start+1))
)
),
TextSplit(str, sep)
))
- Hi Sergei, very nice, I'll review more in depth, however your version assumes comma if delimiter isn't provided, mine splits on each char if not provided; also, mine handles delimiters where LEN(del)>1. I like to handle this case because sometimes I want to be sure I'm not splitting on part of my string inadvertently; I like to do TEXTJOIN with #@# or similar in case there is # or @ in cells.
You are right, that's limited version. I have couple more, with separator as text string and another one which ignores separators within the quotes. For example, "a,b",c will be split on a,b | c , not on a | b | c.
So far didn't merge all variants in one universal functions, still hope native TEXTSPLIT() appears in Excel.
still hope native TEXTSPLIT() appears in Excel.
I really hope that happens and happens soon. It's almost depressing to look at the size of the suggested LAMBDA functions necessary to perform such a basic task.
