SOLVED

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

New Contributor
<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>

8 Replies

@jpsaini91 

A simple solution could be:

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

 

@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)))

I am using office 2013. And this formula returned pop-up error.
best response confirmed by jpsaini91 (New Contributor)
Solution

@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.

First, thanks a ton, that formula works, exactly what I needed.
Second, I am just a layman, knows basic and doesn't even begin to understand how that formula of yours work.
Thanks again.
a loooong formula but perhaps easier to understand?
=ISNUMBER(SEARCH("xxx",(IFERROR(MID(A2,1,1)*1,"x")&IFERROR(MID(A2,2,1)*1,"x")&IFERROR(MID(A2,3,1)*1,"x")&IFERROR(MID(A2,4,1)*1,"x")&IFERROR(MID(A2,5,1)*1,"x")&IFERROR(MID(A2,6,1)*1,"x")&IFERROR(MID(A2,7,1)*1,"x"))))

Replace everything that's not a number with the letter x,. Example first char :
IFERROR(MID(A2,1,1)*1,"x") (take char 1 and multiplicate with 1. If its a letter you get an error and put leter X)
etc, for 7 positions, join with "&" and then search for xxx. If xxx exist you get a number an ISNUMBER gets true
ISNUMBER(SEARCH("xxx",....