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,,":")))

 

[pto160]

I have been using this formula at work. Is it possible to add a third column for the check date?

Book1
	A	B	C
1	Product	Check	Check Date
2	1800:9400:1600:800	1445	8/8/2024
3	1800:2400	1446	10/10/2024
4
5
6
7	Result I would like		I would like to add this column
8	Product	Check	Check Date
9	1800	1445	8/8/2024
10	9400	1445	8/8/2024
11	1600	1445	8/8/2024
12	800	1445	8/8/2024
13	1800	1446	10/10/2024
14	2400	1446	10/10/2024
Sheet1

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.

 

[djclements]

Another option is to use a "text to columns / unpivot" approach:

=LET(
    a, A2:A3,
    b, B2:C3,
    t, TEXTBEFORE(TEXTAFTER(":"&a&":",":",SEQUENCE(,MAX(LEN(a)-LEN(SUBSTITUTE(a,":",)))+1)),":"),
    HSTACK(TOCOL(t,2),CHOOSEROWS(b,TOCOL(IFS(NOT(ISERROR(t)),SEQUENCE(ROWS(a))),2)))
)

TEXTBEFORE-TEXTAFTER (with multiple [instance_num]s) will split the delimited data into an array. Items with fewer delimiters than the item with the most delimiters will have their extra columns filled with errors. TOCOL then ignores these errors with the optional [ignore] argument set to 2, and CHOOSEROWS returns the applicable data by referencing the row number of the original table.

Adjust the a & b range references as needed.

 

[pto160]

A very elegant and dynamic formula. Absolutely fantastic. Thank you so much djclements. This will save me so much time at work.