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

Discuss the spreadsheet application

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

Postby Chris_A » Thu Nov 05, 2015 4:14 pm

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].
Chris Adams
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...

Postby gerard24 » Thu Nov 05, 2015 4:43 pm

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.2.6 on Windows Vista
gerard24
Volunteer
 
Posts: 913
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

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

Postby Chris_A » Thu Nov 05, 2015 5:00 pm

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?
Chris Adams
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...

Postby Villeroy » Thu Nov 05, 2015 5:10 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24794
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby Chris_A » Thu Nov 05, 2015 5:20 pm

Thanks so much. That did the trick!
Chris Adams
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...

Postby eremmel » Sat Jan 13, 2018 9:41 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
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
User avatar
eremmel
Volunteer
 
Posts: 950
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

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

Postby Lupp » Sun Jan 14, 2018 12:17 am

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 5.4.4 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1593
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: Yahoo [Bot] and 15 guests