Group Consequtive Numbers from a Text String

[DPhilip]

Hi Everyone,

I am in need of a formula or VBA code to achieve the following:

CELL A1: 100,101,102,106,110,111,115,116,117,118,119,120 ---> Output in Cell A2 = 100-102,106,110-111,115-120

Thanks in advance for your help

-Dennis

 

[Marcelo Branco]

Two things...

1) Using the function name Group brings up a "That function is not valid" error message when I put your function into a formula on the worksheet (using XL2010).

2) If I change your function name (I used GroupEm), then your function returns an incorrect answer. For example, I put this in cell A1...

100,101,102,103,104,105,106,107,108,119,121,123,125,126,128,129

and your function returned this to me...

100-100, 119-119, 121-121, 123-123, 125-100, 128-100

Note the multi-cell ranges have the same ending number as starting number and the first cell value is also placed as the last cell value.

Rick

The function Group (post above) in Excel 2010 worked perfectly for me.


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

100,101,102,103,104,105,106,107,108,119,121,123,125,126,128,129​

[/td][td]

100-108, 119-119, 121-121, 123-123, 125-126, 128-129​

[/td][/tr]
[/table]

Repeating

Function Group(s As String)
    Dim spl As Variant, currMin As String, currMax As String
    Dim i As Long, strAux As String
    
    spl = Split(s, ",")
    currMin = spl(0)
    currMax = currMin
        
    For i = 1 To UBound(spl)
        If CLng(spl(i)) = CLng(spl(i - 1)) + 1 Then
            currMax = spl(i)
        Else
            strAux = strAux & ", " & currMin & "-" & currMax
            currMin = spl(i)
            currMax = currMin
        End If
    Next i
    Group = Mid(strAux & ", " & currMin & "-" & currMax, 3)
End Function

Don't understand why didn't work for you

M.

 

[Tetra201]

Here is a shorter one to try:

Function NGroup(s As String) As String
    Dim Nums() As String, i As Long
    Nums = Split(s, ",")
    If Nums(0) = Nums(1) - 1 Then NGroup = Nums(0) Else NGroup = Nums(0) & "-" & Nums(0)
    For i = 1 To UBound(Nums) - 1
        If CLng(Nums(i)) <> CLng(Nums(i - 1)) + 1 Then NGroup = NGroup & "," & Nums(i)
        If CLng(Nums(i)) <> CLng(Nums(i + 1)) - 1 Then NGroup = NGroup & "-" & Nums(i)
    Next i
    If CLng(Nums(i)) <> CLng(Nums(i - 1)) + 1 Then NGroup = NGroup & "," & Nums(i)
    NGroup = NGroup & "-" & Nums(i)
End Function

 

[Tetra201]

@ Rick Rothstein:

Just to mention: your UDF seems to stumble on "99,101,102,106" (returns "99-102, 106-106").