Glad I could help. Looks like Peo's prediction of Excel choking on that size
of an array were spot-on.

Sometimes worksheet formulas are good, sometimes VBA - in this case VBA
turned out to be the Bigger (and better) Hammer. Not always the case.

"Peter Herman" wrote:

Hi J,
Thanks so much! you are a sweethart! your solution worked like a charm. If
you ever need any lighting for your home please do not hesitate to contact me
at
for an equally sweet arrangement.
Peo - by the way your solution worked well on a test run but I was having
trouble running it on 45,000 records as the spreadsheet kept freezing.
Thanks,
Peter Zev

"JLatham" wrote:

Some super-guru of worksheet formulas might come up with something, but I'm
not The Guy. You've got a couple of things to deal with he the entries in
each individual cell really aren't numbers, they are text representations of
numbers. In order to sort them properly they have to be broken out into
individual number groups and then sorted. If you leave them as text, then
they won't sort in the manner you expect. That's why I came up with a code
solution - and I think it's why Peo offered the code solution initially also.

Try his recommendation for trying to do the Text To Columns thing and see if
that doesn't help.

As a last resort? I've uploaded a workbook with the operational code that
you can simply copy your one worksheet into and run the code that way.
There's a single sheet in the workbook, it has a place to type in the column
letter where your data is and another cell to type in the number of the first
row on that sheet with data that needs sorting. Enter those values and click
the button and it will do its job and tell you when it's done.

Just click this link and choose to save it to your hard drive:
http://www.jlathamsite.com/uploads/for_PeterHerman.xls

Be sure you enable macros if asked to. If nothing happens when you click
the button, it probably means your Macro Security is set too high. To adjust
that,
choose Tools | Macro | Macro Security from the Excel menu bar. You may have
to pull down the list to see the Macro Security entry. Choose MEDIUM
setting. Close the window and close Excel. You have to close and reopen
Excel for the setting to become effective. Next time you open it, choose
[Enable] as the response to the question about allowing macros to run, and
then try again.

If you're using Excel 2007 - to get to the same place:
click the large Office button in the upper left of the Excel window.
click the [options] link at the lower right of the window that appears
click [Trust Center] in the left pane of the Excel Options window
then click the [Trust Center Settings] button
choose the 2nd option from the top: Disable all macros with notification
click [OK] and again close and reopen Excel
when the file opens, look near the top of the worksheet page and you should
see a notice that macros are disabled, but you'll have an option available to
enable them.




"Peter Herman" wrote:

sorry J,
this is kind of over my head. is there not a formula or function that would
do this?

"JLatham" wrote:

Put this code into your workbook, it should do the trick.
Since this is a 'destructive' operation (values in cells are overwritten) I
recommend that you create a copy of your workbook to use to verify that it
works properly. Actually, you can simply create a copy of the worksheet and
put it in the same workbook to test with.

To put the code into a workbook: open the workbook, then press [Alt]+[F11]
to open the Visual Basic Editor (VBE). In the VBE use its menu to Insert |
Module and then copy the code into that module. Make any changes to column
identifier or starting row number (row with your 1st set of numbers to sort),
then close the VBE.

Select the sheet with the data on it, then from the Excel menu toolbar choose:
Tools | Macro | Macros and click on SortInCells and click the [Run] button.

It took my slow machine 4 or 5 seconds to do 45,000 rows of your sample
data: AMD 3200+ 1GB RAM, WinXP Pro, Excel 2003. Time will depend on actual
number of entries and number of numbers in each entry, and physical setup of
the computer you run it on.

Sub SortInCells()
'must have sheet with data on it selected
'when you call this macro

Const dataColumn = "A" ' change?
Const firstRowUsed = 1 ' change?
Dim toSort() As Integer
Dim lastRow As Long
Dim rngToSort As Range
Dim anyCell As Object
Dim splitOut As Variant
Dim lCount As Long ' loop counter
Dim tmpValue As Integer
Dim swappedFlag As Boolean
Dim humptyDumpty As String

lastRow = Range(dataColumn & Rows.Count).End(xlUp).Row
Set rngToSort = Range(dataColumn & firstRowUsed & ":" & _
dataColumn & lastRow)
For Each anyCell In rngToSort
If Not IsEmpty(anyCell) And InStr(anyCell.Value, ",") 0 Then
splitOut = Split(anyCell.Value, ",")
ReDim toSort(LBound(splitOut) To UBound(splitOut))
'convert strings to numbers and put in array to sort
For lCount = LBound(splitOut) To UBound(splitOut)
toSort(lCount) = Val(splitOut(lCount))
Next
'Quicksort is a little slow for small groups of
'numbers, but if you've got longer lists, it'll
'be much faster than ones for short lists (like a bubble)
QuickSort toSort(), LBound(toSort), UBound(toSort)

'now we have to put the pieces back together as a string
humptyDumpty = "" ' clear any prior results
For lCount = LBound(toSort) To UBound(toSort)
humptyDumpty = humptyDumpty & Trim(Str(toSort(lCount))) & ","
Next
humptyDumpty = Left(humptyDumpty, Len(humptyDumpty) - 1)
anyCell.Value = humptyDumpty
End If
Next ' end of loop through rngToSort
End Sub

Private Sub QuickSort(list() As Integer, ByVal min As Long, ByVal max As Long)
'an implementation of a Quick Sort
'change the List() type to the type of data you will be sorting
'
Dim med_value As Long
Dim hi As Long
Dim lo As Long
Dim i As Long

' If min = max, the list contains 0 or 1 items so it
' is sorted.
If min = max Then
Exit Sub
End If

' Pick the dividing value.
i = Int((max - min + 1) * Rnd + min)
med_value = list(i)

' Swap it to the front.
list(i) = list(min)

lo = min
hi = max
Do
' Look down from hi for a value < med_value.
Do While list(hi) = med_value
hi = hi - 1
If hi <= lo Then Exit Do
Loop
If hi <= lo Then
list(lo) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(lo) = list(hi)

' Look up from lo for a value = med_value.
lo = lo + 1
Do While list(lo) < med_value
lo = lo + 1
If lo = hi Then Exit Do
Loop
If lo = hi Then
lo = hi
list(hi) = med_value
Exit Do
End If

' Swap the lo and hi values.
list(hi) = list(lo)
Loop

' Recursively sort the two sublists.
QuickSort list(), min, lo - 1
QuickSort list(), lo + 1, max
End Sub

"Peter Herman" wrote:

Hi there,
I have a column of numbers separated by commas that is about 45,000 rows
long. I would like to sort the numbers in each individual row from small to
large.
In other words my data looks like this:

column A
17,1,36,98,62
56,94,12,24
and so on...all the way down and I would like it to look like this:

Column A
1,17,36,62,98
12,24,56,94

Thanks to anyone who helps me.