WOW, you just "dissappeared into the cornfield" with that one........<g
I understood how to use the first one, but have no clue here........

Vaya con Dios,
Chuck, CABGx3





"JE McGimpsey" wrote:

This version, done as a function, is a bit more robust - it allows a
change in the range separator. For instance,

NumberRangeToList("1-5,8-12,21-19","-")

will return the same result as your example:

Public Function NumberRangeToList( _
ByVal sInput As String, _
Optional sRangeSeparator As String = "-", _
Optional sDelimiter As String = ",") As Variant
Const nMAXCHARS As Long = 32767
Dim nLeftStartChar As Long
Dim nRightEndChar As Long
Dim nLeftArg As Long
Dim nRightArg As Long
Dim nPos As Long
Dim nStep As Long
Dim nSepCharCount As Long
Dim i As Long
Dim sTemp As String
Dim sTemp2 As String
Dim bGoodString As Boolean

sTemp = sInput
bGoodString = True
nSepCharCount = Len(sRangeSeparator)
If Len(sInput) 0 And nSepCharCount 0 Then
If sTemp Like "*#" & sRangeSeparator & "#*" Then
nPos = InStr(2, sTemp, sRangeSeparator)
Do While nPos
nLeftStartChar = nPos - 1
nRightEndChar = nPos + nSepCharCount
If IsNumeric(Mid(sTemp, nLeftStartChar, 1)) And _
IsNumeric(Mid(sTemp, nRightEndChar, 1)) Then
Do While nLeftStartChar 1
If Not IsNumeric(Mid(sTemp, _
nLeftStartChar - 1, 1)) Then Exit Do
nLeftStartChar = nLeftStartChar - 1
Loop
nLeftArg = CLng(Mid(sTemp, nLeftStartChar, _
nPos - nLeftStartChar))
Do While nRightEndChar < Len(sTemp)
If Not IsNumeric(Mid(sTemp, _
nRightEndChar + 1, 1)) Then Exit Do
nRightEndChar = nRightEndChar + 1
Loop
nRightArg = CLng(Mid(sTemp, _
nPos + nSepCharCount, _
nRightEndChar - (nPos + nSepCharCount - 1)))
sTemp2 = sDelimiter & CStr(nLeftArg)
nStep = Sgn(nRightArg - nLeftArg)
If nStep Then
For i = nLeftArg + nStep To _
nRightArg Step nStep
sTemp2 = sTemp2 & sDelimiter & i
bGoodString = Len(sTemp2) <= nMAXCHARS
If Not bGoodString Then Exit Do
Next i
Else
nPos = nPos - nSepCharCount
End If
sTemp = Left(sTemp, nLeftStartChar - 1) & _
Mid(sTemp2, Len(sDelimiter) + 1) & _
Mid(sTemp, nRightEndChar + 1)
bGoodString = Len(sTemp) <= nMAXCHARS
If Not bGoodString Then Exit Do
End If
nPos = InStr(nPos + nSepCharCount, sTemp, _
sRangeSeparator)
Loop
End If
End If
If bGoodString Then
NumberRangeToList = sTemp
Else
NumberRangeToList = CVErr(xlErrValue)
End If
End Function




In article ,
CLR wrote:

It even works with 1-5, 8-12, 21-19 all in the same cell, returning
1,2,3,4,5,8,9,10,11,12,21,20,19

I don't know when I'll ever use it, but it's going directly into my
goodie-stash.