ExcelBanter - View Single Post

Ron Rosenfeld

On 21 May 2007 03:15:03 -0700, wrote:

Hi...I am wanting to sort a series of numbers contained in a single
cell, with each number separated by a comma. I found the code below
in a search, but it doesn't seem to recognise the DELIM part. I am
using Excel 2003 and would appreciate any other solutions. First
prize would be a sort that doesn't put 125 before 2 for example, but
if need be, I can enter the numbers as 002,025,125 etc to negate this
problem.

Many thanks.

Rob

Public Sub SortNumbersWithinCells()
Const DELIM As String = ","
Dim sIn As String
Dim sArr As Variant
Dim sOut As String
Dim temp As Double
Dim change As Boolean
Dim i As Integer
Dim cell As Range

For Each cell In Selection
sIn = cell.Text
sArr = Split(sIn, DELIM)
Do
change = False
For i = 0 To UBound(sArr) - 1
If sArr(i) sArr(i + 1) Then
temp = sArr(i)
sArr(i) = sArr(i + 1)
sArr(i + 1) = temp
change = True
End If
Next i
Loop Until change = False
For i = 0 To UBound(sArr)
sOut = sOut & DELIM & sArr(i)
Next i
cell.Value = Mid(sOut, 2)
sOut = Empty
Next cell
End Sub

Here's one solution for doing a numeric sort on a string of comma separated
numbers within a cell.

There's no error checking, and the delimiter is a ","

==================================
Option Explicit

Sub SortCell()
Dim c As Range
Dim temp As Variant

For Each c In Selection
temp = Split(c.Text, ",")
BblSrt temp
c.Value = Join(temp, ",")
Next c

End Sub
Sub BblSrt(TempArray As Variant)
Dim temp As Variant
Dim i As Integer
Dim NoExchanges As Integer

' Loop until no more "exchanges" are made.
Do
NoExchanges = True

' Loop through each element in the array.
For i = 0 To UBound(TempArray) - 1

' If the element is greater than the element
' following it, exchange the two elements.
If Val(TempArray(i)) Val(TempArray(i + 1)) Then
NoExchanges = False
temp = TempArray(i)
TempArray(i) = TempArray(i + 1)
TempArray(i + 1) = temp
End If
Next i
Loop While Not (NoExchanges)
End Sub
==========================================
--ron