# Is there a way to sort digits within a cell?

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?

-
do you mean a single cell display the min or max for that column? –  datatoo Jul 6 '12 at 15:14
unfortunately leading zeroes will cause the numbers to be interpreted as text and thus textual sorting will happen, which is not what you will want –  datatoo Jul 6 '12 at 15:54
Is there any way to have Excel convert the numbers to text and back, as part of the formula? I'm wanting to do mathematical operations on the resulting number. –  Iszi Jul 6 '12 at 18:17
I would think since 0123456789 will be interpreted as text if there is a leading zero, you might use a decimal point. What type of data is prepending the 0, or is it a data entry being made? –  datatoo Jul 6 '12 at 18:31
@datatoo I'm trying to have Excel do some mathematical acrobatics that require maintaining the same amount of digits within a number. So, while the inputs may or may not have a leading zero, I need the outputs to retain any zeroes encountered even if they end up at the front. –  Iszi Jul 12 '12 at 20:31

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.

-
this does look good, but I wonder what kinds of input generates leading zero numbers in the first place and how accurate calculations might be if the initial format is textual anyway, I get odd results with numbers ending in zeroes, they all seem to prepend to the number. –  datatoo Jul 8 '12 at 17:33
+1 please my ignorance in misreading the question completely. You are rightly sorting the content IN the cell. –  datatoo Jul 8 '12 at 17:46