lrobbo314
Well-known Member
- Joined
- Jul 14, 2008
- Messages
- 3,927
- Office Version
- 365
- Platform
- Windows
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.
The code for the TXT2ARR function is
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
Excel Formula:
=LAMBDA(text,MID(text,SEQUENCE(LEN(text)),1))