Sorting numbers within a cell

[robingSA]

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

 

[Joel]

try this code. the sort alogorithm you are using didn't do a full osrt. I
changed the sort algorithm. I also convert the string to a number and then
back to a string to gett rid of the leading zeroes problem. If you need to
put zeroes in front of the numbers then changge as follows:

from
sOut = sOut & DELIM & CStr(iArr(i))
to
sOut = sOut & DELIM & Format(iArr(i), "000")



Public Sub SortNumbersWithinCells()
Const DELIM As String = ","
Dim sIn As String
Dim sArr As Variant
Dim iArr As Integer
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)
For i = 0 To UBound(sArr)
iArr(i) = Val(sArr(i))
Next i
For i = 0 To (UBound(sArr) - 1)
For j = 1 To UBound(sArr)

If iArr(i) > iArr(j) Then
temp = iArr(i)
iArr(j) = iArr(i)
iArr(i) = temp
End If
Next j
Next i
For i = 0 To UBound(iArr)
sOut = sOut & DELIM & CStr(iArr(i))
Next i
cell.Value = Mid(sOut, 2)
sOut = Empty
Next cell
End Sub

 

[Mike H]

Or sort by the value of the number instead of the string:-

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 Val(sArr(i)) > Val(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

Mike

 

[Joel]

Just noticed a small problem in sort

from
If iArr(i) > iArr(j) Then
temp = iArr(i)
iArr(j) = iArr(i)
iArr(i) = temp
End If

to

If iArr(i) > iArr(j) Then
temp = iArr(i)
iArr(i) = iArr(j)
iArr(j) = temp
End If

 

[robingSA]

Thanks Joel. It returned a Compile Error: Expected Array for:

iArr(i) = in this section:

For i = 0 To UBound(sArr)
iArr(i) = Val(sArr(i))

 

[Joel]

My compiler didn't give the error. It is because iArr is not declared as an
arrray.

try replacing

Dim iArr As Integer
Dim iArr As Variant

 

[robingSA]

Thanks Mike - that works perfectly! Much appreciated.

 

[Mike H]

Your welcome

Thanks Mike - that works perfectly! Much appreciated.

 

[Ron Rosenfeld]

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

 

[Joel]

Be careful of the sort algorithm. It is not a full sort.

If you start with
5,4,3,2,1

You will get the following steps
i = 0: 4,5,3,2,1
i = 1: 4,3,5,2,1
i = 2: 4,3,2,5,1
i = 3: 4,3,2,1,5

It only brought the 5 to the last position.