Counting consecutive numbers in a comma-separated list

[mrc44]

I tried to find a formula or a macro for this online but I couldn't find anything.

I need a way to calculate the consecutive numbers in a comma separated list. Thanks in advance.

Here is a sample data: ConsecutiveCount.xlsm

I'm using Excel 2016.

* In the sample file, I put the expected results in separate cells, but it can be in a single too.

 

[veyselemre]

Sub test()
    Dim al, lst, son&, i&, ii&, topla%, fark
    son = Cells(Rows.Count, 1).End(3).Row
    Range("B2:I" & Rows.Count).ClearContents
    ReDim lst(2 To son, 1 To 8)
    For i = 2 To son
        topla = 0
        al = Split(Cells(i, 1).Value & ",9999999", ",")
        For ii = 1 To UBound(al)
            fark = Val(al(ii)) - Val(al(ii - 1))
            If fark = 1 Then
                topla = topla + 1
            Else
                If topla > 0 And fark <> 1 Then
                    lst(i, topla) = lst(i, topla) + 1
                    lst(i, 8) = lst(i, 8) + 1
                    topla = 0
                End If
            End If
        Next ii
    Next i
    Range("B2").Resize(son - 1, 8).Value = lst
End Sub