Sort Alphabets within cell

  • Hi Experts,


    I had hard time finding a macro which sort the alphabets within selected cells.


    For example, I have data something like


    CBA
    xzy
    DXa
    ABa


    when I select this range macro should sort them like


    ABC
    xyz
    aDX
    AaB


    I found Wigi's macro in post #7 in the this thread http://www.ozgrid.com/forum/showthread.php?t=98174


    its very helpful when we have delimiter as ", ". Thanks Wigi.


    But in my data there no space or delimiter.


    Can someone please spend some time help me out.


    Thanks in advance,


    Charm

  • Re: Sort Alphabets within cell


    Hi holycow


    No, characters will not be 3 always in each cell, they can be


    CBA
    KG
    DEFPO
    ABab
    L


    they should sort like


    ABC
    GK
    DEFOP
    AaBb
    L


    Thanks

  • Re: Sort Alphabets within cell


    This UDF should do what you want.
    =SortString(A1)



    This UDF uses a similar approach to sort delimited strings. If the delimiter is specified as "", it will sort the letters of the input string.
    =SortDelimited(A1, "") will return the same value as =SortString(A1)


  • Re: Sort Alphabets within cell


    Mike in first UDF is small mistake


    Op want AaBb and the code result is AabB.



    [TABLE="width: 296"]

    [tr]


    [TD="width: 64, bgcolor: transparent"]CBA[/TD]
    [TD="width: 64, bgcolor: transparent"]ABC[/TD]
    [TD="width: 138, bgcolor: transparent"]=SortString(A2)[/TD]
    [TD="width: 64, bgcolor: transparent"][/TD]
    [TD="width: 64, bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]KG[/TD]
    [TD="bgcolor: transparent"]GK[/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]DEFPO[/TD]
    [TD="bgcolor: transparent"]DEFOP[/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]ABab[/TD]
    [TD="bgcolor: transparent"]AabB[/TD]
    [TD="bgcolor: transparent, align: left"]


    [/TD]
    [TD="class: xl66, bgcolor: transparent, colspan: 2"]How OP want[/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"]L[/TD]
    [TD="bgcolor: transparent"]L[/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"]ABC[/TD]
    [TD="bgcolor: transparent"]=SortString(A2;TRUE)[/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"]GK[/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"]DEFOP[/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"]ABab[/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"]L[/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [tr]


    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]
    [TD="bgcolor: transparent"][/TD]

    [/tr]


    [/TABLE]

  • Re: Sort Alphabets within cell


    charm,


    Use in cell like


    =SortCell(A1) for ascending order
    =SortCell(A1,0) for descending order


  • Re: Sort Alphabets within cell


    Maybe this one too?


    Data in Column A.. result shown in column C.


  • Re: Sort Alphabets within cell


    Thanks Jindon & Apo...


    Jindon... yr UDF works well....


    Apo... I m sorry, didnt understood how to use yr code...


    Thanks to all for giving your valuable time.


    Charm.

  • Re: Sort Alphabets within cell


    Hi charm..


    Try it as a UDF..


    Similarly to Jindons.. use it like:


    =SortAlpha(A1) for ascending order
    =SortAlpha(A1,0) for descending order


    Code
    1. Function SortAlpha(ByVal txt As String, Optional Ord As Boolean = True)
    2. Dim myArrayList As Object, i As Long
    3. Set myArrayList = CreateObject("System.Collections.ArrayList")
    4. For i = 1 To Len(txt)
    5. myArrayList.Add Mid(txt, i, 1)
    6. Next i
    7. myArrayList.Sort
    8. If Ord = 0 Then myArrayList.Reverse
    9. SortAlpha = Join(myArrayList.toarray(), "")
    10. End Function
  • Re: Sort Alphabets within cell


    Quote from apo;732357

    argghh.. is there no way to control the way it sorts lower case first?


    mikerickson UDF Function SortDelimited can do.


  • Re: Sort Alphabets within cell


    With enlightenment from brother Pike..


    This will sort the way I want it to... :)


    Code
    1. Function SortAlpha(ByVal txt As String)
    2. Dim myArrayList As Object, i As Long
    3. Set myArrayList = CreateObject("System.Collections.ArrayList")
    4. For i = 1 To Len(txt)
    5. myArrayList.Add Mid(txt, i, 1)
    6. Next i
    7. myArrayList.Sort
    8. myArrayList.Sort_2 (CreateObject("System.Collections.CaseInsensitiveComparer"))
    9. SortAlpha = Join(myArrayList.toarray(), "")
    10. End Function