Sorting numbers in a single cell

[Cronje]

Is there a way to sort a list of numbers in a single cell, from least to greatest?

[Ken Puls]

Hi Cronje, and welcome to the board!

Are you numbers truly in a single cell, or in multiple cells but in a single column or row?

If they are in a single cell, how are they separated? By commas, hard returns (Alt Enter), or something else?

For the latter, it's fairly easy, but if in the former, it would be a bit more complicated.

[Zack Barresse]

Hi Cronje, welcome to VBAX!


If you are talking about having some numbers in a cell, and they are seperated by spaces, then you can use something like this, it goes into a standard module ...


[vba]Option Explicit

Sub SortMyCellPlease()
Dim myCel As Range, tmpWs As Worksheet, tmpCel As Range
Dim strTmpVal As String, i As Long
Set myCel = Selection
If myCel.Count > 1 Then
MsgBox "Please, only one cell at a time!", vbInformation, "ERROR"
Exit Sub
End If
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set tmpWs = Worksheets.Add
Set tmpCel = tmpWs.Range("A1")
tmpCel.Value = myCel.Value
tmpCel.TextToColumns Destination:=tmpCel, DataType:=xlDelimited, Space:=True
tmpCel.EntireRow.Sort Key1:=tmpCel, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
For i = 1 To tmpWs.Range("IV1").End(xlToLeft).Column Step 1
strTmpVal = strTmpVal & tmpWs.Cells(1, i).Value & " "
Next i
myCel.Value = Trim(strTmpVal)
tmpWs.Delete
Application.ScreenUpdating = True
Application.DisplayAlerts = False
End Sub[/vba]

HTH

[gsouza]

It does not work for me. I get an error here: Dataoption1:=xlSortNormal

[Zack Barresse]

What version of Excel are you running?

Anyway, you never did answer Ken's question. First things first, can you post an example spreadsheet (zipped)? This will help greatly as so we can 'see' what you are looking at.

[gsouza]

It was not my post I just tryed because I was courious.

[Zack Barresse]

Doh! Oops, sorry.

But if you post an example sheet, we couuld see what you mean.

[HalfAce]

Man, Zack. You're too much. (SortMyCellPlease)? :rofl

I've been know to name mine things like DoIt

Hope you & your's have an excellent holiday season.
Dan

[mark007]

Dataoption1:=xlSortNormal is only available from xl2002 (10) onwards, I assume you ran it in 2000 gsouza. Just remove that bit and it will run fine.

[gsouza]

Okay I will try it, thank you, yes I am running 2000

[gsouza]

Cool it worked perfectly. Thanks again

[Zack Barresse]

Dataoption1:=xlSortNormal is only available from xl2002 (10) onwards, I assume you ran it in 2000 gsouza. Just remove that bit and it will run fine.

Exactly why I asked the version. Compiled on 2002.

Dan: I aim to please!