I'd like to know if there's an Excel Function, or a formula, that can be used to sort digits within a cell.
For example, if the value of A1 is 6193254807 and B1 has our formula with parameters set to sort ascending then B1 should equal 0123456789 (leading zero included). Of course, if the parameters were set so that it would sort descending then B1 should equal 9876543210.
Is there a way to do this, without wandering into VBScript, or macros, or something similarly exotic?
This is possible with an array formula. Assuming the original number is in A1, enter any of the ff. into a blank cell and commit it with Ctrl + Shift + Enter:
Ascending:
=REPT(0,LEN(A1)-LEN(SUBSTITUTE(A1,0,"")))&
SUM(POWER(10,ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,0,""))))-1)*
LARGE(INT(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,0,""))))))
Descending:
=SUM(POWER(10,ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,0,""))))-1)*
SMALL(INT(MID(SUBSTITUTE(A1,0,""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,0,"")))),1)),
ROW(INDIRECT("1:"&LEN(SUBSTITUTE(A1,0,""))))))
&REPT(0,LEN(A1)-LEN(SUBSTITUTE(A1,0,"")))
Example:
Each formula takes the n th largest or smallest number in the cell and multiplies it by a power of 10 according to its "rank" (a.k.a. assign it a new place value), which are then summed up to produce the "rearranged" number. For example, if our original number is 231 the general calculation steps for the first formula would be as follows:
=sum(1*power(10,2), 2*power(10,1), 3*power(10,0))
=sum(100,20,3)
=123
The REPT() segment of the formula takes care of any leading or trailing zeros.
The result will be in text format. I figured this would be a good idea since there's a limit to how many digits you can enter in a cell (I recommend reading this SuperUser question).
If you want to do some calculations with the result, just insert 0+ at the beginning of the formula to turn the result into a number.
