Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

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:

enter image description here

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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.