Forum Discussion

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

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

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
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.
- JackTradeOne
  Copper Contributor
  Mar 20, 2021
  
  still hope native TEXTSPLIT() appears in Excel.
  SergeiBaklan
  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.
- tboulden
  Iron Contributor
  Mar 20, 2021
  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.