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

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

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
Last edited by Hagar Delest on Fri Nov 20, 2015 12:21 pm, edited 1 time in total.
Reason: tagged [Solved].
Open Office 4.1.1
Windows 10 Home Edition
Chris_A

Posts: 10
Joined: Wed Oct 07, 2015 5:51 pm

Re: Help -Sorting numbers in a Single cell...

Hi,

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

Code: Select all   Expand viewCollapse view
`=SUMPRODUCT(LARGE(VALUE(MID(A1;ROW(\$A\$1:\$A\$3);1));ROW(\$A\$1:\$A\$3));10^(ROW(\$A\$1:\$A\$3)-1))`
LibreOffice 5.4.7 on Windows Vista
gerard24
Volunteer

Posts: 924
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Help -Sorting numbers in a Single cell...

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?
Open Office 4.1.1
Windows 10 Home Edition
Chris_A

Posts: 10
Joined: Wed Oct 07, 2015 5:51 pm

Re: Help -Sorting numbers in a Single cell...

Replace the A1 with TEXT(A1;"000")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x

Villeroy
Volunteer

Posts: 25758
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help -Sorting numbers in a Single cell...

Thanks so much. That did the trick!
Open Office 4.1.1
Windows 10 Home Edition
Chris_A

Posts: 10
Joined: Wed Oct 07, 2015 5:51 pm

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

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.
LO4.2.8.2 for real life with ORB; AOO4.1.3,LO5.2.5.1 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians

eremmel
Volunteer

Posts: 1005
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

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

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   Expand viewCollapse view
`{=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".)
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München

Lupp
Volunteer

Posts: 2046
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany