Forum Discussion

dipankar_de's avatar
dipankar_de
Copper Contributor
Nov 12, 2022

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...
Formulas and Functions
dscheikey's avatar
dscheikey
Bronze Contributor
Nov 12, 2022

dipankar_de 

At first I thought that this could be solved quite simply with TEXTSPLIT() within BYROW(). But that always resulted in a CALC# error.

I then solved this rather oldscool with MID() SEARCH() and SEQUENCE().

There are probably other variants.

 

=LET(
in,SUBSTITUTE("; "&Table2[Values]&"; "," ",""),
maxi,MAX(LEN(in)-LEN(SUBSTITUTE(in,";","")))-1,
start,SEARCH(CHAR(124),SUBSTITUTE(in,";",CHAR(124),SEQUENCE(1,maxi,1))),
end,SEARCH(CHAR(124),SUBSTITUTE(in,";",CHAR(124),SEQUENCE(1,maxi,2))),
HSTACK(Table2['#],IFERROR(MID(in,start+1,end-start-1),"")))

 

 

I hope that's OK for you.

  • mtarler's avatar
    mtarler
    Silver Contributor
    Nov 12, 2022

    dscheikey  alternative using REDUCE and VSTACK:

    =LET(colOne, Table2['#], colRest, Table2[Values], splittedData, 
    IFERROR(DROP(
       REDUCE("",colRest, 
          LAMBDA(p,q, VSTACK(p, TEXTSPLIT(q, "; ", , FALSE, 1, "")))
          )
       ,1),""),
    HSTACK(colOne, splittedData))

Resources