I’m using Excel 365 and would like to stipulate whether it is true or not that the text cell I’m looking at has x amount of consecutive numbers.
For this example, I would like to know when there is 5 consecutive numbers in the cell:
| Text cell | True? |
|---|---|
| AB 131 | FALSE |
| 50353 GO | TRUE |
| A A 6 | FALSE |
| CV90356 | TRUE |
| TT 2041 | FALSE |
| 13190 13224 | TRUE |
As you can see it will return TRUE if five consective numbers appear in the cell, even if there are two lots of this sequence in the cell. What formula could I use to achieve this? Thanks in advance.
>Solution :
You could try using the following formula:
=MAP(A2:A7,LAMBDA(x,OR(LEN(TEXTSPLIT(x,TEXTSPLIT(x,SEQUENCE(10)-1,,1),,1))=5)))
Or, as commented above:
=OR(LEN(TEXTSPLIT(A2,TEXTSPLIT(A2,SEQUENCE(10)-1,,1),,1))=5)
Or,
=OR(LEN(IFERROR(--MID(A2,SEQUENCE(LEN(A2)),5),0))=5)
