Forum Discussion
Solved
Needs a formula to return true if a cell contains three or more consecutive letters
<p>
For ex:
98ABCD8 returns true
98XYZ99 returns true
88DG987 returns false
Help me here, please. Needs a formula rather than a VBA code. Thank you
</p>
That formula won't work in Office 2013.
Try this. It is an array formula that has to be confirmed with Ctrl+Shift+Enter.
With a value such as 12ABS34 in A2:
=MAX(FREQUENCY(IF(ISERROR(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2)))),IF(ISNUMBER(-MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("1:"&LEN(A2))))))>=3
The formula can be filled down if required.
8 Replies
- that didn't worked...
If you have Microsoft 365 or Office 2021:
=LET(n,LEN(A2),s,SEQUENCE(n),a,MID(A2,s,1),b,ISERROR(-a),c,","&TEXTJOIN(",",TRUE,b)&",",ISNUMBER(FIND(",TRUE,TRUE,TRUE,",c)))
