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.

Forum Discussion

Needs a formula to return true if a cell contains three or more consecutive letters

Resources