CODEWITHSUNDEEP
excelvba
ANWAR ALAM
ANWAR ALAM

Reputation: 11

Sort digits of a number in ascending or descending order in Excel 2010?

I am Using Excel 2010. i write any number(say 69138) in cell A1. I want to display this number in both ascending order (13689) and descending order (98631) in cells B1 and C1, respectively.(2) what will be the VBA CODE, behind a command button for this same purpose.Thanks in anticipation

Upvotes: 0

Views: 337

Answers (1)

zoldxk
zoldxk

Reputation: 1

You can type this in the cell B1 to display A1 in ascending order:
=TEXT(SUM(SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),REPT("0",LEN(A1)))
the press ** Ctrl + Shift + Enter**
For descending order type in the cell C1:
=TEXT(SUM(LARGE(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^(LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))))),REPT("0",LEN(A1))).

Also you can write a user defined function like this :
1- Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2- Click Insert > Module, and paste the following code in the Module Window.

Function SortNumsInCell(pNum As String, Optional pOrder As Boolean) As String
'Update 20140717
Dim xOutput As String
For i = 0 To 9
  For j = 1 To UBound(VBA.Split(pNum, i))
    xOutput = IIf(pOrder, i & xOutput, xOutput & i)
  Next
Next
SortNumsInCell = xOutput
End Function

3-Then save and close this code, go back to your worksheet, and enter this formula =sortnumsincell(A1) into a blank cell next to your data
Note: If you want to sort the numbers in descending order, please enter this formula =sortnumsincell(A1,1).

Upvotes: 3

Related Questions