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...
You may try this, works with all version of Excel:
- 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.