Sorting numbers within a cell without omitting the 0
I would like to sort numbers from smallest to largest within a single cell. The current cell content is 1, 4, 0, 2, -9, -10 and should be -10, -9, 0, 1, 2, 4
I managed to sort it, however, the 0 is omitted in the output which is an issue. How can I make sure the entire content is sorted?
For reference, this is the current formula I am using:
=TEXTJOIN(", ",1,IFERROR(1/(1/SMALL(FILTERXML("<a><b>"&SUBSTITUTE(A5,",","</b><b>")&"</b></a>","//b"),ROW($1:$99))),""))
Solution
You could try using the TEXTSPLIT() function here:
• Formula used in cell B1
=TEXTJOIN(", ",1,SORT(--TEXTSPLIT(A1,,", ")))
• Or,
=TEXTJOIN(", ",1,IFERROR(SMALL(FILTERXML("<m><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></m>","//b"),ROW(1:99)),""))
• If you have access to LET() then and not TEXTSPLIT():
=LET(
a, 1/(1/SMALL(FILTERXML("<m><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></m>","//b"),ROW(1:99))),
TEXTJOIN(", ",1,IFERROR(IFNA(IF(ERROR.TYPE(a)=2,0,a),a),"")))
• Or,
=TEXTJOIN(", ",1,SUBSTITUTE(IFERROR(1/(1/SMALL(--(FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b")&".1"),ROW($1:$99))),""),".1",))
- What does it do to pass a callback returning an expression involving `Math.random()` to the `sort` method?
- I want to display the key/value bean based on sorting of value in Struts 2
- What sort order does Rust's built-in cmp for &str use?
- Sort an associative array by values descending and preserve keys then give a ranking number to each key
- Sort an associative array with unique values by the values of another indexed array
- How to use OrderBy with findAll in Spring Data
- How to get sorted list of map keys in Go?
- Sort an array to have specific items first in the array
- DataTable jquery - How to remove sort icon from first column?
- Sorting numbers within a cell without omitting the 0
- jQuery sort function not working properly
- Sort an array of PSCustomObject by values of a property
- Sorting cells in table view through buttons
- Will Arrays.sort() increase time complexity and space time complexity?
- Reverse a comparator in Java 8
- sort object by integer key in PHP
- Python: Decorator to count swaps in sorting algorithms
- Select rows from a table that are not in another
- Sort QTreeWidget alphabetically, except one item
- PHP Natural sort on array of objects by a column
- Sort an array of filepath strings naturally
- Sort a multidimensional array by first level keys
- Naturally sort an array of alphanumeric strings
- Sort a multidimensional array by a column value
- Move a row with a specific column value to the end of the array
- Sort a 2d array by a column descending
- Sort a 2d array by one column then another
- Using a comparator function to sort
- Sorting list array on one field, then on another one
- Sort a 2d array with 1d array in appscript