Forum Discussion

Copper Contributor

Apr 27, 2022

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>

excel

Formulas and Functions

HansVogelaar
Apr 27, 2022
jpsaini91

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.

Detlef_Lewin

Silver Contributor

Apr 27, 2022

jpsaini91

A simple solution could be:

=SUM(--ISERROR(-MID(A1,SEQUENCE(7),1)))>2

jpsaini91

Copper Contributor

Apr 27, 2022

that didn't worked...

HansVogelaar
MVP
Apr 27, 2022
jpsaini91

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)))
- jpsaini91
  Copper Contributor
  Apr 27, 2022
  I am using office 2013. And this formula returned pop-up error.
  - HansVogelaar
    MVP
    Apr 27, 2022
    jpsaini91
    
    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.