>
> This has worked fabulously so far -except I've hit one snag with it.... and
> it might be a limitation of excel... which I can probably work around...
>
> However, the problem that I'm seeing now...
>
> is when I have a cell that contains more than 33 numbers (each seperated by
> a comma, then when command runs, I get #value as the result... if I hack off
> some of the numbers in the column it works great...
>
> Is there a limit to the number of commas I can have in a cell?
>
> thanks much
>
> "Dave Peterson" wrote:
>
> > Split and Replace were added to VBA in xl2k. I think that's one of the
> > differences with Mac's. They don't have these two functions (as well as other
> > stuff).
> >
> > But you can use application.substitute() instead of Replace and Tom Ogilvy
> > created a function that does a split in xl97 (and Macs??):
> >
> > Option Explicit
> > Function GroupTheNumbers(myInStr As String) As String
> >
> > Dim myOutStr As String
> > Dim mySplit As Variant
> > Dim iCtr As Long
> >
> > myInStr = Application.Substitute(myInStr, " ", "")
> >
> > If Len(myInStr) = 0 Then
> > myOutStr = ""
> > Else
> > mySplit = Split97(myInStr, ",")
> > myOutStr = mySplit(LBound(mySplit))
> > If UBound(mySplit) = LBound(mySplit) Then
> > 'single number, don't do anything else
> > 'myOutStr is alread the only number
> > Else
> > For iCtr = LBound(mySplit) + 1 To UBound(mySplit) - 1
> > If Val(mySplit(iCtr)) = mySplit(iCtr - 1) + 1 Then
> > If Val(mySplit(iCtr)) = mySplit(iCtr + 1) - 1 Then
> > 'in a series like 4,5,6
> > Else
> > 'in a series like 4,5,8
> > myOutStr = myOutStr & "-" & mySplit(iCtr)
> > End If
> > Else
> > If Val(mySplit(iCtr)) = mySplit(iCtr + 1) - 1 Then
> > 'in a series like 3,5,6
> > myOutStr = myOutStr & "," & mySplit(iCtr)
> > Else
> > 'in a series like 3,5,7
> > myOutStr = myOutStr & "," & mySplit(iCtr)
> > End If
> > End If
> > Next iCtr
> > 'do the last number
> > If Val(mySplit(UBound(mySplit))) _
> > = mySplit(UBound(mySplit) - 1) + 1 Then
> > 'like 7,8
> > myOutStr = myOutStr & "-" & mySplit(UBound(mySplit))
> > Else
> > 'like 7,12
> > myOutStr = myOutStr & "," & mySplit(UBound(mySplit))
> > End If
> > End If
> > End If
> >
> > GroupTheNumbers = myOutStr
> >
> > End Function
> > Function Split97(sStr As String, sdelim As String) As Variant
> > 'from Tom Ogilvy
> > Split97 = Evaluate("{""" & _
> > Application.Substitute(sStr, sdelim, """,""") & """}")
> > End Function
> >
> >
> > It worked fine in xl2003. But that's not much of a test for Macs <vbg>!
> > Maryellen wrote:
> > >
> > > this worked perfectly on a PC -any way to turn it into a Mac 2004 friendly
> > > version of it?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > This UDF seemed to work ok for me:
> > > >
> > > > Option Explicit
> > > > Function GroupTheNumbers(myInStr As String) As String
> > > >
> > > > Dim myOutStr As String
> > > > Dim mySplit As Variant
> > > > Dim iCtr As Long
> > > >
> > > > myInStr = Replace(myInStr, " ", "")
> > > >
> > > > If Len(myInStr) = 0 Then
> > > > myOutStr = ""
> > > > Else
> > > > mySplit = Split(myInStr, ",")
> > > > myOutStr = mySplit(LBound(mySplit))
> > > > If UBound(mySplit) = LBound(mySplit) Then
> > > > 'single number, don't do anything else
> > > > 'myOutStr is alread the only number
> > > > Else
> > > > For iCtr = LBound(mySplit) + 1 To UBound(mySplit) - 1
> > > > If Val(mySplit(iCtr)) = mySplit(iCtr - 1) + 1 Then
> > > > If Val(mySplit(iCtr)) = mySplit(iCtr + 1) - 1 Then
> > > > 'in a series like 4,5,6
> > > > Else
> > > > 'in a series like 4,5,8
> > > > myOutStr = myOutStr & "-" & mySplit(iCtr)
> > > > End If
> > > > Else
> > > > If Val(mySplit(iCtr)) = mySplit(iCtr + 1) - 1 Then
> > > > 'in a series like 3,5,6
> > > > myOutStr = myOutStr & "," & mySplit(iCtr)
> > > > Else
> > > > 'in a series like 3,5,7
> > > > myOutStr = myOutStr & "," & mySplit(iCtr)
> > > > End If
> > > > End If
> > > > Next iCtr
> > > > 'do the last number
> > > > If Val(mySplit(UBound(mySplit))) = mySplit(UBound(mySplit) - 1) + 1
> > > > Then
> > > > 'like 7,8
> > > > myOutStr = myOutStr & "-" & mySplit(UBound(mySplit))
> > > > Else
> > > > 'like 7,12
> > > > myOutStr = myOutStr & "," & mySplit(UBound(mySplit))
> > > > End If
> > > > End If
> > > > End If
> > > >
> > > > GroupTheNumbers = myOutStr
> > > >
> > > > End Function
> > > >
> > > > If you're new to macros:
> > > >
> > > > Debra Dalgleish has some notes how to implement macros here:
> > > >
http://www.contextures.com/xlvba01.html> > > >
> > > > David McRitchie has an intro to macros:
> > > >
http://www.mvps.org/dmcritchie/excel/getstarted.htm> > > >
> > > > Ron de Bruin's intro to macros:
> > > >
http://www.rondebruin.nl/code.htm> > > >
> > > > (General, Regular and Standard modules all describe the same thing.)
> > > >
> > > > ========
> > > > Short course:
> > > >
> > > > Open your workbook.
> > > > Hit alt-f11 to get to the VBE (where macros/UDF's live)
> > > > hit ctrl-R to view the project explorer
> > > > Find your workbook.
> > > > should look like: VBAProject (yourfilename.xls)
> > > >
> > > > right click on the project name
> > > > Insert, then Module
> > > > You should see the code window pop up on the right hand side
> > > >
> > > > Paste the code in there.
> > > >
> > > > Now go back to excel.
> > > > Into a test cell and type:
> > > > =GroupTheNumbers(a1)
> > > >
> > > >
> > > > Maryellen wrote:
> > > > >
> > > > > I will have on going spreadsheets that will have one column of comma
> > > > > delimited numbers.- the cell could contain anywhere from 15 - 150 numbers.
> > > > >
> > > > > What the desired result would be - in a new column is to turn that series of
> > > > > numbers into ranges or groupings
> > > > >
> > > > > for instance in my cell if I had
> > > > > 1,2,3,4,5,6,7,8,12,13,14,15,17,18,19,20,21
> > > > > my new column would contain
> > > > > 1-8,12-15,17-21
> > > > >
> > > > > Each spreadsheet might have 15-20 rows - possibly more so a macro or formula
> > > > > that would do this would be ideal -
> > > > >
> > > > > I'm honestly jsut stuck on what to search on to find my answer...
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >