BYROW #CALC error

[lrobbo314]

I am trying to cut up a string into chunks of 3 character long strings to see how many times there are 3 sequential letters in the string.

When I reference a range, e.g. D62#, the BYROW formula works as expected. But, when I substitute D62# for the formula that is in it, I get #CALC errors, empty array.

I don't understand why this is happening or how to fix it.

Book3
	B	C	D	E	F
60	abc123def
61
62		1	abc	1	#CALC!
63		2	bc1	0	#CALC!
64		3	c12	0	#CALC!
65		4	123	0	#CALC!
66		5	23d	0	#CALC!
67		6	3de	0	#CALC!
68		7	def	1	#CALC!
Sheet1

Cell Formulas
Range	Formula
C62:C68	C62	=SEQUENCE(LEN(B60)-2)
D62:D68	D62	=MID(B60,C62#,3)
E62:E68	E62	=BYROW(D62#,LAMBDA(br,LET(c,CODE(TXT2ARR(UPPER(br))),a,DROP(c,1),b,DROP(c,-1),x,(a>64)*(a<91)*a,y,(b>64)*(b<91)*b,--(SUM(--(x-y=1))=2))))
F62:F68	F62	=BYROW(MID(B60,C62#,3),LAMBDA(br,LET(c,CODE(TXT2ARR(UPPER(br))),a,DROP(c,1),b,DROP(c,-1),x,(a>64)*(a<91)*a,y,(b>64)*(b<91)*b,--(SUM(--(x-y=1))=2))))
Dynamic array formulas.

The code for the TXT2ARR function is

=LAMBDA(text,MID(text,SEQUENCE(LEN(text)),1))

 

[JGordon11]

I agree it seems like it should work. Evaluating the F column formula it seems to be failing at the TXT2ARR function.

Here's an approach in column G that doesn't rely on TXT2ARR

Book1
	A	B	C	D	E	F	G	H
60		abc123def
61
62			1	abc	1	#CALC!	1
63			2	bc1	0	#CALC!	0
64			3	c12	0	#CALC!	0
65			4	123	0	#CALC!	0
66			5	23d	0	#CALC!	0
67			6	3de	0	#CALC!	0
68			7	def	1	#CALC!	1
69
Sheet3

Cell Formulas
Range	Formula
C62:C68	C62	=SEQUENCE(LEN(B60)-2)
D62:D68	D62	=MID(B60,C62#,3)
E62:E68	E62	=BYROW(D62#,LAMBDA(br,LET(c,CODE(TXT2ARR(UPPER(br))),a,DROP(c,1),b,DROP(c,-1),x,(a>64)*(a<91)*a,y,(b>64)*(b<91)*b,--(SUM(--(x-y=1))=2))))
F62:F68	F62	=BYROW(MID(B60,C62#,3),LAMBDA(br,LET(c,CODE(TXT2ARR(UPPER(br))),a,DROP(c,1),b,DROP(c,-1),x,(a>64)*(a<91)*a,y,(b>64)*(b<91)*b,--(SUM(--(x-y=1))=2))))
G62:G68	G62	=BYROW(UPPER(MID(B60,C62#,3)),LAMBDA(t,LET(a,CODE(MID(t,1,1)),b,CODE(MID(t,2,1)),c,CODE(MID(t,3,1)),(c-b=1)*(b-a=1)*(a>=CODE("A"))*(c<=CODE("Z")))))
Dynamic array formulas.

 

[lrobbo314]

Might be a minute before I get a chance to try it out. But thank you for the response. Looks great.