Loop through a column and vstack to a new single column using formula-CodePudding

I have a column of strings, example below. Each string is a delimited combinations of texts. Each row has different number of texts. I want to create a single column with one text per row based on this column.

FROM:

a;b
x;y;z
p;q;r;s;t

TO:

a
b
x
y
z
p
q
r
s
t

How do I achieve this using a single formula?

I tried TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",TRUE, data),";")) However, this fails because the TEXTJOIN part results in more than 32767 characters.

I also tried building a 2D array of mxn where m=no. of rows in the original data and n=no. of texts. However, the MAKEARRAY still results in a single column. Had it worked, I would have used TOCOL or something similar to convert to a single column.

=MAKEARRAY(ROWS(data),COLUMNS(MAX(num_of_texts_in_each_row)), LAMBDA(r,c, LET(
drow, INDEX(data,r,1),
splits, TEXTSPLIT(drow,";"),
INDEX(splits,,c)
)))

CodePudding user response：

Another approach would be by using REDUCE:

=DROP(REDUCE(0,A1:A3,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,,";")))),1)

REDUCE behaves like a BYROW, where the VSTACK stacks the spilled result per row on top of eachother after the full spill value. As it starts at 0, we use DROP the first value to get the desired result.

We could also avoid DROP, but that makes the formula more complicated and longer, but for reference: =REDUCE(TEXTSPLIT(A1,,";"),A2:A3,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,,";"))))

CodePudding user response：

Use:

=LET(
    rng,A1:A3,
    clm,MAX(BYROW(rng,LAMBDA(a,COUNTA(TEXTSPLIT(a,";"))))),
    TOCOL(MAKEARRAY(ROWS(rng),clm,LAMBDA(a,b,INDEX(TEXTSPLIT(INDEX(A1:A3,a),";"),b))),3))