Sorting numbers Assending or descending within a cell

[Kamran01]

Dear Experts,

I have a cell, in which a series of numbers available. I want to sort these numbers assending and descending orders.
Can anyone suggest a formula to do this.

Cell Values Assending / Desending
5573914 1345579 9755431
4513217 1123457 7543211
5373478 3345778 8775433
1563957 1355679 9765531

Thanks,
Kamran Noor

 

[BrianJN1]

How are those values recorded, in one column?
I assume that you aware of the Sort facility in the tool bar.
If you do not want to use that, are you therefore asking for a VBA/macro solution?

 

[BrianJN1]

Here are two macros which do what I have suggested.
I placed your values in Col E. I used the VBA Macro utility under the Developer tab.
Sub Go_Down()
'
' Go_Down Macro
'

'
Range("E1:E12").Select

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("E1:E12")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

______________________________________________________________

Sub Go_Up()
'
' Go_Up Macro
'

'
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("E1:E12")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Kamran01]

Dear Brian,

Simply these are numbers and i want to sort assending and descending by formula or vba code.

Thanks,

 

[Kamran01]

Dear Brian,

I tried but both are not working, i place my values in Column E and than added this query in vba editor and than run but still all the values are same as old.
Looking forward for your swift resposne,

Best regard,
Kamran Noor

 

[Rick Rothstein]

Here is a UDF (user defined function) that you can use. It takes one required argument (the number to be sorted by its digits) and one optional argument which controls whether the sort order is ascending or descending... FALSE or if omitted makes the sort ascending, TRUE makes the sort descending.

[table="width: 500"]
[tr]
	[td]Function SortDigits(Number As Variant, Optional ReverseIt As Boolean) As String
  Dim X As Long, Num() As String
  Num = Split(StrConv(Number, vbUnicode), Chr(0))
  With CreateObject("System.Collections.ArrayList")
    For X = 0 To UBound(Num)
      .Add Application.Trim(Num(X))
    Next
    .Sort
    If ReverseIt Then .Reverse
    SortDigits = Join(.ToArray, "")
  End With
End Function[/td]
[/tr]
[/table]

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example, ascending, use either...

=SortDigits(A1)

or...

=SortDigits(A1,FALSE)

and descending...

=SortDigits(A1,TRUE)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Kamran01]

It's working perfectly, Thanks Mr. Rick.
Do we have any formula source too for this function?

Please share if possible and thanks once again.

 

[Rick Rothstein]

It's working perfectly, Thanks Mr. Rick.
Do we have any formula source too for this function?

Please share if possible and thanks once again.

I cannot think of any way to do this with a regular formula (which is why I posted the UDF for you to use).

 

[Kamran01]

Thanks alot Mr. Rick.

 

[István Hirsch]

Try this:

Excel Workbook
	A	B	C
1	5570391	0135579	9755310
2	4513217	1123457	7543211
3	5373478	3345778	8775433
4	150630957	001355679	976553100
5	319910087	001137899	998731100
Sheet1