Apr 27 2022 04:26 AM
98ABCD8 returns true
98XYZ99 returns true
88DG987 returns false
Help me here, please. Needs a formula rather than a VBA code. Thank you
</p>
Apr 27 2022 04:37 AM
Apr 27 2022 04:49 AM
Apr 27 2022 04:50 AM
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)))
Apr 27 2022 05:05 AM
Apr 27 2022 05:15 AM
SolutionThat 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.
Apr 27 2022 05:37 AM
Apr 27 2022 05:40 AM
It's based on How to count max number of consecutive positive / negative numbers in Excel?
But with a different condition.
Apr 27 2022 05:55 AM
Apr 27 2022 05:15 AM
SolutionThat 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.