CODEWITHSUNDEEP
excelexcel-formulavba
Mark
Mark

Reputation: 69

Arrange excel value in alphabetical order

Good day! I want to ask if there's a way to arrange the cell value in alphabetical order?

Example Column A

A,M
A,Q
Q,A
M,A

I want to alphabetically arrange the value of a single cell. Not sorting the column.

Result should be like this:

A,M
A,Q
A,Q
A,M

Upvotes: 0

Views: 425

Answers (1)

trincot
trincot

Reputation: 350270

You could use this VBA code, which makes use of this answer for sorting:

Dim col As Variant
Dim list As Variant
Dim i As Long
Dim part as Variant

' Create a list that can be used for sorting
Set list = CreateObject("System.Collections.ArrayList")

' Get cell contents into an array and loop over each value
col = ActiveSheet.UsedRange.Columns(1)
For i = 1 To UBound(col)
    ' Extract the comma-separated values with Split and add them to the list
    list.Clear
    For Each part In Split(col(i, 1), ",")
        list.Add part
    Next
    ' Now use the handy Sort method on that list
    list.Sort
    ' Join the result back to the comma-separated format, and put it in the array
    col(i, 1) = Join(list.ToArray(), ",")
Next
' Put the modified array contents back in the sheet where they came from
ActiveSheet.UsedRange.Columns(1) = col

Upvotes: 2

Related Questions