alphabetizing/sorting text within a cell

[patrickchen]

hi,

does anybody know how to alphabetize groups of words within a cell (by
first letter of the word)?

for instance, in a cell i have the words "dog bird cat" and after
alphabetization it would come out as "bird cat dog"

any help would be appreciated thanks

 

[Guest]

You could use Text To Columns (delimiter = space) to split the data. Then
use Excel's sort (Data/Sort), and concatenate the items back together into
one string. Another possibility is to try a User Defined Function.

Function MySort(rngCell As Range) As String
Dim arrData As Variant
Dim i As Long
Dim t As Long
Dim strTemp As String

arrData = Split(rngCell.Value, " ", -1, vbTextCompare)

For i = LBound(arrData) To UBound(arrData) - 1
For t = i + 1 To UBound(arrData)
If LCase(CStr(arrData(t))) < LCase(CStr(arrData(i))) Then
strTemp = arrData(i)
arrData(i) = arrData(t)
arrData(t) = strTemp
End If
Next t
Next i

MySort = Join(arrData, " ")

End Function

 

[patrickchen]

Thanks, the function worked really well!

 

[Guest]

Glad to help