Excel 2016 - Sort value in cell in ascending order

Question

I currently have a cell with some values, separated by commas. I wish to sort them in ascending order.

Sample Input (Value in a single cell):

Sample Output (Value in a single cell):

I have seen many answers when it comes to sorting rows and columns, but I can't seem to sort the values in a single cell in ascending order. Is it possible to sort the values in a single cell in ascending order? Or is there a workaround for this?

Some explanation/documentation would be appreciated as I'm a beginner at VBA. Thank you for your help.

*"didn't work"* is about as vague as you can get. It would be easier for other to help you if you include a [mcve] and an explanation of where's it's giving you trouble. Also [here's](http://www.cpearson.com/excel/SortingArrays.aspx) an article about sorting arrays in VBA from guru Chip Pearson (who's [website](http://www.cpearson.com/Excel/Topic.aspx) taught many of us VBA) ✞ — ashleedawg, Jan 28 '22 at 10:23
You could take those values into an array using VBA and probably the function [Split](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/split-function) and then [Sort the array](https://stackoverflow.com/a/152325/9199828) — Foxfire And Burns And Burns, Jan 28 '22 at 10:27

score 0 · Answer 1 · answered Jan 28 '22 at 11:22

0

Please try to split data in a single cell by comma, then sorting it and combine all of them together?

answered Jan 28 '22 at 11:22

PNM

1

score 0 · Answer 2 · answered Jan 28 '22 at 11:23

The comments provide you with numerous ways to sort an array. Rightly or wrongly, I've provided my own flavour on an old VBA topic along with the extended piece of outputting the string in a delimited format. Take it or leave it ...!

You should be able to refer to the below function directly from any given cell like you would any built-in function in Excel.

Public Function SplitAndSortAscending(ByVal strText As String, ByVal strDelimiter As String) As String
    Dim arrData() As String, arrNewData() As String, i As Long, x As Long, y As Long
    
    arrData = Split(strText, strDelimiter)
    ReDim arrNewData(UBound(arrData))
    
    For i = 0 To UBound(arrData)
        For x = 0 To UBound(arrNewData)
            If arrData(i) < arrNewData(x) Or arrNewData(x) = "" Then
                For y = UBound(arrNewData) To x + 1 Step -1
                    arrNewData(y) = arrNewData(y - 1)
                Next
                
                arrNewData(x) = arrData(i)
                Exit For
            End If
        Next
    Next
    
    For i = 0 To UBound(arrNewData)
        SplitAndSortAscending = SplitAndSortAscending & strDelimiter & arrNewData(i)
    Next
    
    SplitAndSortAscending = Mid(SplitAndSortAscending, Len(strDelimiter) + 1)
End Function

If you have O365, you can use something like the below to achieve the same sort of thing. Take note, my implementation will take 1.0 and format it as a whole number, i.e. it will come out as 1.

=TEXTJOIN(",",TRUE,SORT(FILTERXML("<r><v>" & SUBSTITUTE(A1,",","</v><v>") & "</v></r>","//v")))

The assumption is that the example you provided is in cell A1.

Excel 2016 - Sort value in cell in ascending order

2 Answers2