Default values for LAMBDA paramters - Part II

[JackTradeOne]

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):

(A1," ")

raises a #VALUE! error.

What to do?

[#2 · Accepted solution]

@JackTradeOne 

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 &amp; . Similar for some other characters.

[#3]

Thanks! I didn't realize IF("TEXT",... raises an error. I'll use your suggestion or IF(delimiter<>"",delimiter," ").

As to your comment about the problem with using FILTERXML to split strings by delimiter - I definitely agree, but I have to say this is the only solution I know of to do that for strings with more than two delimiters (those can be split with RIGHT, LEFT, MID). It's really strange that something as basic as this, which in (for example) python and javascript can be accomplished with something as simple as string.split(" ") requires people to resort to gymnastics such as converting the string to xml, running an xpath expression on it and then transposing the output...

[#4]

VBA also has a split function. Maybe you could make a user defined wrapper function for it and use that instead of filterxml?

[#5]

@JackTradeOne 

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
    )
)

[#6]

@tboulden 

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)
))

[#7]

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.

[#8]

@tboulden 

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.

[#9]

Yes, I would like very much if we get a native function, and it may be wishful thinking to hope for native functions that handle regular expressions or similar! In the meantime, its good practice for me to come up with or see different versions and learn new ways of completing same task.

[#10]

---

still hope native TEXTSPLIT() appears in Excel.

---

@Sergei Baklan 

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.

[#11]

Indeed, but the whole idea was to create an internal Excel function without resorting to VBA, Office JS or whatever. I definitely don't like having to use FILTERXML (or any of the alternative proposed above) - it's ridiculous that we have to resort to this kind of trickery for something that should a built in function.