VSTACK (and TEXTSPLIT) on dynamic ranges

Question

I am trying to combine the output of many dynamic functions into one column.

I have come across this problem before in various forms.

Currently I am trying to take comma separated data and create a single column of all values.

I can create the output in C1 with =VSTACK(TEXTSPLIT(A1,,","), TEXTSPLIT(A2,,","), TEXTSLIT....) but this is manual and does not allow for dynamic changing of data in A1 in length.

I am looking for a dynamic formula. I can't seem to get VSTACK and TEXTSPLIT to play nicely together dynamically.

Answer 1

Building on discussions/concerns about speed/limits formed around other answers, maybe the whole issue with speed is the use of LAMBDA() which essentially iterates a given array. Maybe we can avoid this and the limits of TEXTJOIN() at the same time. My two cents to counter this:

Formula in B1:

=LET(x,A1:A4,TOCOL(TEXTAFTER(","&TEXTBEFORE(x&",",",",SEQUENCE(,MAX(LEN(x)-LEN(SUBSTITUTE(x,",",))+1)),,,NA()),",",-1),3))

---

EDIT: Did some benchmarking against 10000 rows with comma-seperated data == 'a,b,c', meaning splitting and stacking would result in 30000 rows of data. For this test I used this source-code where I'd tell excel to calculate manually and wait while Excel is busy:

Rows/Function	MAKEARRAY()	REDUCE() & VSTACK()	TEXTBEFORE/AFTER()
10000	52.68 sec	34.82 sec	0.09 sec
100000	crash	crash	0.59 sec

I tried benchmarking against 100000 rows which proven too much to handle for LAMBDA() related functions causing Excel to freeze/crash. It worked for the above mentioned function in 0.59 seconds which supprised me tbh.

I also found there is just a slight, almost unnoticable/neglectable, setback when simplifying the above to:

=LET(x,A1:A4,TOCOL(TEXTAFTER(","&TEXTBEFORE(x&",",",",SEQUENCE(,MAX(LEN(x))),,,NA()),",",-1),3))

Or even, as per @DavidLeal:

=LET(x,A1:A4,TOCOL(TEXTBEFORE(TEXTAFTER(","&x,",",SEQUENCE(,MAX(LEN(x))))&",",","),3))

Answer 2

Perhaps try this way, and it would work for any number of rows:

---

• Formula used in cell C1

=DROP(REDUCE("",A1:A4,LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,",")))),1)

---

Also, the formula credit wholly goes to JvdV Sir, without any doubt.

---

Here is the example query where I specifically came to know about it:

How to split texts from dynamic range?

---

Edit:

---

We can also wrap in one more function viz. TOCOL() to avoid the blank cells, I have specifically used 3 the optional parameter to [ignore] because it ignores blanks as well as any errors if any. Therefore the formula will be.

---

• Formula used in cell C1

=DROP(REDUCE("",TOCOL(A1:A4,3),LAMBDA(x,y,VSTACK(x,TEXTSPLIT(y,,",")))),1)

---