# single formula to cover entire array column?



## excelNewbie22 (Tuesday at 12:13 PM)

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.xlsbABCDEFGHIJKLMNOPQRSTU1123456721-2-3-4-5-6123456131-2-3-4-5-7141-2-3-4-6-7151-2-3-5-6-7161-2-4-5-6-7171-3-4-5-6-7182-3-4-5-6-72testCell FormulasRangeFormulaH2:H8H2=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:N2I2=TEXTSPLIT(H2,"-",,,,)P2:P8P2=TEXTSPLIT(H2#,"-",,,,)Dynamic array formulas.


----------



## Fluff (Tuesday at 1:11 PM)

How about

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


----------



## excelNewbie22 (Tuesday at 3:19 PM)

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


----------



## Fluff (Tuesday at 3:33 PM)

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 (Tuesday at 4:12 PM)

ok, thank you fluff, again


----------



## Dave Patton (Tuesday at 5:03 PM)

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.xlsmHIJKLMNO121-2-3-4-5-6-7123456731-2-3-4-5-6-7123456741-2-3-4-5-6-7123456751-2-3-4-5-6-7123456761-2-3-4-5-6-7123456771-2-3-4-5-6-7123456781-2-3-4-5-6-7123456791-2-3-4-5-6-71234567101-2-3-4-5-6-71234567111aCell FormulasRangeFormulaI2:O10I2=DROP(REDUCE("",H2:H10,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b,"-")))),1)Dynamic array formulas.


----------



## Fluff (Wednesday at 6:13 AM)

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 (Wednesday at 9:28 AM)

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 (Wednesday at 2:41 PM)

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


----------

