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

 

[bosco_yip]

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

Here is the regular formula solution

1] Ascending sort digits :
=TEXT(SUMPRODUCT(AGGREGATE(14,6,--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2))))*10^(ROW(INDIRECT("1:"&LEN(A2)))-1)),REPT("0",LEN(A2)))

2] Descending sort digits :
=TEXT(SUMPRODUCT(AGGREGATE(15,6,--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2))))*10^(ROW(INDIRECT("1:"&LEN(A2)))-1)),REPT("0",LEN(A2)))

Regards
Bosco

 

[Kamran01]

Try this:

Sheet1

	A	B	C

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:109px;"><col style="width:103px;"><col style="width:95px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]5570391[/TD]
[TD="align: right"]0135579[/TD]
[TD="align: right"]9755310[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]4513217[/TD]
[TD="align: right"]1123457[/TD]
[TD="align: right"]7543211[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]5373478[/TD]
[TD="align: right"]3345778[/TD]
[TD="align: right"]8775433[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]150630957[/TD]
[TD="align: right"]001355679[/TD]
[TD="align: right"]976553100[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]319910087[/TD]
[TD="align: right"]001137899[/TD]
[TD="align: right"]998731100[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B1 {=REPT("0",LEN(A1)-LEN(SUBSTITUTE(A1,"0","")))&SUM(LARGE(1*(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))*10^(ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))-1))} C1 {=""&SUM(SMALL(1*(MID(A1,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))),1)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1))))*10^(ROW(INDEX(A:A,1):INDEX(A:A,LEN(A1)))-1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hello Istavan,

Thank you for your coding, can you please describe the function you used seperately.
Looking forward for your prompt response,

Best Regard,
Kamran

 

[Kamran01]

Here is the regular formula solution

1] Ascending sort digits :
=TEXT(SUMPRODUCT(AGGREGATE(14,6,--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2))))*10^(ROW(INDIRECT("1:"&LEN(A2)))-1)),REPT("0",LEN(A2)))

2] Descending sort digits :
=TEXT(SUMPRODUCT(AGGREGATE(15,6,--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2))))*10^(ROW(INDIRECT("1:"&LEN(A2)))-1)),REPT("0",LEN(A2)))

Regards
Bosco

Thanks bosco,

Its working perfectly, can you please describe the function you used.

Best Regard,
Kamran

 

[Peter_SSs]

Some more formula options assuming you have the CONCAT() function (Office 365)

1. If all your numbers are 7 digits like your samples.

Excel Workbook
	A	B	C
1	Num	Asc	Desc
2	5573914	1345579	9755431
3	4513217	1123457	7543211
4	5373478	3345778	8775433
5	1563957	1355679	9765531
Sort 7 digits

2. If your numbers can vary in length.
These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formulas can then be copied down.

Excel Workbook
	A	B	C
1	Num	Asc	Desc
2	557391420	012345579	975543210
3	561	156	651
4	20356	02356	65320
5	1563957	1355679	9765531
Sort any length

 

[Peter_SSs]

.. and if you are interested, here is another UDF approach.

Function OrderDigits(s As String, Optional bDesc As Boolean) As String
  Dim a(0 To 9) As String, i As Long
  
  For i = 1 To Len(s)
    a(Mid(s, i, 1)) = a(Mid(s, i, 1)) & Mid(s, i, 1)
  Next i
  OrderDigits = Join(a, "")
  If bDesc Then OrderDigits = StrReverse(OrderDigits)
End Function

Excel Workbook
	A	B	C
1	Num	Asc	Desc
2	557391420	012345579	975543210
3	561	156	651
4	20356	02356	65320
5	1563957	1355679	9765531
Sort UDF