Forum Discussion

JackTradeOne's avatar
JackTradeOne
Copper Contributor
Mar 19, 2021
Solved

Default values for LAMBDA paramters - Part II

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 de...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Mar 19, 2021

    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.

JackTradeOne's avatar
JackTradeOne
Copper Contributor
Mar 19, 2021

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

tboulden's avatar
tboulden
Iron Contributor
Mar 20, 2021

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

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 20, 2021

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

     

    • tboulden's avatar
      tboulden
      Iron Contributor
      Mar 20, 2021
      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.
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Mar 20, 2021

        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.

Resources