How to sort numbers with a space in the one cell

New Contributor

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 values? So in the cell below can you order the data as 1 4 6 24 35 45 52? Thanks for your help.

35 24 45 4 42 6 52 1
21 Replies
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))))

@JMB17 Hi - Thanks for trying. I have the cell in A1. But neither formula worked and comes up with a value error. 

@Ravie72 

 

You may try this, works with all version of Excel:

Rajesh-S_0-1614839520337.png

  • An array (CSE) formula in cell C50:

 

 

{=IFERROR(SMALL(IF((COUNTIF($C$49:C49, $A$50:$A$61)=0)*ISNUMBER($A$50:$A$61), $A$50:$A$61, "A"), 1), INDEX($A$50:$A$61, MATCH(SMALL(IF(ISTEXT($A$50:$A$61)*(COUNTIF(C$49:$C49, $A$50:$A$61)=0), COUNTIF($A$50:$A$61, "<"&$A$50:$A$61), ""), 1), IF(ISTEXT($A$50:$A$61), COUNTIF($A$50:$A$61, "<"&$A$50:$A$61), ""), 0)))}

 

 

  • Finish formula with Ctrl+Shift+Enter , and fill down till needed.

If you are using higher version then yo may try this

 

 

=SORT(UNIQUE(FILTER(A50:A61,A50:A61<>"")))

 

 

Another is:

  • Apply  Auto Filter to exclude blank cells, which actually does get rid of Null Strings. Then SORT the data.

 

  • Adjust cell references in the formula as needed.

@Ravie72 

 

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:

 

 

JMB17_1-1614840158190.png

 

 

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).
Thanks JMB17 you have been a great help. One final question. If I want to delete a zero within the cell can this also be done? e.g. 3 15 0 9 56 = 3 9 15 56
Thanks again
Try:
=TRIM(SUBSTITUTE(" "&TEXTJOIN(" ",TRUE,SMALL(FILTERXML("<L><I>"&SUBSTITUTE(TRIM(A1)," ","</I><I>")&"</I></L>","//I"),ROW(INDIRECT("1:"&LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1))))&" "," 0 ",""))

@Ravie72 

Just to show how different a modern version of Excel can be, the following worksheet formula provides a very different solution strategy 

image.png

It is a mix of insider beta functionality and a legacy XLM macro 4 function.

 

 

Please elaborate your post & share that using which editor you have created this Macro supported by the new launch LAMBDA,, and the version which supports all these CELL.SORT ,, EVAL,,,, since this forum has users still working with older versions and not comfortable with VBA macros even!!

@Rajesh-S 

EVALUATE() is available in any version of Excel starting from Excel 4.0.

@Rajesh-S 

Since September 2018 there has been seismic changes in Excel.  By 'seismic', I mean it is now possible to write Excel solutions that an average user might not even recognise as being Excel.  In the post,  have only used worksheet functions, there is no macro other than the old EVALUATE function that takes a text string and evaluates it as if it were a formula.

 

The Excel 365 changes (later in the year this should also be Office 2021) I refer to are to make array formulas the default without needing CSE.  The there is the LET function that allows every alternate parameter to be a name created by the user, which refers to the formula in the following parameter.  This helps make sense of deeply nested formulas and those that repeat the use of a calculation fragment.

 

The final change, that is currently only on release in the beta versions of Excel, is the LAMBDA function.  It was this that allowed me to define the name

= CELL.SORT(seq)

which refers to the formula written over several lines (using Alt/Enter to generate a line feed).  The LAMBDA function has the names to be used within the formula as its initial parameters and the function to evaluate as its final parameter.  The thing that makes the function so revolutionary is that the LAMBDA is followed by the actual parameters to be used in the formula, held within parentheses.  These are then substituted one for one in place of the dummy parameters which means that the same defined function can be used to refer to many different areas of the worksheet. 

 

What makes the whole thing so startling to anyone schooled to accept traditional spreadsheet practice is that the resulting formulae read more like a snippet of code than a conventional formula.

 

@Ravie72 

Sort data in a range or table

 

With permission from everyone, here is some additional information about your project.

 

Thank you for your understanding and patience

 

Nikolino

 

@Nikolino 

There is nether range, nor table. Information is within one cell as text string.

To err is human, probably correct text in the wrong post.
That's what happens when you want to multitask everything.
Thanks for the hint
That I know ,,, my expectation is that the answerer must show the correct way to create & implementation of UDFs ,, Macro & features like LAMBDA,,, how may users are familiar with new arrivals !!
My dear friend I'm well versed with all you have written here,, my concern was,,, being a answerer ,, it's the prime responsibilities that one must show the correct way to create UDFs,,, VBA stuffs,,, and their implementations,, if I'm not wrong hardly 1% users can handle this new arrival feature,,, . So please show the way you have created this function !!
To be fair, I did not specify the specific version in my response and neither did you. I just said "if you have these functions", you made reference to "higher version", and Peter said "modern version" and also noted his was a mixture of legacy and "insider beta functions". His response is as specific, if not more, as either of ours.

If someone is an "insider beta", then it is reasonable to expect they have the knowledge to create named formulas through name manager as they are likely more knowledgeable users.

@Rajesh-S 

Sorry, I didn't catch. Code for both UDF (cell.value and eval) is presented. The rest are native functions like SORT(), LAMBDA(), SUBSTITUTE(), LET()