Forum Discussion
How to sort numbers with a space in the one cell
Hi - Have been searching online but can't find the correct formula. Is there a way to sort numbers in order i.e. lowest to highest within one excel cell as per below when there is a space between val...
If you have the textjoin, filterxml, and sort functions, then I believe this will work:
=TEXTJOIN(" ",TRUE,SORT(FILTERXML("<L><I>"&SUBSTITUTE(A1," ","</I><I>")&"</I></L>","//I")))
If you don't have sort, then I believe this will also work (may need to hit Ctrl+Shift+Enter after keying it into the formula bar if you don't have the latest excel version):
=TEXTJOIN(" ",TRUE,SMALL(FILTERXML("<L><I>"&SUBSTITUTE(A1," ","</I><I>")&"</I></L>","//I"),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))))
=TEXTJOIN(" ",TRUE,SORT(FILTERXML("<L><I>"&SUBSTITUTE(A1," ","</I><I>")&"</I></L>","//I")))
If you don't have sort, then I believe this will also work (may need to hit Ctrl+Shift+Enter after keying it into the formula bar if you don't have the latest excel version):
=TEXTJOIN(" ",TRUE,SMALL(FILTERXML("<L><I>"&SUBSTITUTE(A1," ","</I><I>")&"</I></L>","//I"),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1))))
JMB17 Hi - Thanks for trying. I have the cell in A1. But neither formula worked and comes up with a value error.
Though I really think it would work, I don't have the sort function so can't confirm it. But, I can confirm the second one should work, provided you have those functions:
- It worked, There was a space after my last digit in the data cell that I was trying to sort. Is there something that can be added to the formula to ignore that space as I have over 1000 cells of data to sort with that space. Thanks for your help.
- Wrap the cell reference with the TRIM function. So, in my example, you would A1 to TRIM(A1).