single formula to cover entire array column?

[excelNewbie22]

hi,
how can i use a formula with an array instead of fill down?
like in the example, i want the formula in i2 to split the text of all the array of col h
like

=TEXTSPLIT(H2,"-",,,,)

only to cover all col h
so if add # like

=TEXTSPLIT(H2#,"-",,,,)

it returns just the first number, like in p2

possible?

test.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	1	2	3	4	5	6	7
2								1-2-3-4-5-6	1	2	3	4	5	6		1
3								1-2-3-4-5-7								1
4								1-2-3-4-6-7								1
5								1-2-3-5-6-7								1
6								1-2-4-5-6-7								1
7								1-3-4-5-6-7								1
8								2-3-4-5-6-7								2
test

Cell Formulas
Range	Formula
H2:H8	H2	=LET(d,TOROW(A1:G1,1,1),N,6,c,COUNT(d),x,BYROW(IF(MOD(INT(SEQUENCE(2^c,,0)/2^SEQUENCE(,c,0)),2),d,""),LAMBDA(r,IF(COUNT(r)=N,TEXTJOIN("-",,r),""))),FILTER(x,x<>""))
I2:N2	I2	=TEXTSPLIT(H2,"-",,,,)
P2:P8	P2	=TEXTSPLIT(H2#,"-",,,,)
Dynamic array formulas.

 

[Fluff]

How about

=DROP(REDUCE("",H2#,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)

 

[excelNewbie22]

it's perfect, but when i'm trying it on a large amount of rows, like 80000 rows or so, it get stucked

 

[Fluff]

That's the problem with some of the lambda functions like that on large amounts of data. You've just got to be patient, or use a drag down formula.

 

[excelNewbie22]

ok, thank you fluff, again

 

[Dave Patton]

Hello Fluff

help on the syntax

I looked at the formula and I tried it.
H2# did not work for me but this did =DROP(REDUCE("",H2:H10,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)

Please explain the syntax.
I am not sure what is defined with letters a and b.
I have used Lambda's with a few challenges but some of the syntax is very new to me.

Thanks in advance.

Drop.xlsm
	H	I	J	K	L	M	N	O
1
2	1-2-3-4-5-6-7	1	2	3	4	5	6	7
3	1-2-3-4-5-6-7	1	2	3	4	5	6	7
4	1-2-3-4-5-6-7	1	2	3	4	5	6	7
5	1-2-3-4-5-6-7	1	2	3	4	5	6	7
6	1-2-3-4-5-6-7	1	2	3	4	5	6	7
7	1-2-3-4-5-6-7	1	2	3	4	5	6	7
8	1-2-3-4-5-6-7	1	2	3	4	5	6	7
9	1-2-3-4-5-6-7	1	2	3	4	5	6	7
10	1-2-3-4-5-6-7	1	2	3	4	5	6	7
11
1a

Cell Formulas
Range	Formula
I2:O10	I2	=DROP(REDUCE("",H2:H10,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)
Dynamic array formulas.

 

[Fluff]

The # on H2# signifies that it is the 1st cell in a spill range, as H2 is not a spill range on your mini-sheet you need to use H2:H10 as you have done.

 

[excelNewbie22]

ok, think i found something ligther/faster, but still not perfect,

=HSTACK(LEFT(H2#, SEARCH("-",H2#,1)-1),MID(H2#, SEARCH("-",H2#) + 1, SEARCH("-",H2#,SEARCH("-",H2#)+1) - SEARCH("-",H2#) - 1),MID(H2#, SEARCH("-",H2#) + 3, SEARCH("-",H2#,SEARCH("-",H2#)+2) - SEARCH("-",H2#) - 1),MID(H2#, SEARCH("-",H2#) + 5, SEARCH("-",H2#,SEARCH("-",H2#)+2) - SEARCH("-",H2#) - 1),MID(H2#, SEARCH("-",H2#) + 7, SEARCH("-",H2#,SEARCH("-",H2#)+2) - SEARCH("-",H2#) - 1),MID(H2#, SEARCH("-",H2#) + 9, SEARCH("-",H2#,SEARCH("-",H2#)+2) - SEARCH("-",H2#) - 1))

the above formula good only while each number is single, but when one of them is two digits, it return -,
can anyone help me out change the formula to be adjusted to any length number that may be? always seperated by -

 

[excelNewbie22]

bumping...
and temporary remove the solution mark for others can see my ask, up