Sort text in single cell alphabetically

I have a string of text that contains cells with multiple country codes. They all contain commas in-between each code. 

(Example:)

AR,BA,CZ,GH,KG,KH,MN,MO,OM,PE,RO,UY,AE,AF,AW,AZ,BD,BF,BH,BN,BY,CN,CV,CY,DZ,EG,ES,ET,FJ,GM,GN,GT,IE,JO,KW,LB,LK,LR,LT,MA,MU,MV,MY,NE,NI,PA,QA,RU,SA,SN,TN,TT,UZ,VN,X1,

I need a function that will sort these in alphabetical order. I have a macro (below) but, a function works better for me. 

                         

Sub StoreData()
    Dim sString As String
    Dim sTemp As String
    Dim iNoCommas As Integer

    sString = Range("C4").Value

     ' Evaluate where the commas are positioned
     ' in order to transfer all entries in the
     ' string within array

    For i = 1 To Len(sString)
        sTemp = Mid(sString, i, 1)
        If sTemp Like "," Then
            iNoCommas = iNoCommas + 1
        End If
    Next i

     ' Update information in cell A12
    Range("C4").Value = SortArray(sString, iNoCommas)

End Sub

|
Answer
Answer

Here is my function

best wishes

Function SortMe(mycell)
 Dim mycode() As String
 myinput = mycell.Value
 mycode = Split(myinput, ",")
 For j = LBound(mycode) To UBound(mycode) - 1
   For k = j To UBound(mycode)
     If mycode(k) < mycode(j) Then
       temp = mycode(k)
       mycode(k) = mycode(j)
       mycode(j) = temp
     End If
   Next k
 Next j
 mysort = ""
 For j = LBound(mycode) + 1 To UBound(mycode)
   mysort = mysort & mycode(j) & ","
 Next j
 SortMe = mysort
End Function

In Internet Explorer - click the image to get larger view

http://people.stfx.ca/bliengme
A Guide to MS Excel 2013 for Scientists and Engineers

7 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated February 1, 2021 Views 6,015 Applies to: