How to count consecutive specific characters in a range excel

[Mavri]

Hello,everyone.
I need to count consecutive specific characters in an excel range.For example:

+

N

+

+

N

N

DO

N

N

DO

DO

How do I count how many times "N" is appearing more than once consecutively?And what is number of maximum consecutive appearing of an "N"?
I need two different formulas.

Thanks in advance!

 

[James006]

Hi,

With your data in range B2 to L2, you can test following two Array Formulas

=SUM(IF(FREQUENCY(IF(B2:L2="N",COLUMN(B2:L2)),IF(B2:L2<>"N",COLUMN(B2:L2)))>=2,1,0))
=MAX(FREQUENCY(IF(B2:L2="N",COLUMN(B2:L2)),IF(B2:L2<>"N",COLUMN(B2:L2))))

 

[Mavri]

It doesnt work

 

[James006]

It doesn't work .... is not a very explicit answer ...

What is the result you are getting ???

 

[JEC]

VBA solution. Assumed your range is B2:L2

Output in P1:P2

Sub jec()
 Dim r, x As Long, y As Long, s As Long
 For Each r In Range("B2:L2")
    If r.Offset(, -1) = "N" And r = "N" And r.Offset(, 1) <> "N" Then x = x + 1
    If r = "N" Then
       y = y + 1
    Else
      s = IIf(y > s, y, s): y = 0
    End If
 Next
 Range("P1:P2") = Application.Transpose(Array(x, s))
End Sub

 

[James006]

Forgot to add the obvious reminder about Array Formulas:

Instead of the standard Enter key .... you do need to use simultaneously the three keys : Control + Shift + Enter

Most probably : "It does not work" will happily turn into : "It does work"

 

[Mavri]

It does work Thanks a lot!

 

[James006]

You are welcome