[Solved] Sort digits within a cell

[SpreadOut]

How do i change or sort [lowest to highest]a 5digit number in a cell? example; A1 [15729] to B1 [12579]

[Lupp]

You are surely aware of the fact that to "change" the content of a cell does require either editing or running a Sub.
Thus I may interpret the question as something like "How to rearrange the digits of a number sorted based on a formula?"

Of course, there is no standard function specifically made for this purpose. In addition there is no accumulating function for text pieces (the sorted digits e.g.). We only can rely on a fix (maximum) number of digits and concatenate the respective characters/text pieces one by one using the concatenation operator or the CONCATENATE function. This is very unhandy and inefficient. We may do it this way if the task is specified to suppress repetitions of digits and the limitation to 5 digits per input is assured to remain unchanged.
Beyond that only some statistical functions like SMALL (or LARGE) can be used for a kind of sorting.

Conclusion: I made an example demonstrating a solution based on a custom function from my toolbox. If you can accept to rely on some user code you can do it this way, and the number of digits need not be fixed. See attached.

aoo86229NumbersWithSortedDigits_1.ods

(18.92 KiB) Downloaded 99 times

(In LibO V5.2.0 or higher you find a preliminary implementation of an unspecified function TEXTJOIN which should work in a similar way as my xTextJoin does. It is, however, probably not powerful enough, and still buggy.)
Disclaimer: Also my custom function is preliminary. It may be rather inefficient and even faulty. Errors expected!

Editing:
Another turn on the task showed that it can be done for arbitrary integer numbers by the wee little monster

Code: Select all

=REPT("0";LEN($A12)-LEN(SUBSTITUTE($A12;"0";"")))&REPT("1";LEN($A12)-LEN(SUBSTITUTE($A12;"1";"")))&REPT("2";LEN($A12)-LEN(SUBSTITUTE($A12;"2";"")))&REPT("3";LEN($A12)-LEN(SUBSTITUTE($A12;"3";"")))&REPT("4";LEN($A12)-LEN(SUBSTITUTE($A12;"4";"")))&REPT("5";LEN($A12)-LEN(SUBSTITUTE($A12;"5";"")))&REPT("6";LEN($A12)-LEN(SUBSTITUTE($A12;"6";"")))&REPT("7";LEN($A12)-LEN(SUBSTITUTE($A12;"7";"")))&REPT("8";LEN($A12)-LEN(SUBSTITUTE($A12;"8";"")))&REPT("9";LEN($A12)-LEN(SUBSTITUTE($A12;"9";"")))

This for a number contained in $A12 and allowing for repetitions.

Code: Select all

=IF(ISNUMBER(FIND("0";$A12));"0";"")&IF(ISNUMBER(FIND("1";$A12));"1";"")&IF(ISNUMBER(FIND("2";$A12));"2";"")&IF(ISNUMBER(FIND("3";$A12));"3";"")&IF(ISNUMBER(FIND("4";$A12));"4";"")&IF(ISNUMBER(FIND("5";$A12));"5";"")&IF(ISNUMBER(FIND("6";$A12));"6";"")&IF(ISNUMBER(FIND("7";$A12));"7";"")&IF(ISNUMBER(FIND("8";$A12));"8";"")&IF(ISNUMBER(FIND("9";$A12));"9";"")

will not produce repetitions.

[MrProgrammer]

Hi, and welcome to the forum.

How do i change or sort [lowest to highest]a 5digit number in a cell? example; A1 [15729] to B1 [12579]

The functions you need are QUOTIENT, MOD, SMALL, and SERIESSUM. Read about those functions in Help → Index or in User Guides (PDF) or searching for topics about them in the Calc Forum. Here's an example with simple formulas.

201611262056.ods

(7.56 KiB) Downloaded 116 times

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Lupp]

@SpreadOut: Concerning the solution suggested by MrProgrammer don't miss to get the helper columns ZA through ZJ into your view (hiding interjacent columns e.g.). You then see the 10 helper cells used per item. It will also be clear then that the solution depends on the assurance of a maximum number of digits (characters) in the entry, and that zeros will be suppressed (in case you want to apply the procedure to a string of digits rather than to a numeric value, and to get the result as a string again). Adapting the solution to the case of "no-repetions-permitted" may not be quite easy.

Thus I would suggest in this specific case to use the "monster formulae" from my answer despite their annoying length.

[SpreadOut]

@Lupp from München
Vielen Dank, for your solution.I am just a beginner.This is too complex for me at this time.

[SpreadOut]

@ MrProgrammer
Thank you very much for the example download file. WOW holy SH#T,its so beautiful,so clean and simple.OMG****You cant imagine the feeling inside,with relief to see that this can be done.Random 5digits test was inputted into A2, and whoooolaaa,B2 SORTED [actually RE-ARRANGED] the input from A2.I am just a beginner,and was just hoping to see a botton to click onthe spreadsheet todo this...but there isnt.I dont know how amazing this is for advance spread-user outhere,but for me, this moment has definetly drew a light athe end of the tunnel ,on my project.Cant thank you enuff humanly coz i had been trying days & nites hours on end trying to gethis done for months. Ps;but however B3 did not do the same,to the 5digits in A3.my 5digit data runs into the thousands. so my next quest will be trying to find out how todo "B2-Fill Down"

[Lupp]

...so my next quest will be trying to find out how todo "B2-Fill Down"...

You also need to fill down the formulae in the 10 helper columns. MrProgrammer chose to use the columns ZA through ZJ, far out of sight, for the purpose. The filling down in column B is done as usuall. For empty cells in column A you will get 0 as the result then.

[MrProgrammer]

My 5-digit data runs into the thousands, so my next quest will be trying to find out how todo "B2-Fill Down"

The simplest method for thousands of rows is to use the Name Box. See Help → Index → formula bar;sheet area names. Let's say you want to fill down to line 3141. Type ZA2:ZJ3141 (the helper columns ZA to ZJ) in the Name Box and press Enter. Use menu Edit → Fill → Down. Type B2:B3141 (the result column B) in the Name Box and press Enter. Edit → Fill → Down. Learn about other techniques in the tutorial below.

In this attachment, I use a number style on the values in columns A and B to ensure that leading zeros in the 5-digit number are displayed.

201611272153.ods

(7.62 KiB) Downloaded 83 times

[Tutorial] How do I specify the formula for a column?

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[SpreadOut]

Lupp >>> [MrProgrammer chose to use the columns ZA through ZJ, far out of sight].Thank You so much Lupp,this saved my @ss & put me on the road again.I would have never knew this was hiding wayout yonder.Your quick insight
to my quest is now complete & fullfilled..."kinda like what good is it if you have the car,but not the key." Bothe You [Lupp] & Mr. Programmer,are of great assets to this forum & the community inwhole.With GREAT Appreciation from me.The feeling is fun again to proceed with my intense project,after horrible days&nites starring athe blank spreadsheet,wondering how todo this,coz of my inabilities with spreadsheets in general.

[SpreadOut]

Lupp >>> [You also need to fill down the formulae in the 10 helper columns.] yeaaaa,i relocated these little helpers.
adjacent to my data,man o man its awsome.it gave me the re-arranged results into the 1000s.Thanks again Lupp.