Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Sorting numbers within a cell
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5
Default Sorting numbers within a cell
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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default Sorting numbers within a cell
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

" 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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Sorting numbers within a cell
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

" 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default Sorting numbers within a cell
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



" 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5
Default Sorting numbers within a cell
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))


On May 21, 12:49 pm, Joel wrote:
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

" 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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default Sorting numbers within a cell
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

" wrote:

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))


On May 21, 12:49 pm, Joel wrote:
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

" 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



Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5
Default Sorting numbers within a cell
Thanks Mike - that works perfectly! Much appreciated.

On May 21, 12:52 pm, Mike H wrote:
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

" 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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,501
Default Sorting numbers within a cell
Your welcome

" wrote:

Thanks Mike - that works perfectly! Much appreciated.

On May 21, 12:52 pm, Mike H wrote:
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

" 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



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 5,651
Default Sorting numbers within a cell
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
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 9,101
Default Sorting numbers within a cell
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.

" 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting high numbers from low numbers between two rows scotty New Users to Excel 7 February 12th 07 09:38 PM
Sorting - cells containing numbers, numbers and letters Gunny Excel Discussion (Misc queries) 5 July 16th 06 01:22 AM
Sorting numbers with differing numbers of digits Trudy Excel Discussion (Misc queries) 5 March 4th 06 12:31 PM
sorting numbers darkjedi Excel Discussion (Misc queries) 2 November 30th 05 09:01 PM
sorting the row by numbers Jen Excel Worksheet Functions 0 January 12th 05 06:55 AM


All times are GMT +1. The time now is 09:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement