SOLVED

Sort Numbers Within A Cell In Excel?

Copper Contributor

Hi everyone, 

 

I am just looking for a formula to sort numbers within a cell

 

042 = 024 or 420

 

3406 = 0346 or 6430 

 

32 = 23 or 32

 

 

 

 

21 Replies

Perhaps try:
=textjoin("",true,SMALL(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1)))))

You may need to hit Ctrl+Shift+Enter for it work after keying it into the formula bar. For descending order, try replacing "small" with "large".

 

If you have office 365 and the newer functions (such as sort and sequence), then it could probably be made more efficient. This is a guess as to how you could try the sort function as I don't have that one, but someone else may be able to speak to whether or not it would work:

 

=textjoin("",true,sort(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

@Marco365 If you are on a recent Excel version, perhaps like this:

Screenshot 2021-01-21 at 06.38.12.png

Workbook attached.

@Riny_van_Eekelen 

 

Hi Riny,

 

I am not able to do it so far, I am not sure why. 

 

I have added more to what I am trying to get done in this file. Let me know if you can see it and if you can help, otherwise I would post the file to get some help.   Thank you so much 

@Marco365 Sorry, but you need to be more specific. What I do get is that, for instance the number in E1 = 351. The largest number that can be created from these digits is 531. Similar for E2 and E3. The digits in the respective numbers can all be "re-grouped" to (the largest number) 531. Now, E4 (on 18 sep 2020) has the number 131. How does this one relate to the 531 in J17, also 18 sep 2020? Similar for the next one (331 on 16 sep). And why is there a number "532" in the list of 531's, dated 17 sep 2020? (See picture). Perhaps you accidentally typed it wrong and it should also be 531 and on 14 sep 2020. But why?

Screenshot 2021-01-22 at 05.51.18.png   Screenshot 2021-01-22 at 05.38.36.png

Why not create an example of, say, the first 10 numbers on your list and manually produce the full output of what you need, noting down exactly, the logic for each step.

 

 

@Riny_van_Eekelen 

 

Hi Riny, so sorry for the confusion, that is what I was thinking of doing creating a brand new spreadsheet showing what I need to get done manually. I will work on it. Should I do a different post or use this one again? I am not too familiar with this platform, I don't want to have things all over the place. 

@Marco365 Always best to start one thread regarding one problem and not mix in other questions on the go. I noticed that you uploaded the same file and question at the end of the thread in the link below.

https://techcommunity.microsoft.com/t5/excel/assign-a-number-to-the-different-values/m-p/1148488 

 

Best for now is that you take some of your dates and numbers and carefully produce the output you need, explaining the logic of every step needed to get there. What you have given so far is not clear, at least not to me.

@Riny_van_Eekelen 

 

I am sure it is kind of confusing, I thought so myself. I have attached another spreadsheet  with 3 sheets with instruction in the sheet or see the sheet title. Let me know if that is clear and is possible to achieve. Thanks  

best response confirmed by Marco365 (Copper Contributor)
Solution

@Marco365 Thanks for the file and clarification. Rather than creating a lot of manual steps, I thought it was best to create two pivot tables ("pt"). on the basis of the data sheet "1 Sort by cell....."

 

The first pt counts the number of numbers in each month of the year.

Screenshot 2021-01-24 at 08.50.13.png

A second pt creates a list of all dates in the data, with a filter on the number with digits in descending order. I called it "DescNr". This is the basis for individual pt's for each of these numbers. They look like this:

Screenshot 2021-01-24 at 08.53.36.png

Don't worry! You don't have to create every pt for each "DescNr". Under options on the Pivot Table Analysis ribbon, you can select "Show Report Filter Pages...". This will create one pt for each of the items in the filter field.

Screenshot 2021-01-24 at 08.36.24.png

Perhaps you find this approach acceptable. Almost fully automated and not much room for error.

@Riny_van_Eekelen 

 

 

First of all, THANK YOU SO MUCH!, THANK YOU, THANK YOU, THANK YOU!

 

Now, I have some questions, did I miss the first step? I wanted to sort all the numbers by cell, so 291 and 192 can all become 921, not combining them but just sort each one of them in the column but by cell. I would prefer from highest to lowest.

 

 

Second, how can I also count how many days in the second pt, first instance, 331 has 5 for 2020  and 7 for 2019, is that clear?

 

 

last one, I know you use pt, would a video be possible at all to see the steps for this particular file? if not I'll just have to go find out how to use a pt and try it on my own. I would love to save that time and learn exactly what I need to learn right now from this file.

 

 

Thank you, thank you! 

@Marco365 In sheet "1 Sort by......" click the filter button and sort Descending. Is that what you mean?

 

Have added a field with sub-total to count the number of occurrences of every "DescNr". 

 

A video is not something I'm able to create just now. Google for "How to create a pivot table" and you'll get many resources/tutorials. Much better than what I can do. 

 

Attaching a file with the changes described above.

@Riny_van_Eekelen 

 

I started looking at tutorial, I will go ahead and learn more about it because I need it. But I would like your help with just one more thing. I feel like I am missing it, but let me clarify again, in sheet "1Sort by cell..." I had to take column E and manually create column F, meaning each cell in column E I had to manually create another cell for it in column F by just typing it. Column E for instance had in each cell these numbers 531, 513, 135, 153, 315, 353 but I had to manually turn all of them in column F to 531, 531, 531, 531, 531, 531. How can I sort each cell from highest to lowest so all become 531 automatically? is that clear?

@Marco365 Column F can be created by the formula I explained in my very first response. Isn't that one working for you? If not, what Excel version are you one?

 

@Riny_van_Eekelen 

 

I have 2019, I did try but didn't work. It could be me. I am sorry. Perhaps you can give me another chance by trying it in the file I sent? Thanks

@Marco365 Okay. Then you can use the formula that was provided by @JMB17 above. It works pretty much the same as mine and should work in E2019. Just read his post for more instructions.

 

=TEXTJOIN("",TRUE,LARGE(--MID(E2,ROW(INDIRECT("1:"&LEN(E2))),1),ROW(INDIRECT("1:"&LEN(E2)))))

 

@JMB17 

 

Thank you, it works now. Thank you very much.

@Riny_van_Eekelen 

 

Thank you, I did go back and play with it and figured it out. It works. Thank you very much. 

@JMB17 

 

Hi again JMB17,

 

How can I tweak this formula to do the same thing for alphabets rather then numbers, for instance, ACBM can become ABCM or MCBA?

@Marco365 

 

Try:

=TEXTJOIN("",TRUE,CHAR(SMALL(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),ROW(INDIRECT("1:"&LEN(A1))))))

 

And, you should be able to replace "small" with "large" to go in descending order. Also, I'm assuming the data does not mix upper/lower case?

@Marco365 , it shall work with texts

1 best response

Accepted Solutions
best response confirmed by Marco365 (Copper Contributor)
Solution

@Marco365 Thanks for the file and clarification. Rather than creating a lot of manual steps, I thought it was best to create two pivot tables ("pt"). on the basis of the data sheet "1 Sort by cell....."

 

The first pt counts the number of numbers in each month of the year.

Screenshot 2021-01-24 at 08.50.13.png

A second pt creates a list of all dates in the data, with a filter on the number with digits in descending order. I called it "DescNr". This is the basis for individual pt's for each of these numbers. They look like this:

Screenshot 2021-01-24 at 08.53.36.png

Don't worry! You don't have to create every pt for each "DescNr". Under options on the Pivot Table Analysis ribbon, you can select "Show Report Filter Pages...". This will create one pt for each of the items in the filter field.

Screenshot 2021-01-24 at 08.36.24.png

Perhaps you find this approach acceptable. Almost fully automated and not much room for error.

View solution in original post