Excel Text To Columns LAMBDA function

TEXTTOCOLUMNS.lambda

/*
Name: Split Text To Columns (TEXTTOCOLUMNS)
Description: Separates text into columns similar to Excel's Text To Column feature. 
   One or more delimiters can be specified as an array, but only one multi-characters
   delimiter is supported (default: comma).  If a quoted identfier is specified, any
   delimiters between quotes will be ignored and outer quotes will be removed. Also 
   supports different characters for left quote and right quote by specifying two 
   character quoted identifier (ie: ʻʼ).
Author: Excel Robot (@ExcelRobot)
Category: Text
*/
TEXTTOCOLUMNS = LAMBDA(Text,[Delimiter],[QuoteIdentifier],LET(
    LeftQuote, IF(ISOMITTED(QuoteIdentifier),"",LEFT(QuoteIdentifier,1)),
    RightQuote, IF(ISOMITTED(QuoteIdentifier),"",RIGHT(QuoteIdentifier,1)),
    Delimiters, IFS(
        ISOMITTED(Delimiter), ",",
        COLUMNS(Delimiter)>1, TRANSPOSE(Delimiter),
        TRUE, Delimiter
        ),
    MultiCharDelimiter, IFERROR(INDEX(FILTER(Delimiters,LEN(Delimiters)>1),1),""),
    EscapeChar, CHAR(135),
    EscapeMultiCharDelimiter, SUBSTITUTE(Text,MultiCharDelimiter,EscapeChar),
    Letters, MID(EscapeMultiCharDelimiter,SEQUENCE(,LEN(EscapeMultiCharDelimiter)),1),
    IsQuote, IFNA(IF((Letters=LeftQuote)+(Letters=RightQuote),1,0),0),
    FindQuotes, SCAN(0,Letters,
        LAMBDA(s,a,
            IFS(
                a=LeftQuote, IF(LeftQuote=RightQuote,IF(s=0,1,0),s+1),
                a=RightQuote, s-1,
                TRUE, s
                )
            )
        )-IsQuote,
    InQuotes, IF(FindQuotes>0,FindQuotes,0),
    IsDelimiter, --((BYCOL(--(Letters=Delimiters)*SEQUENCE(ROWS(Delimiters)),
        LAMBDA(delim_match,MAX(delim_match)))+(Letters=EscapeChar))>0
        ),
    UnescapeMultiChar, SUBSTITUTE(Letters,EscapeChar,MultiCharDelimiter),
    EscapeXML, REDUCE(UnescapeMultiChar,{"&&amp;";"<&lt;";">&gt;";"'&apos;";"""&quot;"},
        LAMBDA(s,a,SUBSTITUTE(s,LEFT(a,1),RIGHT(a, LEN(a)-1)))
        ),
    XMLText, "<r><c>"&CONCAT(IF((IsDelimiter=1)*(1-InQuotes),"</c><c>",EscapeXML))&"</c></r>",
    SplitXML, IFERROR(TRANSPOSE(FILTERXML(XMLText,"//c")),""),
    RemoveQuotes, IF((LEFT(SplitXML,1)=LeftQuote)*(RIGHT(SplitXML,1)=RightQuote),
        IF(LEN(SplitXML)=2,"",SUBSTITUTE(
            MID(SplitXML,2,LEN(SplitXML)-2),
            LeftQuote&RightQuote,
            IF(LeftQuote=RightQuote,LeftQuote,"")
            )),
        SplitXML
        ),
    RemoveQuotes
    ))
;

Fixed two bugs: 1) consecutive delimiters now return blank cell instead of #VALUE! error, and 2) double quotes that are in quotes are now undoubled (ie: if you are splitting 100,"Austin, TX","""The Live Music Capital of the World""" by commas with " as your quoted delimiter, you should get back three cells: [ 100 ][ Austin, TX ][ "The Live Music Capital of the World" ].)

super helpful. Thanks!
I would propose renaming it TEXTSPLIT and having an optional 3rd parameter to output into columns/Rows using TRANSPOSE

super helpful. Thanks! I would propose renaming it TEXTSPLIT and having an optional 3rd parameter to output into columns/Rows using TRANSPOSE

I'm glad you like it!

TEXTSPLIT seems like an appropriate name but I already have a TEXTSPLIT lambda that mimics the TEXTSPLIT function that is currently available in Beta and will be available to everyone on 365 soon. It's available here if you want to check it out: Text Manipulation LAMBDA Functions

Thanks for the suggestion on a TRANSPOSE parameter. I think I would opt for just wrapping the function in a TRANSPOSE function instead just to keep things simple.