Last active
August 23, 2022 00:21
-
-
Save ExcelRobot/2767d2b1c83540a7351d62a1cb18efe2 to your computer and use it in GitHub Desktop.
Excel Text To Columns LAMBDA function
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
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,{"&&";"<<";">>";"''";"""""}, | |
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 | |
)) | |
; |
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.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
super helpful. Thanks!
I would propose renaming it TEXTSPLIT and having an optional 3rd parameter to output into columns/Rows using TRANSPOSE