Array to return Multiple Results (maybe byrow function)

[pto160]

I am trying to use Textsplit with byrow to return multiple results, but I am getting a CALC error. Ignore the name error. This is my personal computer. The ":" is the delimiter.

Book1
	A	B
1	Product
2	1800:9400
3	2400:2500
4
5		#NAME?
6
7	Result I would like
8	1800
9	9400
10	2400
11	2500
Sheet1

Cell Formulas
Range	Formula
B5	B5	=BYROW(A2:A3,LAMBDA(x,TEXTSPLIT(x,,":")))

 

[Scott Huish]

Perhaps:
=TEXTSPLIT(TEXTJOIN(":",TRUE,A2:A3),,":")

Book1
	A
1	Product
2	1800:9400
3	2400:2500
4
5
6
7	Result I would like
8	1800
9	9400
10	2400
11	2500
Sheet1

Cell Formulas
Range	Formula
A8:A11	A8	=TEXTSPLIT(TEXTJOIN(":",TRUE,A2:A3),,":")
Dynamic array formulas.

 

[Cubist]

Try:

Book1
	A	B
1	Product
2	1800:9400
3	2400:2500
4
5		1800
6		9400
7	Result I would like	2400
8	1800	2500
9	9400
10	2400
11	2500
Sheet1

Cell Formulas
Range	Formula
B5:B8	B5	=TOCOL(TEXTSPLIT(TEXTJOIN(":",TRUE,A2:A3),":"))
Dynamic array formulas.

 

[Scott Huish]

You shouldn't need the TOCOL if you put the : in the row delimiter instead of the column delimiter in TEXTSPLIT.

 

[Cubist]

I didn't know that. The col_delimiter do not have the [] brackets so appear to be a required parameter while the row is optional.

 

[pto160]

Absolutely fantastic. Thank you so much to everyone. I am using this with check numbers to import into my accounting system. Is it possible to do the following below?

Book1
	A	B
1	Product	Check
2	1800:9400	1445
3	2400:2500	1446
4
5		#NAME?
6
7	Result I would like
8	Product	Check
9	1800	1445
10	9400	1445
11	2400	1446
12	2500	1446
Sheet1

Cell Formulas
Range	Formula
B5	B5	=BYROW(A2:A3,LAMBDA(x,TEXTSPLIT(x,,":")))

 

[Cubist]

Will column A ever have more than 2 values? e.g. 1800:9400:1600

 

[pto160]

Sometimes, yes, unfortunately there will be more than 2 values.

 

[Cubist]

I don't know if there's a simpler way but try this. Note that column B requires an additional blank cell.

Book3 (version 1).xlsb
	A	B
1	Product	Check
2	1800:9400:1600:800	1445
3	1800:2400	1446
4
5
6
7	Result I would like
8	Product	Check
9	1800	1445
10	9400	1445
11	1600	1445
12	800	1445
13	1800	1446
14	2400	1446
Sheet8

Cell Formulas
Range	Formula
A9:B14	A9	=LET( ct,VSTACK(0,BYROW(A2:A3,LAMBDA(br,COLUMNS(TEXTSPLIT(br,":"))))), t,TEXTSPLIT(TEXTJOIN(":",TRUE,A2:A3),,":"), l,XLOOKUP(SEQUENCE(SUM(ct)),SCAN(1,ct,LAMBDA(a,b,a+b)),B2:B4,,-1), HSTACK(t,l))
Dynamic array formulas.

 

[pto160]

This works absolutely great. This will save me so much time at work. Thank you.