Excel spreadsheet data manipulation for zip code clusters

Question

Thank you in advance.  Is there a function that can be done that would take my data in the example below and create a cell that would list each of the 3 digit zips in the range,  separated by a comma?

Using the second example below, I would want it to create a cell (or change the cell) that looked like this: 618,619,620,622,623,624,625,626,627,628,629

I don't know anything about this type of thing.  I would consider myself just above beginner.

Thanks again

600-617
618-620, 622-629

Answer 1

Copy the following code into a module in the Visual Basic Editor:

Function ListZips(s As String) As String

    Const strSeparator = ","

    Dim arrParts() As String

    Dim arrLimits() As String

    Dim i As Long

    Dim j As Long

    Dim strReturn As String

    arrParts = Split(s, ",")

    For i = 0 To UBound(arrParts)

        arrLimits = Split(Trim(arrParts(i)), "-")

        Select Case UBound(arrLimits)

            Case 0

                ' Only one number

                strReturn = strReturn & strSeparator & arrLimits(0)

            Case 1

                ' Two numbers

                For j = Val(Trim(arrLimits(0))) To Val(Trim(arrLimits(1)))

                    strReturn = strReturn & strSeparator & j

                Next j

            Case Else

                ' This should not occur

        End Select

    Next i

    If strReturn <> "" Then

        strReturn = Mid(strReturn, Len(strSeparator) + 1)

    End If

    ListZips = strReturn

End Function

Let's say you have a string such as 600-617 in cell A2.

In B2, enter the formula   =ListZips(A2)

This formula can be filled down.