how to sort values in a cell

i have values in a cells like this

i need each cell to be organized

specialized, Financial, tech, Healthcare
tech, broker

to
Financial, Healthcare, specialized, tech
broker, tech
finnstoneAsked:
Who is Participating?
 
Martin LissOlder than dirtCommented:
Sub SortWords()
Dim lngLastRow As Long
Dim lngRow As Long
Dim strWords() As String
Dim lngIndex As Long
Dim arrList As Object
Dim varObject As Variant

Set arrList = CreateObject("System.Collections.ArrayList")
Const DATA_COL = "A" ' Change as needed
Const FIRST_ROW = 1 ' Change as needed

lngLastRow = Range(DATA_COL & "1048576").End(xlUp).Row

For lngRow = FIRST_ROW To lngLastRow
    strWords = Split(Cells(lngRow, DATA_COL), ",")
    For lngIndex = 0 To UBound(strWords)
        arrList.Add Trim(strWords(lngIndex))
    Next

    arrList.Sort
    
    Cells(lngRow, DATA_COL).ClearContents
    For Each varObject In arrList
        Cells(lngRow, DATA_COL) = Cells(lngRow, DATA_COL) & varObject & ", "
    Next
    Cells(lngRow, DATA_COL) = Left(Cells(lngRow, DATA_COL), Len(Cells(lngRow, DATA_COL)) - 2)
    arrList.Clear
Next
End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.