Forum Discussion
Using TEXTSPLIT without copy-fill-down
Hello All, I am trying to split "; " separated range of text into columns. The intent is to use one single spill-formula (instead of copy-fill-down) to address each text in the range. I have...
I'd simplify to
=LET(
data, HSTACK( Table2['#], TEXTSPLIT( TEXTJOIN("|",,Table2[Values]), ";","|",,,"") ),
header, HSTACK( {"#","Key"}, "Value" & SEQUENCE(, COLUMNS(data) - 2 ) ),
VSTACK( header, data )
)- Hello Sergei,
This doesn't work when the length of the string, containing col_delimited text in the range, is more than 32,767 characters due to Excel limitations. This is the reason I requested for help on this forum for a single spill array formula that will process each col_delimited string in each row individually but will output the results for the entire range in one go.
Both CONCAT and TEXTJOIN have the 32,767-character restriction and hence the problem.
the example spreadsheet can be found at https://1drv.ms/x/s!Aqyz05eCPz3-rWwakzdAFKiYsjtH?e=ydgpxa in which you can look through any of the worksheets than the TEXTSPLIT worksheet for the near real-world problem.
Warm regards,
Dipankar De
