Mar 19 2021 12:14 PM
Mar 19 2021 12:14 PM
Based on the responses to this question, I decided to try the answer out on a function I picked up somewhere, which splits a string based on a specified delimiter.
Using a LAMBDA function without default parameters, and feeding it the two parameters (the string - several words with space between them, specified in A1 - and the delimiter - space, for this example):
=LAMBDA(string,delimiter, TRANSPOSE(FILTERXML("<t><s>" & SUBSTITUTE(string, delimiter, "</s><s>") & "</s></t>", "//s") ) )(A1," ")
everything works as expected.
Setting space as the default parameter and NOT including it in the parameters submitted, also works as expected:
=LAMBDA(string,delimiter, TRANSPOSE( FILTERXML( "<t><s>" & SUBSTITUTE( string, IF(delimiter,delimiter," "), "</s><s>" ) & "</s></t>", "//s" ) ) )(A1,)
However, feeding both parameters to the same LAMBDA (whether the delimiter is space or anything else):
raises a #VALUE! error.
What to do?
Mar 19 2021 12:46 PMSolution
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.
Mar 19 2021 02:01 PM
Thanks! I didn't realize IF("TEXT",... raises an error. I'll use your suggestion or IF(delimiter<>"",delimiter," ").
Mar 19 2021 07:33 PM
Mar 19 2021 08:25 PM
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 ) )
Mar 20 2021 03:05 AM
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) ))
Mar 20 2021 05:15 AM
Mar 20 2021 05:28 AM
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.
Mar 20 2021 05:54 AM
Mar 20 2021 06:01 AM