[Solved] Sorting numbers in a Single cell...

[Chris_A]

Hi:

Could anyone tell me if it is possible to sort/arrange the individual digits of a number (within a cell) from lowest to highest? For example, if i have the following numbers in a cell:

749

...and want change the number to:

479 (i.e. lowest to highest)

Is this possible and if so, how do i do it? I have 20 cells in a column, each with a 3 digit number, and I want to rearrange the numbers in each cell from lowest to highest. Any help would be appreciated.

Thanks,

Chris

[gerard24]

Hi,

Your 3 digits numbers in Column A, this formula in B1 (and copy down) :

Code: Select all

=SUMPRODUCT(LARGE(VALUE(MID(A1;ROW($A$1:$A$3);1));ROW($A$1:$A$3));10^(ROW($A$1:$A$3)-1))

[Chris_A]

Thanks, that worked. However, some of my number sequences begin with a 0, for example 032. I am getting an error message for these (Err:502). Any way around this?

[Villeroy]

Replace the A1 with TEXT(A1;"000")

[Chris_A]

Thanks so much. That did the trick!

[eremmel]

Just for generalization for variable length numbers replace $A$1:$A$3 with OFFSET($A$1;0;0;LENGTH(A1);1).
Note do not change the $A$1, but only the A1 in the replacement when your value is not in A1.

[Lupp]

Assuming the numbers are actually text as the example "032" given by the OQ suggests, I won't worry about conversion to text.
Concerning the generalisation by eremmel there is a wee little issue: If the original data are just somewhere, and none in the first row, user may one day delete that first row. >> "#REF!". In fact the issue also afflicts the less generalised solutions which will lose one digit per step.
To avoid this we need to base the OFFSET() expression on INDIRECT("a1") instead of using $A$1 in that place.

Another idea (just for fun): If you want to play with an LibO V5.3.4 or higher (NOT working in AOO!) you can try

Code: Select all

{=TEXTJOIN("";0;REPT(ROW(OFFSET(INDIRECT("a1");0;0;10;1))-1;COUNTIF(MID(G11;ROW(OFFSET(INDIRECT("a1");0;0;LEN(G11);1));1);""& (ROW(OFFSET(INDIRECT("a1");0;0;10;1))-1))))}

entered for array-evaluation (original number in G11 here).

(OT: If I remember correctly LibO V 4.2.8 was one of the few versions suffering from the issue concerning "Update references when sorting range of cells".)