Today’s function is SUPER_TEXTSPLIT. Splits multiple text values into rows and columns.
LAMBDA(text,col_delimiter,row_delimiter,[ignore_empty],[match_mode],[pad_with]=
LET(textSpill, DROP(REDUCE({""}, SEQUENCE(, COLUMNS(text)),
LAMBDA(a,v, HSTACK(a, DROP(REDUCE({""}, CHOOSECOLS(text, v), LAMBDA(a,v, VSTACK(a,
IF(v = "", "", TEXTSPLIT(v, col_delimiter, row_delimiter, IF(ISOMITTED(ignore_empty), FALSE, ignore_empty), IF(ISOMITTED(match_mode), 0, match_mode), IF(ISOMITTED(pad_with), NA(), pad_with))
)))), 1)))), , 1),
result, IF(ISOMITTED(pad_with), textSpill, IFERROR(textSpill, pad_with)),
result))
𝐒𝐘𝐍𝐓𝐀𝐗
SUPER_TEXTSPLIT(text, col_delimiter, row_delimiter, [ignore_empty], [match_mode], [pad_with])
• text: The text to split (can be multiple cells!!!)
• col_delimiter: The text which marks the point to start a new column
• row_delimiter: The text which marks the point to start a new row
• [ignore_empty]: Include (TRUE) or exclude (FALSE) empty strings.
• [match_mode]: 0 = Case sensitive (default), 1 = Case insensitive
• [pad_with]: Value with which to pad empty values
Note: ignore_empty relates to empty values within text, not empty cells.
𝐄𝐗𝐀𝐌𝐏𝐋𝐄
Splits the text values in B3:B10 based on using the colon ( : ) for creating new columns and the line break (CHAR(10)) to create new rows.
=SUPER_TEXTSPLIT(B3:B10,":",CHAR(10))
ليست هناك تعليقات:
إرسال تعليق