Find all consecutive numbers in range

**sans** · 10-09-2012, 06:05 PM

Hi guys,

I am trying to find the consecutive numbers in a range but I only manage to get the result for one consecutive number only.

For example,

with range 1,2,3,5,6,9,18,19 the result should be 3-2-2 because there are 3 consecutive numbers (1,2,3), then 2 consecutive numbers (5,6), then another 2 consecutive numbers (18,19)

A few more examples,

with range 1,2,5,6,8,9,11,12 the result would be 2-2-2-2
with range 1,2,3,4,7,8,10,13 the result would be 4-2
with range 1,2,3,5,6,9,10,12 the result would be 3-2-2

If this can be done with either a macro or a formula it would be great. Thank you for looking!

**rylo** · 10-09-2012, 07:27 PM

Hi

Are the values in different cells (say A1:H1) or are they all in a single cell, with a comma separator?

If they are in the range A1:H1, then put the following in a general module

Function myfunc(rng As Range)
  cntr = 1
  For Each ce In rng
    If ce.Offset(0, 1) = ce + 1 Then
      cntr = cntr + 1
    Else
      If cntr > 1 Then
        holder = holder & cntr & "-"
      End If
      cntr = 1
    End If
  
  Next ce
  myfunc = Left(holder, Len(holder) - 1)
End Function

In J1 enter the formula